How to select from subquery using Laravel Query Builder?

Solution 1:

In addition to @delmadord's answer and your comments:

Currently there is no method to create subquery in FROM clause, so you need to manually use raw statement, then, if necessary, you will merge all the bindings:

$sub = Abc::where(..)->groupBy(..); // Eloquent Builder instance

$count = DB::table( DB::raw("({$sub->toSql()}) as sub") )
    ->mergeBindings($sub->getQuery()) // you need to get underlying Query Builder
    ->count();

Mind that you need to merge bindings in correct order. If you have other bound clauses, you must put them after mergeBindings:

$count = DB::table( DB::raw("({$sub->toSql()}) as sub") )

    // ->where(..) wrong

    ->mergeBindings($sub->getQuery()) // you need to get underlying Query Builder

    // ->where(..) correct

    ->count();

Solution 2:

Laravel v5.6.12 (2018-03-14) added fromSub() and fromRaw() methods to query builder (#23476).

The accepted answer is correct but can be simplified into:

DB::query()->fromSub(function ($query) {
    $query->from('abc')->groupBy('col1');
}, 'a')->count();

The above snippet produces the following SQL:

select count(*) as aggregate from (select * from `abc` group by `col1`) as `a`

Solution 3:

The solution of @JarekTkaczyk it is exactly what I was looking for. The only thing I miss is how to do it when you are using DB::table() queries. In this case, this is how I do it:

$other = DB::table( DB::raw("({$sub->toSql()}) as sub") )->select(
    'something', 
    DB::raw('sum( qty ) as qty'), 
    'foo', 
    'bar'
);
$other->mergeBindings( $sub );
$other->groupBy('something');
$other->groupBy('foo');
$other->groupBy('bar');
print $other->toSql();
$other->get();

Special atention how to make the mergeBindings without using the getQuery() method

Solution 4:

From laravel 5.5 there is a dedicated method for subqueries and you can use it like this:

Abc::selectSub(function($q) {
    $q->select('*')->groupBy('col1');
}, 'a')->count('a.*');

Abc::selectSub(Abc::select('*')->groupBy('col1'), 'a')->count('a.*');

Solution 5:

Correct way described in this answer: https://stackoverflow.com/a/52772444/2519714 Most popular answer at current moment is not totally correct.

This way https://stackoverflow.com/a/24838367/2519714 is not correct in some cases like: sub select has where bindings, then joining table to sub select, then other wheres added to all query. For example query: select * from (select * from t1 where col1 = ?) join t2 on col1 = col2 and col3 = ? where t2.col4 = ? To make this query you will write code like:

$subQuery = DB::query()->from('t1')->where('t1.col1', 'val1');
$query = DB::query()->from(DB::raw('('. $subQuery->toSql() . ') AS subquery'))
    ->mergeBindings($subQuery->getBindings());
$query->join('t2', function(JoinClause $join) {
    $join->on('subquery.col1', 't2.col2');
    $join->where('t2.col3', 'val3');
})->where('t2.col4', 'val4');

During executing this query, his method $query->getBindings() will return bindings in incorrect order like ['val3', 'val1', 'val4'] in this case instead correct ['val1', 'val3', 'val4'] for raw sql described above.

One more time correct way to do this:

$subQuery = DB::query()->from('t1')->where('t1.col1', 'val1');
$query = DB::query()->fromSub($subQuery, 'subquery');
$query->join('t2', function(JoinClause $join) {
    $join->on('subquery.col1', 't2.col2');
    $join->where('t2.col3', 'val3');
})->where('t2.col4', 'val4');

Also bindings will be automatically and correctly merged to new query.