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.

Oleneolenka

I just figured out how you can do the equivalent of a SELECT ... AS in eloquent; by providing the column name as the key in an array provided to ->addSelect. E.g. $queryBuilder->addSelect([ 'name_of_generated_column' => DB::table('table_name') ->selectRaw(1) ->whereNotNull('example_column_in_example_subquery') ->limit(1) ]; You can then use the generated column name in $queryBuilder->get(['name_of_generated_column']); – Breaker

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

Thicken

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

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? – Beagle

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

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

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

@Bs 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. – Thicken

@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. – Bs

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

@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? – Bs

@Bs 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() – Thicken

@JarekTkaczyk Thanks, it really did work! Appreciated! – Bs

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

@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. – Thicken

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

@Kannan No idea what you're referring to? – Thicken

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

@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.. – Thicken

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

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

Answer 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`

Orlena

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

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

Answer 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

Shamikashamma

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

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

Delivery

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

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

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

Answer 5

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) {
        $sub->from('abc')
            ->groupBy('col1');
    }, 'a')
    ->count();

// 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) {
        $sub->from('abc')
            ->groupBy('col1')
    }, 'a')
    ->count();

// 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(*)'};

Goshen

Answer 6

Correct way described in this answer: https://mcmap.net/q/136767/-how-to-select-from-subquery-using-laravel-query-builder Most popular answer at current moment is not totally correct.

This way https://mcmap.net/q/136767/-how-to-select-from-subquery-using-laravel-query-builder 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.

Microphyte

Answer 7

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.

Grochow

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

Answer 8

This works fine

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

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

Spiritualist

Answer 9

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;

Polished

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! – Oleneolenka

Answer 10

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;

Produces...

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

Fairlead

Answer 11

->selectRaw('your subquery as somefield')

Paly

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

Answer 12

$sub_query = DB::table('abc')->select(*)->groupby('col1');

$main_query = DB::table($sub_query,'S')->selectRaw("count(*)")

NOTE:- 'S' is alias for $sub_query

Walloping

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Govea