Eloquent Aggregates in HAVING Clauses for Complex Partner Filtering

When building reports with complex partner relationship logic, you can use selectRaw() with aggregate functions combined with havingRaw() to filter results based on multiple relationship conditions.

Instead of loading all relationships into memory and filtering in PHP, let the database do the heavy lifting by calculating relationship counts in your SELECT and filtering in HAVING.

This is especially powerful when you need to check:

• “Is this exclusive TO this partner?” (count exclusive = 1)
• “Is this excluded FROM this partner?” (count excluded < 1)
• “Is this exclusive to ANYONE?” (count exclusive for others > 0)

// Count partner relationships at different levels
$query->selectRaw(
    'count(if(partners.excluded = 0 AND partners.org_id = ?, 1, null)) as is_exclusive_for_me',
    [$currentOrgId]
)
->selectRaw(
    'count(if(partners.excluded = 1 AND partners.org_id = ?, 1, null)) as is_excluded_from_me',
    [$currentOrgId]
)
->selectRaw(
    'count(if(partners.excluded = 0, 1, null)) as exclusive_for_anyone'
)
->havingRaw(
    '(is_exclusive_for_me >= 1 OR (is_excluded_from_me < 1 AND exclusive_for_anyone < 1))'
);