How to select from subquery using Laravel Query Builder?

Question

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.

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

or

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.

View more solutions

Comments

[quenty658]

Thank you for your reply. There is a problem in the method of "Abc::from(???) and DB::table(???)". $sql = Abc::where('id', '=', $id)->groupBy('col1')->toSql(); $count = DB::table(DB::raw("($sql) AS a"))->count(); SQL error occur in the above code. - where and parameter assign!

[quenty658]

Thank you! I didn't know mergeBindings method. Expressing subquery by code is complex...

[sulaiman sudirman]

Hi, I got following error Argument 1 passed to Illuminate\Database\Query\Builder::mergeBindings() must be an instance of Illuminate\Database\Query\Builder, instance of Illuminate\Database\Eloquent\Builder given, what did I miss?

[Jarek Tkaczyk]

@sulaiman You missed $sub->getQuery() part, which gets the underlying Query\Builder object.

[Jordi Puigdellívol]

Note that if you have a complex query as a belongsToMany as subselect you have to add getQuery() twice => $sub->getQuery()->getQuery()

[Skyzer]

Can the SQL injection be done in that $sub query, since arguments passed there are not escaped and it's used inside DB::raw

[Jarek Tkaczyk]

@Skyzer You're getting it wrong - arguments are not passed there at all, they are ? pdo placeholders as usually - there is mergeBindings part that handles their real values. To sum up - no, it's sql-injection safe in this form.

[Skyzer]

@JarekTkaczyk I don't get it. $sub->getQuery()->toSql() translates to pure SQL statement, so if there is any user input it's vulnerable because I'm using that inside DB::raw($sub->getQuery()->toSql()) which does not escape anything.

[Jarek Tkaczyk]

@Skyzer No, it doesn't translate to pure sql, but pdo prepared statement with bindings - check again.

[Skyzer]

@JarekTkaczyk does this mean when my $sub query is created with proper Eloquent, then when it's converted with toSql() it will be escaped, since the values are band properly? Even if I don't use mergeBindings but just using toSql() inside DB::raw all would be safe?

[Jarek Tkaczyk]

@Skyzer You're not reading what I write. Nothing is escaped when you call toSql. Read about PDO php.net/manual/en/book.pdo.php and see the result of your $query->toSql()

[Skyzer]

@JarekTkaczyk Thanks, it really did work! Appreciated!

[Jimmy Ilenloa]

With regards to ->mergeBindings($sub->getQuery()) just do ->mergeBindings($sub)

[Jarek Tkaczyk]

@JimmyIlenloa If the $sub query is an Eloquent Builder, then you still need the ->getQuery() part, otherwise you get error, since this method is typehinted against Query\Builder class.

[Kannan Ramamoorthy]

Hi @JarekTkaczyk .. didn't we still get the subquery from support?

[Jarek Tkaczyk]

@Kannan No idea what you're referring to?

[Kannan Ramamoorthy]

@Jarek On your statement "Currently there is no method to create subquery in FROM clause".. do we have it in laravel now?

[Jarek Tkaczyk]

@Kannan nope. it's a candidate for a PR I guess, but in the end that's not very common use case. Probably this is the reason for not having it there up to this day..

[hagabaka]

It seems that subSelect can be only used to add a sub query to SELECT, not FROM.

[hagabaka]

This is returning the correct data, but how can I get a collection of objects in the original model class?

[Tschallacka]

Awesome solution. works as advertised. Just a note, if you have an order by in the original query you need to either remove it, or set it after retrieving the count in SQL server.

[Maruf Alom]

Call to undefined method subSelect() seems like subSelect doesn't exists.

[Sasa Blagojevic]

Thanks for bringing this to my notice, I misspelt the name, it should have been selectSub. I've updated my response now.

[Wak]

Thanks this worked for me, as a side note, be careful with the select content because laravel added some quote marks and I had to use ->select(\DB::raw('Your select')) to get rid of them.

[The Onin]

Using DB::raw() did the job for me

[Steve Moretz]

Amazing I used this to make eloquent work after join without prefixing tables.(when you don't care about other results)

[WaitingForGuacamole]

Consider adding more detail and explanation to this answer, perhaps going so far as to copy part of the question's code and inserting it in context to show the OP how it would be used.

[Richard]

Really this should now be the accepted answer fromSub solves the PDO binding issue that occurs in some subqueries.