CODEWITHSUNDEEP
laravellaravel-4many-to-manyeloquent
user3776967
user3776967

Reputation: 35

Multiple Many to Many relationship in Eloquent / Laravel 4

I am rather new to Laravel, and am a bit confused on how to proceed.

I have a Role model, a Category model and a Question model. I am using the data to display tests for different role types, broken down by category and displaying related questions.

Roles have many categories, and categories have many questions (I have the pivot tables created and the manyToMany relationships defined in the models), but the tricky part here is that the questions also have an associated role (because different roles can have similar tests with the same categories, but the questions vary).

The way I started was first finding the role:

$role = Role::find(role_id);

Then I got the related categories:

$categories = $role->categories;

Then I looped over each category and spit out the questions:

 foreach($r->categories as $cat){
   foreach($cat->questions as $cq){
     print_r($cq->question);                                                                                                              
   }
  }

But the problem is I don't know where to set the restriction that the question only shows if it has the associated Role as defined by the initial role id.

I tried something like:

foreach($cat->questions()->where('role_id','=',$role_id)->get() as $cq){                                                                 
    print_r($cq->question);
 }

But this failed saying:

Illuminate \ Database \ QueryException
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'role_id' in 'where clause' (SQL: select `questions`.*, `category_question`.`category_id` as `pivot_category_id`, `category_question`.`question_id` as `pivot_question_id` from `questions` inner join `category_question` on `questions`.`id` = `category_question`.`question_id` where `category_question`.`category_id` = 1 and `role_id` = 2)

Any assistance would be greatly appreciated!

Upvotes: 1

Views: 948

Answers (1)

Jarek Tkaczyk
Jarek Tkaczyk

Reputation: 81187

What you need is has. And by the way, use eager (or lazy) loading in order to avoid n+1 queries problem.

$role->load('categories'); // lazy load categories, for a single model works just like $role->categories;

$roleId = $role->id;

$role->categories->load(['questions' => function ($q) use ($roleId) {
    $q->whereHas('roles', function ($q) use ($roleId) { // assuming roles is relation name
        $q->where('roles.id', $roleId); // assuming roles is table name
    }]);
// this time you load the relation on the collection so it is completely different
// to calling $category->questions; in a loop

Then you can access the questions in a loop like you tried.

If you need just the questions in a collection (not attached to the categories) then solution will look a bit differently.

Upvotes: 1

Related Questions