Question 16504 in Laravel Question posted in
The official Laravel Documentation can be found here.

How can I get the same value using different foreign keys – Laravel

I have this 3 tables and I want to get the same subjects by given student_id

enter image description here

enter image description here

enter image description here

For example I will select Vince (student_id = 1) And Jane (student_id = 3) the return value should be the subjects english and science.

Tags:

4

Answers


  1. 0

    I will assume that you did infact create your database correctly, this is a full solution approach:

    Some information to consider, as you might run into SQL errors if you do not have the correct database names or column names:

    1. Laravel’s naming convention for pivot tables is snake_cased model names in alphabetical order separated by an underscore. So, if one model is Feature, and the other model is Product, the pivot table will be feature_product: naming tables in many to many relationships laravel

    2. In addition to customizing the name of the intermediate table, you may also customize the column names of the keys on the table by passing additional arguments to the belongsToMany method. The third argument is the foreign key name of the model on which you are defining the relationship, while the fourth argument is the foreign key name of the model that you are joining to: https://laravel.com/docs/9.x/eloquent-relationships#many-to-many-model-structure

    Example:

    return $this->belongsToMany(Role::class, 'role_user', 'user_id', 'role_id');

    Student Model:

    class Student extends Model
{   
    public function subjects(){
        return $this->belongsToMany(Subject::class);
    }
}

    Subject Model:

    class Subject extends Model
{
    public function students() {
        return $this->belongsToMany(Student::class);
    }
}

    StudentController:

    /**
 * Show the specified model.
 *
 * @param  IlluminateHttpRequest  $request
 * @param  Student $student
 * @return IlluminateHttpResponse
 */
public function show(Request $request, Student $student){
    // This will load all of the inner relationships in a single query.
    $student->load('subjects');
        
    //Assign the loaded subjects to be used
    $subjects = $student->subjects;

    return view('sampleView', compact('student', 'subjects'));
}

    In sampleView Blade:

    <p>{{$student->name}}</p>
<!-- You call the subjects relationship and display it !-->

<ul>
   @foreach($subjects as $subject)
      <li>ID: {{$subject->id}} - Name: {{$subject->name}}</li>       
   @endforeach
</ul>
    Login or Signup to reply.
  2. 0

    To find the subjects that Vince and Jane have in common, you can use a JOIN and WHERE clause in a SELECT statement.

    SELECT su.name AS subject_name
FROM students_subjects ss
JOIN students s ON ss.student_id = s.id
JOIN subjects su ON ss.subject_id = su.id
WHERE s.name IN ('Vince', 'Jane')
GROUP BY su.name
HAVING COUNT(su.name) = 2;

    This will select the names of the subjects that both Vince and Jane are enrolled in. The JOIN clauses are used to bring in the relevant data from the students and subjects tables, and the WHERE clause is used to filter the results to only include rows where the student’s name is either Vince or Jane. The GROUP BY and HAVING clauses are used to ensure that only subjects that are taken by both students are included in the final result.

    The output of this query will be a list of subject names that Vince and Jane have in common.

    Login or Signup to reply.
  3. 0

    Create Two Models

    <?php

namespace AppModels;

use IlluminateDatabaseEloquentFactoriesHasFactory;
use IlluminateDatabaseEloquentModel;

class Student extends Model
{
    use HasFactory;


    public function subjects(){

        return $this->hasMany(Subject::class);
    }
}

    and

    <?php

namespace AppModels;

use IlluminateDatabaseEloquentFactoriesHasFactory;
use IlluminateDatabaseEloquentModel;

class Subject extends Model
{
    use HasFactory;


    public function students(){

        return $this->belongsToMany(Student::class);
    }
}

    and I hope you have followed laravel naming convention for database tables

    students ,subjects,student_subject

    and your query will be

     $subject=AppModelsSubject::query()->whereHas('students',function ($query){
        $query->whereIn('student_id',[1,3]);
    },"=",2)->get();

    dd($subject->toArray());

    Since you need both students common subjects so you should pass count to whereHas method

    So output will be

    array:2 [▼ // routesweb.php:27
  0 => array:4 [▼
    "id" => 2
    "name" => "English"
    "created_at" => null
    "updated_at" => null
  ]
  1 => array:4 [▼
    "id" => 3
    "name" => "Science"
    "created_at" => null
    "updated_at" => null
  ]
]

    if you want it to be dynamic then

     $studentIds=[1,3,6];
    $subject=AppModelsSubject::query()->whereHas('students',function ($query)use($studentIds){
        $query->whereIn('student_id',$studentIds);
    },"=",count($studentIds))->get();

    To understand whereHas Method params.Here is the implementation.

    /**
     * Add a relationship count / exists condition to the query with where clauses.
     *
     * @param  string  $relation
     * @param  Closure|null  $callback
     * @param  string  $operator
     * @param  int  $count
     * @return IlluminateDatabaseEloquentBuilder|static
     */
    public function whereHas($relation, Closure $callback = null, $operator = '>=', $count = 1)
    {
        return $this->has($relation, $operator, $count, 'and', $callback);
    }
    Login or Signup to reply.
  4. 0

    If you are looking for the query, this will help you :

    select s.name AS subject_name
from students_subjects ss
inner join __subjects s on s.id = ss.subject_id
where student_id in (1,3)
group by ss.subject_id
having count(1) = ( select count(1) from students where id in (1,3) );
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search