jswoolf01's avatar

Query on parent-table condition or child-table condition

Situation: I have three tables: Product (inventory products), Flags (indicator flags that show 'something needs attention'), and Rules (rules for creating Flags). The relationship chain goes product->flags->rules. Each product can have many flags, while each flag belongs to one rule (but one rule can have many flags).
We're transitioning to a new way of defining and handling flags. Under the old way, the flag record itself contains a flag message, and the first few characters of the message are the severity. Under the new way, the severity is shown by a boolean field in the rule definition: false means low severity, true means high severity.
I need a way to retrieve all flags for a given product where severity = 'high', whether the flag was defined under the old way or the new way. That is, I want a query in a Product class method that says "retrieve all flags for this product where either flag->message like 'HIGH%' or flag->rule->severe = TRUE."
I've tried the straightforward approach:

$flagList = $this->flags()
			->where('message',  'like', 'HIGH%')
			->orWhere('rule->severe',TRUE) 
			->get();

but got an error because table Flags doesn't contain a column called 'rule'. I also tried using an orWhereHas query:

$flagList = $this->flags()
			->where('message', 'like', 'HIGH%')
			->orWhereHas('rule',function($q) {
					$q->where('severe',TRUE);
				})
			->get();

but that didn't work either -- I got all high-severity flags on all products, not just the current one. How do I do this query?

Thanks.

0 likes
3 replies
Jsanwo64's avatar
Jsanwo64
1 month ago
Best Answer
Level 11

Your orWhereHas() attempt is very close. The issue is operator precedence.

When you do this on a relationship query:

$this->flags()
    ->where('message', 'like', 'HIGH%')
    ->orWhereHas('rule', function ($q) {
        $q->where('severe', true);
    })

it effectively becomes:

(product_id = current_product AND message LIKE 'HIGH%')
OR
(exists rule where severe = true)

So the OR escapes the product constraint and starts matching flags from other products too. Laravel’s docs explicitly warn that chaining orWhere-style clauses after relationship constraints requires logical grouping.

Use a grouped where(...) closure so both severity checks stay inside the current product’s flag relationship:

$flagList = $this->flags()
    ->where(function ($query) {
        $query->where('message', 'like', 'HIGH%')
              ->orWhereHas('rule', function ($q) {
                  $q->where('severe', true);
              });
    })
    ->get();

That keeps the query shaped like:

product_id = current_product
AND
(
    message LIKE 'HIGH%'
    OR EXISTS (
        SELECT * FROM rules
        WHERE rules.id = flags.rule_id
          AND severe = true
    )
)

So this will return only this product’s flags, where either:

  1. the old-format flag says high severity in message, or

  2. the related rule has severe = true

Also, this part was never going to work:

->orWhere('rule->severe', TRUE)

because rule is a relationship, not a column on the flags table. To filter by related model data in Eloquent, you need whereHas / orWhereHas rather than dotting into a relationship as if it were a JSON column.

https://laravel.com/docs/12.x/eloquent-relationships https://laravel.com/docs/12.x/queries https://laravel.com/docs/12.x/collections

jswoolf01's avatar

A thorough and easy-to-understand answer, jsanwo64. Thanks. I had a feeling it was a precedence issue, but didn't know how to fix it. .

1 like

Please or to participate in this conversation.