Laravel Inner join the last value of the right table

Question

I am trying to get the last value of the login log of my user but I can't do it with this code:

DB::table('users')
    ->leftJoin('login_logs', 'login_logs.id', '=', function($query) {
        $query->select('login_logs.id')
            ->where('users.id', '=','login_logs.user_id')
            ->orderBy('login_logs.created_at')
            ->limit(1);
    })->get();

I got 2 tables users and login log I want to get all users with the last login log can someone give me a hand? I am new in Laravel.

Edit: i got this code for the sql:

SELECT c.*, o.* 
FROM users c 
INNER JOIN login_logs o ON o.id = (
SELECT id 
FROM login_logs 
WHERE login_logs.users_id = c.id 
ORDER BY id DESC 
LIMIT 1)

Edit2: this code work for me

DB::table('users')
->leftJoin('login_logs', 'login_logs.id', '=', (DB::RAW('(
SELECT id FROM login_logs 
WHERE login_logs.user_id = users.id 
ORDER BY login_logs.id DESC 
LIMIT 1)')))
->select('users.*','login_logs.created_at as lastLogin','login_logs.type')
->get()

some best ideas?:

i am using datatables https://github.com/yajra/laravel-datatables/ and this not work with models :( — Jarloyz Juarez, Jun 14 '18 at 16:25

score 0 · Answer 1 · answered Jun 13 '18 at 23:10

0

Try something like this:

DB::table('users')
            ->join('login_logs', 'login_logs.user_id', '=', 'users.id')
            ->order_by('login_logs.id', 'desc')
            ->first();

answered Jun 13 '18 at 23:10

Shoukat Mirza

800
9
19

it only return one i want return all users with the last login date – Jarloyz Juarez Jun 14 '18 at 16:26
remove ->first() – Shoukat Mirza Nov 19 '18 at 08:11

score 0 · Accepted Answer · answered Jun 14 '18 at 06:15

0

To get latest record from login log table for each user you can use following self joined query

select u.*, l.*
from users u
join login_logs l on u.id = l.user_id
left join login_logs l1 on l.user_id = l1.user_id
and l.created_at < l1.created_at
where l1.user_id is null

Using query builder you might rewrite it as

DB::table('users as u')
  ->select('u.*', 'l.*')
  ->join('login_logs as l', 'u.id', '=', 'l.user_id')
  ->leftJoin('login_logs as l1', function ($join) {
        $join->on('l.user_id', '=', 'l1.user_id')
             ->whereRaw(DB::raw('l.created_at < l1.created_at'));
   })
  ->whereNull('l1.user_id')
  ->get();

Laravel Eloquent select all rows with max created_at

Laravel - Get the last entry of each UID type

Laravel Eloquent group by most recent record

answered Jun 14 '18 at 06:15

M Khalid Junaid

63,861
10
90
118

thanks for the reply, this is my sql code working SELECT c.*, o.* FROM users c INNER JOIN login_logs o ON o.id = (SELECT id FROM login_logs WHERE login_logs.users_id = c.id ORDER BY id DESC LIMIT 1) how i can use it in laravel? – Jarloyz Juarez Jun 14 '18 at 16:33
@JarloyzJuarez why do you want to run that query instead of what i have addressed in my answer, Give it a try and let me if you still don't get the right data or in case of error – M Khalid Junaid Jun 14 '18 at 21:47
sorry about that, you code is not working, the code return all login log for each users – Jarloyz Juarez Jun 14 '18 at 22:50
@JarloyzJuarez I doubt that , this can't be true , Also can you give us a favor and include sample data set for your tables and table definitions using http://sqlfiddle.com/ or any other online tool or may be you can add in your question, So that i can verify what you stated is correct or not – M Khalid Junaid Jun 14 '18 at 22:53
1

oh men sorry i see my fault, some of my data log got the same datetime and that was the reaseon why you query was not working i fixed id changing l.created_at < l1.created_at for l.id < l1.id Thanks a lot – Jarloyz Juarez Jun 15 '18 at 15:48

score 0 · Answer 3 · answered Jun 14 '18 at 06:43

0

Try this code. Inner Query will provide your last login detail of each user and join it by Table(Id) with LEFT JOIN. So it will answer only your required user's details. You may add WHERE CONDITION at Last to get Specific Id(User) detail

SELECT U.ID,
    LOGS.CREATED_AT
FROM USERS AS U
    LEFT JOIN
        (
            SELECT ID,
                MAX(CREATED_AT) AS CREATED_AT
            FROM LOGIN_LOGS
            GROUP BY ID
        ) AS LOGS
    ON  LOGS.ID = U.ID
--WHERE U.ID='your_user_id'

Hope this will help you.

answered Jun 14 '18 at 06:43

Irfan

665
6
29

thanks for the reply, i got this code working SELECT c.*, o.* FROM users c INNER JOIN login_logs o ON o.id = (SELECT id FROM login_logs WHERE login_logs.users_id = c.id ORDER BY id DESC LIMIT 1) – Jarloyz Juarez Jun 14 '18 at 16:35
'ORDER BY' will slow down your Query. Currently, maybe you are working in Test Data. But having lacs of data will decrease your performance by ratio 30:70. – Irfan Jun 15 '18 at 00:35
thanks for the reply, what you think about this? SELECT c.*, o.* FROM users c INNER JOIN login_logs o ON o.id = (SELECT MAX(id) FROM login_logs WHERE login_logs.user_id = c.id GROUP BY login_logs.user_id); – Jarloyz Juarez Jun 15 '18 at 15:58
currently you are going 3-Steps ▼ usres[id]→ login_logs[id] → login_logs[user_id]. I am giving you the shortcut to proceed fast. ▲ usres[u_id]→ login_logs[u_id]. Now you choose which seems better. – Irfan Jun 16 '18 at 03:47

Laravel Inner join the last value of the right table

3 Answers3