How to get "unique" users by combining email and date

Question

I am using Laravel 5.5.

I have a database which contains users. The problem is that some users exist more than one time because of a bug. I want to query my database and select all "unique" users.

By using the word "unique" I mean the below :

If a user with email "test@test.com" exists 50 times I want the row that created_at is closest to now.

My query, which returns all users is written below :

DB::table('users')
  ->select('name', 'surname', 'email', 'phone', 'answers', 'newsletter', 'created_at')
  ->get();

I got confused and I'm not sure if i should use limit combining it with order by created_at column.

Any ideas?

try using (group by) on email with orderby – Gaurav Gupta Dec 13 '17 at 10:13 — Gaurav Gupta, Dec 13 '17 at 10:13

Amarnasan · Accepted Answer · 2017-12-13T11:53:07.203

3

Ok, this is what you need to do: First off, you get a table with the users in their last created_at 'version'. Now you have a list of emails and dates. Then you perform a left join of all the users with that temporary table.

TL;DR:

    $users = DB::select('select t1.* from users t1 right join (SELECT email, MAX(created_at) as created_at from users group by email) as t2 on t1.email=t2.email and t1.created_at=t2.created_at');

I hate raw SQL, and I hate subqueries, but this is the only way I know using generic SQL (I mean, you could do a better MySQL or MSSQL native queries, but this should do for you.)

edited Dec 13 '17 at 11:53

answered Dec 13 '17 at 11:17

Amarnasan

14,939
5
33
37

This does return users but i think it needs a modification. I have a total of 8000 users but only ~900 are "unique". Your `$users` has the 900 users but also has the rest 7100 - but they don't have the `email`. Could i just have the 900? – Antonios Tsimourtos Dec 13 '17 at 11:50
Shoot, you're right. use right join instead of left join and only t1.* instead of * ... query updated – Amarnasan Dec 13 '17 at 11:53

Ankur Tiwari · Answer 2 · 2017-12-13T10:22:10.367

0

You can use

DB::table('users')->select('name', 'surname', 'email','phone','answers','newsletter','created_at')->orderBy('created_at', 'desc')->groupBy('email')->get();

For more help refer Order By before Group By using Eloquent (Laravel)

edited Dec 13 '17 at 10:22

answered Dec 13 '17 at 10:15

Ankur Tiwari

2,762
2
23
40

Gaurav Gupta · Answer 3 · 2017-12-13T10:32:12.933

0

What you need is groupby and orderby

try this code

DB::table('users')->select('name', 'surname', 'email','phone','answers','newsletter','created_at')
                  ->orderBy('created_at', 'desc')
                  ->groupBy('email')
                  ->get();

hope it will help you if you need further info try above link!

edited Dec 13 '17 at 10:32

answered Dec 13 '17 at 10:18

Gaurav Gupta

1,588
2
14
21

It's much complex than that. – Amarnasan Dec 13 '17 at 10:40
@Amarnasan can you further explain your comment? – Manish Yadav Dec 13 '17 at 10:42
Of course. First off, you will get a nice SQL error because of trying to get fields not in the GROUP BY directive. – Amarnasan Dec 13 '17 at 10:49
1

Oh my god... run that query and tell me. Have you even tried it? "Column 'name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." – Amarnasan Dec 13 '17 at 11:04

score 0 · Answer 4 · answered Dec 13 '17 at 12:06

To get latest user record among duplicates you can use a self join

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

In plain SQL it would be something like

select u.*
from users u
left join users u1 on u.email = u1.email
and u.created_at < u1.created_at
where u1.id is null

How to get "unique" users by combining email and date

4 Answers4