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

Question

I am using Laravel 5.5 .

I have a database containing users. The problem is that some users exist more than once due to an error. I want to query my database and select all the "unique" users.

Using the word "unique" , I mean the following:

If the email user exists 50 times, I need the line that is closest to . "[e-mail]"created_atnow

My query, which returns all users, is written below:

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

I'm confused and I'm not sure what I should use limit, combining it with the order in the column created_at.

Any ideas?

Answer 1

Well, this is what you need to do: first, you get a table with users in the latest version of created_at '. Now you have a list of letters and dates. Then you perform a left join of all users with this 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 shared SQL (I mean, you could make better MySQL or MSSQL queries, but this should do it for you.)

Answer 2

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, contact Order By before Group Using Eloquent (Laravel)

Answer 3

groupby orderby

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

hope this helps you, if you need more information, try the link above!

Answer 4

To get the most recent user entry among duplicates, you can use 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 will 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