conditions in eager loading in eloquent laravel

Question

I get my Product be this code. I use with() method to load variations relation. I have some filters for it. But if any variations does't exist I get Product.

How can I get only this Product where variations exist ?

$query = Product::with(array(
            'variations' => function($query) use($filters){
                if(isset($filters['price_start']) && !empty($filters['price_start'])){
                    $query->groupBy('id')->having(DB::raw('SUM(price_base + price_engraving)'), '>=', $filters['price_start']);
                }
                if(isset($filters['price_end']) && !empty($filters['price_end'])){
                    $query->groupBy('id')->having(DB::raw('SUM(price_base + price_engraving)'), '<=', $filters['price_end']);
                }

                if(isset($filters['q_magazine_start']) && !empty($filters['q_magazine_start'])){
                    $query->where('q_magazine', '>', $filters['q_magazine_start']);
                }
                if(isset($filters['q_magazine_end']) && !empty($filters['q_magazine_end'])){
                    $query->where('q_magazine', '<', $filters['q_magazine_end']);
                }

                return $query;
            }
        ))->whereIn('id', $productList);

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

1

To accomplish that you have to do pretty much the same thing again but with whereHas. Since the closure will be exactly the same we can put it in a variable to avoid duplicate code:

$filterClosure = function($query) use ($filters){
    if(isset($filters['price_start']) && !empty($filters['price_start'])){
        $query->groupBy('id')->having(DB::raw('SUM(price_base + price_engraving)'), '>=', $filters['price_start']);
    }
    if(isset($filters['price_end']) && !empty($filters['price_end'])){
        $query->groupBy('id')->having(DB::raw('SUM(price_base + price_engraving)'), '<=', $filters['price_end']);
    }

    if(isset($filters['q_magazine_start']) && !empty($filters['q_magazine_start'])){
        $query->where('q_magazine', '>', $filters['q_magazine_start']);
    }
    if(isset($filters['q_magazine_end']) && !empty($filters['q_magazine_end'])){
        $query->where('q_magazine', '<', $filters['q_magazine_end']);
    }
};

$query = Product::with(array('variations' => $filterClosure))
                ->whereHas('variations', $filterClosure)
                ->whereIn('id', $productList);

(By the way, there's no need to return $query at the end of the anonymous function)

Edit

As we figured out you need to change the groupBy statements to where

$filterClosure = function($query) use ($filters){
    if(isset($filters['price_start']) && !empty($filters['price_start'])){
        $query->where(DB::raw('price_base + price_engraving'), '>=', $filters['price_start']);
    }
    if(isset($filters['price_end']) && !empty($filters['price_end'])){
        $query->where(DB::raw('price_base + price_engraving'), '<=', $filters['price_end']);
    }

    // same code as above
};

edited Jun 20 '20 at 09:12

Community

1
1

answered Jan 29 '15 at 19:06

lukasgeiter

147,337
26
332
270

Now i got these error 'Subquery returns more than 1 row' – Thomas Shelby Jan 29 '15 at 20:09
Do you have more details or just that? Just for testing purposes can you quickly comment out the `whereHas()` part and see if it runs without any errors? – lukasgeiter Jan 29 '15 at 20:16
If I comment whereHas() it works. With whereHas() it generate this query SELECT * FROM `products` WHERE (SELECT COUNT(*) FROM `products_variations` WHERE `products_variations`.`deleted_at` IS NULL AND `products_variations`.`product_id` = `products`.`id` AND `products_variations`.`deleted_at` IS NULL GROUP BY `id` HAVING SUM(price_base + price_engraving) >= '10') >= 1 AND `id` IN ('346', '1', '1201', '1201') – Thomas Shelby Jan 29 '15 at 20:22
I have a feeling that the `groupBy` is messing up things. Can you try and comment that out? – lukasgeiter Jan 29 '15 at 20:26
But I use having, so I have to use groupBy. When I do not use groupBy it returns always one row. – Thomas Shelby Jan 29 '15 at 20:35
Why do you need to group by `id` anyways? Isn't `id` unique? – lukasgeiter Jan 29 '15 at 20:38
id is unique. I need have sum for each variations. But sum returns one row. Sum of all rows. I need sum, for each variations. – Thomas Shelby Jan 29 '15 at 20:50
I change $query->groupBy('id')->having(DB::raw('SUM(price_base + price_engraving)'), '<=', $filters['price_end']); to $query->where(DB::raw('price_base + price_engraving'), '<=', $filters['price_end']); and it works! – Thomas Shelby Jan 29 '15 at 20:57
1

That's what I just wanted to suggest! :) – lukasgeiter Jan 29 '15 at 20:57

conditions in eager loading in eloquent laravel

1 Answers1

Edit