Php – Laravel eloquent multiple table join with filter


There are theree tables in my system.

  1. Students
  2. Articles
  3. categories

Student can write many articles and a article is belong to just one student. And A Article can have only one category.


public function all_articles_by_student_by_category(Request $request){

        $students_id = $request->students_id;
        $categories_id = $request->categories_id;

        $article_list = Students::find($students_id)->articles->all();

        //This return Something like, Select All Articles Written by Damith


class Students extends Model
    protected $fillable = ['id','first_name', 'last_name', 'age', 'created_at', 'updated_at'];

    public function articles()
        return $this->hasMany('App\Articles');

What I am try to get

Something like, Select All Articles Written by Damith for Technology Category (Category Name should be there)

What I able to do so far

Something like, Select All Articles Written by Damith using $article_list = Students::find($students_id)->articles->all(); (You can find this code from controller)

What I want from you

How do I modify $article_list = Students::find($students_id)->articles->all(); to get, something like, Select All Articles Written by Damith for Technology Category. (Category name must be there in result and it is on category table, and for where condtion you can use the category_id which is i the article table )

Best Solution

First off with what you have done so far the ->all() method is not needed when getting the records for a relation on a model, this would return all of the articles linked to that student:


Go through Articles Model
You could do something like:

Article::where('student_id', $students_id)
  ->where('category_id', $category_id)->get();

Which would acheive the result you are after.

Go through Students Model

If you want to go through Students Model you can constrain the relation using the with method.

$student = Students::with(['articles' => function($query) use ($category_id) {
  $query->where('category_id', $category_id);

$filteredArticles = $student->articles

Useful Links

When accessing Eloquent relationships as properties, the relationship data is "lazy loaded". This means the relationship data is not actually loaded until you first access the property. However, Eloquent can "eager load" relationships at the time you query the parent model.

Sometimes you may wish to eager load a relationship, but also specify additional query constraints for the eager loading query.