Laravel: how to use derived tables / subqueries in the laravel query builder

Question

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".

Midlands

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. – Semblable

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. – Midlands

Much better, have an upvote. :) I'm sorry I can't help you with this question, but I hope somebody else can. – Semblable

You can consider a database view and you can create a migration to create the view by the way. – Telemann

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. – Historiography

Answer 1

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();

Baran

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()? – Midlands

I noticed you have already posted a similar question Is this now solved too? If yes I'd suggest you delete the older question. – Baran

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 – Midlands

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 – Alkene

@Baran when doing the same I am getting Call to undefined method Illuminate\\Database\\Query\\Builder::getQuery() – Biradial