Select last row belongs to user from another table

I have a table for users:

id | name
1  | John
1  | Mark

2nd table user_tests:

id | completed | user_id
1  |      0    |      1    
2  |      1    |      2   
3  |      0    |      2

I want to get the last row for each user from user_tests:

user_id | completed
    1   |      0
    2   |      0

I tried this query:

User::query()
    ->with(['user_tests' => function($q){
        $q->orderBy('id','DESC')->take(1);
    }])
    ->orderBy('id')
    ->get();

But if the user has 1 row in user_tests, It returns no rows.

LaryAI

3 years ago

Level 58

The issue with the current query is that it uses the take(1) method, which limits the number of results to one. Instead, we can use a subquery to get the latest row for each user in the user_tests table, and then join it with the users table to get the user's name. Here's an example query:

SELECT u.id AS user_id, u.name, ut.completed
FROM users u
JOIN (
    SELECT user_id, MAX(id) AS latest_test_id, completed
    FROM user_tests
    GROUP BY user_id
) ut ON u.id = ut.user_id

To convert this to a Laravel query, we can use the DB facade:

$results = DB::table('users as u')
    ->join(DB::raw('(SELECT user_id, MAX(id) AS latest_test_id, completed FROM user_tests GROUP BY user_id) ut'), function ($join) {
        $join->on('u.id', '=', 'ut.user_id');
    })
    ->select('u.id as user_id', 'u.name', 'ut.completed')
    ->get();

This will return a collection of objects with user_id, name, and completed properties.

mdev11

3 years ago

Level 1

@LaryAI SQLSTATE[42000]: Syntax error or access violation: 1055 'user_tests.completed' isn't in GROUP BY

Randy_Johnson

3 years ago

Level 8

I would recommend for you to learn Eloquent, it will save you a lot of difficulties.

Please or to participate in this conversation.