Laravel eloquent multiple table join with filter

1
votes

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.

Controller

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
    }

Model

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 )

2
phplaravellaravel-5laravel-eloquentlaravel-5.5
The answer is easy, please checkout the Laravel documentation on how to use eloquent. If you are able to do a plain SQL query this means that you are able to do it with eloquent. Just learn the syntax. – Desh901

2 Answers

2
votes

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:

Students::find($students_id)->articles

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);
}])->find($student_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.

2
votes

Something like this should work:

$technologyArticles = Articles::where('student_id', '=', $students_id)->where('category_id', '=', $categories_id)->get();