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?

Give it a try before asking how to do something, sorry Peter but SO isn't coding request website! You can find in Laravel's docs a lot of things about this. — GiamPy
– GiamPy, Commented Mar 30, 2017 at 14:19
I have and have drawn a blank. If you read the question I asked how subqueries work, I didn't ask for someone to give me the solution. Looking to learn here not just be given code. — Peter Astbury
– Peter Astbury, Commented Mar 30, 2017 at 14:58
Thanks for asking this question, i was looking solution of this issue in Lumen. Thanks again :) — Kamlesh
– Kamlesh, Commented Feb 7, 2019 at 13:38

Zedzdead · Accepted Answer · 2017-12-11 05:49:48Z

10

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

edited Dec 11, 2017 at 5:49

Zedzdead

3543 silver badges19 bronze badges

answered Mar 30, 2017 at 17:03

Eric Tucker

6,3851 gold badge28 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SUB0DH · Accepted Answer · 2017-03-30 15:18:56Z

7

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

answered Mar 30, 2017 at 15:18

SUB0DH

5,2605 gold badges31 silver badges47 bronze badges

2 Comments

Peter Astbury Over a year ago

thanks for this - do subqueries have to go inside a where clause or can I also use them in a from clause?

SUB0DH Over a year ago

I'm not completely sure about that. You could try and see if that works.

Collectives™ on Stack Overflow

Using subqueries in Eloquent/Laravel

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related