Laravel Eloquent Multiple whereHas with Where condition

Question

I have models Term, Concept, and Expression. I have pivot tables concept_expression and concept_term.

Schema

• terms table
  • id
  • term,
• concepts table
  • id
• expressions table
  • id
  • expression_plural

Relationships

• Term model
  • concepts(): BelongsToMany
  • expressions(): HasManyThrough
• Concept model
  • terms(): BelongsToMany
  • expressions(): BelongsToMany
• Expression model
  • concepts(): BelongsToMany
  • terms(): HasManyThrough

I want to find Terms of a certain Expression category, but first, a concept should be found, because a concept is linked to an expression.

In my CategoryController, I'm trying this with Eloquent:

$terms = Term::whereHas('concepts')
    ->whereHas('expressions', function ($query) use ($expression_plural) {
        $query->where('expression_plural', 'materials');
    })
    ->take(16)
    ->toSql();

The query that is being made is as follows:

SELECT *
FROM   `terms`
WHERE  EXISTS (SELECT *
               FROM   `concepts`
                      INNER JOIN `concept_term`
                              ON `concepts`.`id` = `concept_term`.`concept_id`
               WHERE  `terms`.`id` = `concept_term`.`term_id`)
       AND EXISTS (SELECT *
                   FROM   `expressions`
                          INNER JOIN `concepts`
                                  ON `concepts`.`id` =
                                     `expressions`.`concept_id`
                   WHERE  `terms`.`id` = `concepts`.`term_id`
                          AND `expression_plural` = 'materials')
LIMIT  16

But I'm getting this error message when I check the query in HeidiSQL:

Unknown column 'concepts.term_id' in 'where clause'

To be honest, I'm becoming confused about the relationships. What are the steps to find only certain expressions of a term, when there is also a concept between them?

In my controller, I also tried, for example:

$expression = Expression::where('expression_plural', $expression_plural)->first();
$concepts = $expression->concepts->take(10);

And in my view, I just looped the $concepts to find the $terms, but I just want $terms directly in my view without the need to loop $concepts.

Answer 1

The error you encountered, Unknown column 'concepts.term_id' in 'where clause' suggests that the term_id column is not present in the concepts table, and that's because of the way you are trying to use HasManyThrough in this context, which is not directly applicable.

The issue lies in the fact that HasManyThrough is designed to work with direct relationships between models, whereas your models have BelongsToMany relationships, causing the problem. Check the official documentation to find out how it works.

My recommendation is to use the package "eloquent-has-many-deep" by staudenmeir, which provides a solution for scenarios like yours, allowing you to define more complex relationships.

By using hasManyDeep method from the package, you can define the complex relationship between models and get the desired results directly in your view without the need to loop through concepts.

If you choose not to use the "eloquent-has-many-deep" package or any other similar package, you would have to resort to alternative methods like raw queries or additional intermediate queries, which can be less elegant and more cumbersome to implement and maintain.