16

Edit:

Though this question originally was specific for the query I'm describing underneath, the answer I got applies to almost all questions related to using derived tables / subqueries in Laravel

Original Question:

Lately I'm a bit stuck on the laravel query builder. It has some really nice features but I feel like it just isn't build for more complex database operations.

This is the query I'm trying to build:

select 

'IFNULL(counted.product_count, 0) AS product_count', 
'uncounted.value', 
'uncounted.attribute_id', 
'uncounted.attribute_option_id' 

    from ( 

        select
        'counted.id', 
        'counted.attribute_id', 
        'counted.value', 
        'count(counted.attribute_id) AS product_count'

        from `attribute_options` as `counted` 
        where `counted.product_id` in (?, ?, ?, ?, ?) 
        group by `counted.attribute_option_id` 

    ) as 'counted' 

right join 'attribute_options' as 'uncounted'
        on 'counted.id' = 'uncounted.id' 

  group by 'attribute_option_id'

Explanation of the query: I'm building a faceted search for my product catalog in laravel. Products are narrowed down based on the filters/attributes users provide. For better user experience I want to show the amount of products left for each filter, that's what the query above does: counting all the products for a certain attribute WHERE the product_id is IN an array of product id's.

My try:

    $productIds = [ 1, 2, 3, 4, 5 ];

    $subQuery = \DB::table('attribute_options')->selectRaw('counted.id, counted.attribute_id, counted.value, count(counted.attribute_id) AS product_count')
                    ->from('attribute_options AS counted')
                    ->whereIn('counted.product_id', $productIds)
                    ->groupBy('counted.attribute_option_id')
                    ->mergeBindings($subQuery);

    $query = Model::selectRaw('IFNULL(counted.product_count, 0) AS product_count, uncounted.value, uncounted.attribute_id, uncounted.attribute_option_id')
                    ->from(\DB::raw(' ( ' . $subQuery->toSql() . ' ) AS counted '))
                    ->rightJoin('attribute_options AS uncounted', 'counted.id', '=', 'uncounted.id')
                    ->groupBy('attribute_option_id')
                    ->get();

Please help me because I don't like to use a DB::raw() or DB::select() statement. That wouldn't feel "Laravelish" or "Eloquent".

Luuk Van Dongen
  • 2,391
  • 6
  • 26
  • 40
  • Well, you're definitely going to have to use at least one DB::raw() statement, for the ifnull select. As for the rest, I suggest posting what you've already tried. Please don't expect us to do all of the work for you. – Joel Hinz Feb 04 '15 at 10:47
  • Dear Joel, I'm definitely not trying to let you do all the work for me! I've tried so many things the past week, but I just can't seem to find the right way to translate this SQL to use the query builder. I'm aware that the IFNULL would need a selectRaw() statement. I'll post what I have tried. – Luuk Van Dongen Feb 04 '15 at 10:52
  • Much better, have an upvote. :) I'm sorry I can't help you with this question, but I hope somebody else can. – Joel Hinz Feb 04 '15 at 11:19
  • You can consider a database view and you can create a migration to create the view by the way. – Hazem Mohamed Dec 20 '20 at 14:46
  • Note that you can use `DB::table($subQuery, 'counted')` or `DB::from($subQuery, 'counted')` with a builder and it will merge the bindings automatically. I have found this to be a better approach than using a model for the wrapping builder, because it sometimes has scopes attached to it which will confuse the aggregate query. – datashaman Oct 18 '21 at 15:59

1 Answers1

27

Your first try looks pretty close. Try this:

I removed the long namespace reference and suggest you add a use statement to make your code more readable

$productIds = [ 1, 2, 3, 4, 5 ];

$subQuery = DB::table('attribute_options AS counted')->selectRaw('counted.id, counted.attribute_id, counted.value, count(counted.attribute_id) AS product_count')
                ->whereIn('counted.product_id', $productIds)
                ->groupBy('counted.attribute_option_id')

$query = AttributeOption::selectRaw('IFNULL(counted.product_count, 0) AS product_count, uncounted.value, uncounted.attribute_id, uncounted.attribute_option_id')
                ->from(\DB::raw(' ( ' . $subQuery->toSql() . ' ) AS counted '))
                ->mergeBindings($subQuery->getQuery())
                ->rightJoin('attribute_options AS uncounted', 'counted.id', '=', 'uncounted.id')
                ->groupBy('attribute_option_id')
                ->get();
Akshay Kulkarni
  • 722
  • 2
  • 10
  • 16
lukasgeiter
  • 147,337
  • 26
  • 332
  • 270
  • Okay wow! that does the trick, thanks very much! Is it possible to do this trick in multiple nested selects/subqueries? Do I just need to merge the bindings before using get()? – Luuk Van Dongen Feb 04 '15 at 12:23
  • I noticed you have already posted a [similar question](http://stackoverflow.com/questions/28283483/eloquent-how-to-use-the-query-builder-for-derived-tables) Is this now solved too? If yes I'd suggest you delete the older question. – lukasgeiter Feb 04 '15 at 12:33
  • That's true. Tough that was more in general than this question your answer applies to both of them. I'll edit this question to be more general about using derived tables in laravel/eloquent so other people might find their answer here as well. I'll delete my other question – Luuk Van Dongen Feb 04 '15 at 12:37
  • `mergeBindings` needs an instance of `Database\Query\Builder` and not a `Database\Eloquent\Query\Builder` so I couldn't get this to work with a `$subQuery` that was a `hasMany` on a model. After refactoring into normal builders this worked great – chiliNUT Aug 21 '18 at 07:12
  • @lukasgeiter when doing the same I am getting Call to undefined method Illuminate\\Database\\Query\\Builder::getQuery() – Rohannn Singh Feb 16 '23 at 04:30