[sql] How to select from subquery using Laravel Query Builder?

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

- SQL

 SELECT COUNT(*) FROM 
 (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.

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.*');

or

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

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.

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;

I like doing something like this:

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

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

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

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`