1

I have the following schema:

+------------+
| categories |
+------------+
| id         |
+------------+

+-------------+
| sections    |
+-------------+
| id          |
| category_id |
+-------------+

+------------+
| questions  |
+------------+
| id         |
| section_id |
+------------+

+------------+
| clients    |
+------------+
| id         |
+------------+

+-------------------+
| client_questions  |
+-------------------+
| client_id         |
| question_id       |
+-------------------+

As you can see questions are in sections and sections are in categories.

The admin of the system can toggle a question on or off for each individual client so I create client_questions to create the many-to-many relationship between clients and questions.

Now, I would like to harness the awesomeness of Eloquent to get a client's categories (to list out all the questions) but I can't seem to wrap my head around the query builder.

Basically I can currently do $client->questions via a many to many relationship I defined in the model:

public function questions() {
    return $this->belongsToMany(Question::class,'client_questions','client_id','question_id');
}

but I can't seem to figure out how to do the same thing between clients and categories considering the degree of separation.

Basically I would like to do $client->categories so that I can then list out the whole interview:

@foreach( $client->categories as $category)
  @foreach( $category->sections as $section)
    @foreach( $secion->questions as $question )
      {{ $question->question }}
    @endforeach
  @endforeach
@endforeach
Gazillion
  • 4,822
  • 11
  • 42
  • 59
  • Do you have all the others relationships set up between the different models? Also, are you doing this for just one client? – Rwd May 13 '19 at 19:03
  • I have the following relationships defined: question to section, section to category, client to questions. – Gazillion May 13 '19 at 19:36

2 Answers2

1

I've answered this at laravel looping through eloquent models with deep relationships

but basically you can use dot notation to load far reaching models onto the current one as long as they are all related down the chain.

$questions->load(['sections.categories' => function($q) use(&$categories){
    $categories = $q->get();
}]);

That should be all of the categories for the provided question.

Do note that this is an expensive operation so if the collection is too large be ready for a slog, but this is the only other way besides foreaching over foreaches that I've come across.

N Mahurin
  • 1,406
  • 10
  • 18
  • I already have a list of questions for the client via the client_questions table. Am I misunderstanding that your code gets me the questions based on the client category? I'm looking for the inverse, get categories from questions. – Gazillion May 13 '19 at 19:39
  • This can be used in any fashion as long as the models continue to have a relationship through the dots. I'll modify my answer to provide categories from questions. – N Mahurin May 13 '19 at 19:47
1

Unfortunately, you won't be able to define a direct relationship between clients and categories, however, you can use the existing relationships to get what you want.

For this I would suggest using a mixture of with() (constrained eager loading) and whereHas and starting with the Category model:

$categories = Category::with([
    'sections.questions.clients' => function ($query) use ($clientId) {
        $query->where('id', $clientId);
    },
])->whereHas('sections.questions.clients', function ($query) use ($clientId) {
    $query->where('id', $clientId);
})->get();

The reason you have to use both is because either one on their own could result in you having questions that don't directly relate to the client.

Rwd
  • 34,180
  • 6
  • 64
  • 78
  • Thank you for the help. I was able to get it to work with your code out of the box -- though it made me realize that I was missing a relationship between questions and clients. – Gazillion May 13 '19 at 20:04