Using subqueries in Eloquent/Laravel

Question

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?

Solution 1

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

Solution 2

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