How to select from subquery using Laravel Query Builder?

SqlLaravelLaravel 4EloquentQuery Builder

Sql Problem Overview

I'd like to get value by the following SQL using Eloquent ORM.


 (SELECT * FROM abc GROUP BY col1) AS a;

Then I considered the following.

- Code

 $sql = Abc::from('abc AS a')->groupBy('col1')->toSql();
 $num = Abc::from(\DB::raw($sql))->count();
 print $num;

I'm looking for a better solution.

Please tell me simplest solution.

Sql Solutions

Solution 1 - Sql

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

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


Solution 2 - Sql

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) {
}, 'a')->count();

The above snippet produces the following SQL:

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

Solution 3 - Sql

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(
    DB::raw('sum( qty ) as qty'), 
$other->mergeBindings( $sub );
print $other->toSql();

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

Solution 4 - Sql

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

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


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

Solution 5 - Sql

Correct way described in this answer: Most popular answer at current moment is not totally correct.

This way 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'))
$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.

Solution 6 - Sql

There are many readable ways to do these kinds of queries at the moment (Laravel 8).

// option 1: DB::table(Closure, alias) for subquery
$count = DB::table(function ($sub) {
    }, 'a')

// option 2: DB::table(Builder, alias) for subquery
$sub   = DB::table('abc')->groupBy('col1');
$count = DB::table($sub, 'a')->count();

// option 3: DB::query()->from(Closure, alias)
$count = DB::query()
    ->from(function ($sub) {
    }, 'a')

// option 4: DB::query()->from(Builder, alias)
$sub   = DB::table('abc')->groupBy('col1');
$count = DB::query()->from($sub, 'a')->count();

For such small subqueries, you could even try fitting them in a single line with PHP 7.4's short closures but this approach can be harder to mantain.

$count = DB::table(fn($sub) => $sub->from('abc')->groupBy('col1'), 'a')->count();

Note that I'm using count() instead of explicitly writing the count(*) statement and using get() or first() for the results (which you can easily do by replacing count() with selectRaw(count(*))->first()).

The reason for this is simple: It returns the number instead of an object with an awkwardly named property (count(*) unless you used an alias in the query)

Which looks better?

// using count() in the builder
echo $count;

// using selectRaw('count(*)')->first() in the builder
echo $count->{'count(*)'};

Solution 7 - Sql

I like doing something like this:

->from(DB::raw("( SELECT * FROM `messages`
                  WHERE `to_id` = ".Auth::id()." AND `isseen` = 0
                  GROUP BY `from_id` asc) as `sub`"))

It's not very elegant, but it's simple.

Solution 8 - Sql

I could not made your code to do the desired query, the AS is an alias only for the table abc, not for the derived table. Laravel Query Builder does not implicitly support derived table aliases, DB::raw is most likely needed for this.

The most straight solution I could came up with is almost identical to yours, however produces the query as you asked for:

$sql = Abc::groupBy('col1')->toSql();
$count = DB::table(DB::raw("($sql) AS a"))->count();

The produced query is

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

Solution 9 - Sql

This works fine

$q1 = DB::table('tableA')->groupBy('col');

$data = DB::table(DB::raw("({$q1->toSql()}) as sub"))->mergeBindings($q1)->get();

Solution 10 - Sql

->selectRaw('your subquery as somefield')

Solution 11 - Sql

Deriving off mpskovvang's answer, here is what it would look like using eloquent model. (I tried updating mpskovvang answer to include this, but there's too many edit requests for it.)

$qry = Abc::where('col2', 'value')->groupBy('col1')->selectRaw('1');
$num = Abc::from($qry, 'q1')->count();
print $num;


SELECT COUNT(*) as aggregate FROM (SELECT 1 FROM Abc WHERE col2='value' GROUP BY col1) as q1


All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
Questionquenty658View Question on Stackoverflow
Solution 1 - SqlJarek TkaczykView Answer on Stackoverflow
Solution 2 - SqlmpskovvangView Answer on Stackoverflow
Solution 3 - SqlThiago MataView Answer on Stackoverflow
Solution 4 - SqlSasa BlagojevicView Answer on Stackoverflow
Solution 5 - SqldkopView Answer on Stackoverflow
Solution 6 - SqlIGPView Answer on Stackoverflow
Solution 7 - SqlGuy MazuzView Answer on Stackoverflow
Solution 8 - Sqlpeter.babicView Answer on Stackoverflow
Solution 9 - SqlPrasad kvView Answer on Stackoverflow
Solution 10 - SqlRaz GalstyanView Answer on Stackoverflow
Solution 11 - SqlJohn CView Answer on Stackoverflow