CODEWITHSUNDEEP
phpmysqllaraveleloquent
Peter Astbury
Peter Astbury

Reputation: 115

Using subqueries in Eloquent/Laravel

Here's the query in raw SQL:

SELECT * 
FROM (
    SELECT `characters`.`id`,`characters`.`refreshToken`,
           `characters`.`name`,max(`balances`.`created_at`) as `refreshDate`
        FROM `characters`
        INNER JOIN `balances` ON `characters`.`id` = `balances`.`character`
        WHERE `characters`.`refreshToken` IS NOT NULL
        GROUP BY `characters`.`id`
) AS `t1`
WHERE `refreshDate` < '2017-03-29';

I've tested this in phpMyAdmin and it returns the expected results. However I'm using the Eloquent and Laravel libraries in my PHP app and I'm not sure how to approach this. How exactly do subqueries work in this case?

Upvotes: 7

Views: 18386

Answers (2)

Eric Tucker
Eric Tucker

Reputation: 6335

You can do a subquery as a table but need to create the subquery first and then merge the bindings into the parent query:

$sub = Character::select('id', 'refreshToken', 'name')
    ->selectSub('MAX(`balances`.`created_at`)', 'refreshDate')
    ->join('balances', 'characters.id', '=', 'balances.character')
    ->whereNotNull('characters.refreshToken')
    ->groupBy('characters.id');

DB::table(DB::raw("($sub->toSql()) as t1"))
    ->mergeBindings($sub)
    ->where('refreshDate', '<', '2017-03-29')
    ->get();

If that is your entire query you can do it without the subquery and use having() instead like:

Character::select('id', 'refreshToken', 'name')
    ->selectSub('MAX(`balances`.`created_at`)', 'refreshDate')
    ->join('balances', 'characters.id', '=', 'balances.character')
    ->whereNotNull('characters.refreshToken')        
    ->groupBy('characters.id')
    ->having('refreshDate', '<', '2017-03-29');

Upvotes: 10

SUB0DH
SUB0DH

Reputation: 5240

You can use subqueries in Eloquent by specifying them as a closure to the where method. For example:

$characters = Character::where(function ($query) {
    // subqueries goes here
    $query->where(...
          ...
          ->groupBy('id');
})
->where('refreshDate', '<', '2017-03-29')
->get();

You have to chain your methods to the $query variable that is passed to the closure in the above example.

If you want to pass any variable to the subquery you need the use keyword as:

$characterName = 'Gandalf';

$characters = Character::where(function ($query) use ($characterName) {
    // subqueries goes here
    $query->where('name', $characterName)
          ...
          ->groupBy('id');
})
->where('refreshDate', '<', '2017-03-29')
->get();

Upvotes: 7

Related Questions