SQL Subquery JOIN issue with Laravel Query Builder

Question

I'm stuck at understanding something melted between Laravel Query Builder and pure SQL I guess.

I have 2 tables :

User : containing users with primary key user_id
User_Action : which is a table containing records with composed PRIMARY KEYs of the user_id and the datetime of the action.

ex:

user

user_id
1

user_action

user_id    datetime              action_type
1          2017-12-01 12:10:00   y
1          2017-12-01 12:00:00   x

My need :

Now I want to retrieve a user list, with the newest action for each user. So I need to get only 1 row in the JOIN from user to user_action, and this row have to be the one with newest datetime. So in my ex; I would like to get only the record with the datetime 2017-12-01 12:10:00 with a LEFT JOIN (to retrieve user even if there's no action).

I tried the following :

$userActionSubquery = DB::table('user_action')
        ->select()
        ->orderBy('datetime', 'DESC')
        ->limit(1);
$query->leftJoinSub($userActionSubquery, 'user_action', function ($join) {
         $join->on('user_action.user_id', '=', 'user.user_id');
})->groupBy('user_id');

By doing so, I'm not getting anything from user_action table, BUT it works if the action of the given user is the newest of the table ! I thought the $join->on would filter user id but the JOIN subquery is ran without this filter, and I can't but a WHERE in subquery because I don't have a user_id to give ! it's a query to get a list of user !

PS : the query generated is

SELECT user.user_id FROM user left join (SELECT * FROM user_action ORDER BY datetime DESC LIMIT 1) as user_action on user_action.user_id = user.user_id WHERE user.tenant_id in ('7') GROUP BY `user_id`

I'm missing something to succeed, thanks for your help!

CloudyCity · Accepted Answer · 2020-09-24 16:21:53Z

1

Try this:

$sub = \DB::table('user_action')
    ->select([
        'user_id',
        \DB::raw('SUBSTRING_INDEX( GROUP_CONCAT( action_type ORDER BY datetime DESC ), ",", 1 ) AS last_action_type')
    ])
    ->groupBy('user_id');

$data = \DB::table('user')
    ->leftJoinSub($sub, 'tmp', function ($join) {
        $join->on('tmp.user_id', '=', 'user.id');
    })->get();

sql:

SELECT * FROM user LEFT JOIN ( SELECT user_id, SUBSTRING_INDEX( GROUP_CONCAT( action_type ORDER BY datetime DESC ), ',', 1 ) AS last_action_type FROM user_action GROUP BY user_id ) AS tmp ON tmp.user_id = user.id

It works in my test env:

user

id   name
1    john
2    jack

user_action

id   user_id   datetime              action_type
1    1         2020-09-24 23:51:00   login
2    1         2020-09-24 23:51:20   search
3    2         2020-09-24 23:30:00   login
4    2         2020-09-25 00:00:00   coding

result

Illuminate\Support\Collection Object
(
    [items:protected] => Array
        (
            [0] => stdClass Object
                (
                    [id] => 1
                    [name] => john
                    [user_id] => 1
                    [last_action_type] => search
                )

            [1] => stdClass Object
                (
                    [id] => 2
                    [name] => jack
                    [user_id] => 2
                    [last_action_type] => coding
                )

        )

)

answered Sep 24, 2020 at 16:21

CloudyCity

1131 silver badge6 bronze badges

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

2 Comments

Alex Over a year ago

thank you, the missing key here for me to fully understand what I was doing is to use an aggregate function into the subquery. I don't know why I was so lost at the time !

Alex Over a year ago

I did something similar than you, but I used rather select chaining syntax and MAX aggregation func : $query->select('user_id')->selectRaw('MAX(datetime) as maxDatetime')->groupBy('user_id')

Collectives™ on Stack Overflow

SQL Subquery JOIN issue with Laravel Query Builder

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related