在我的数据库中获得顶级克隆者？

I wondered if anyone could at least help me find where to start with this. I understand its a large question but I have looked for a while and I can't find anything related to how to do with this two columns.

I have a table called 'users', I want to select the top 20 with the most cloned accounts. When I say cloned accounts, I mean that I want to show a list of user's usernames who have the most accounts with the same IP or Machine ID.

The IP column is called ip_last and the machine column is called machine_id.

How would I go about getting the top 20 with the most cloned accounts? All I want to display is the username, how many accounts cloned by IP, and how many accounts cloned by Machine.

Id accept knowing how to do this in MySQL, but if someone could help me get it working with Laravel then that would also help a lot.

Can you try this?

$result = DB::table('users')
             ->select('ip_last', DB::raw('count(*) as total'))
             ->groupBy('ip_last')
             ->take(20);

The same is for machine_id, and for both you can try ->groupBy('ip_last','machine_id').

It's not tested, but this should to the trick:

DB::table("users")
->select("username", DB::raw("COUNT(*) AS `cloned`"))
->whereIn("ip_last", function ($query) {
    $query->select("ip_last")
        ->from("users")
        ->groupBy("ip_last")
        ->havingRaw("COUNT(*) > 1");
})
->orWhereIn("machine_id", function ($query) {
    $query->select("machine_id")
        ->from("users")
        ->groupBy("machine_id")
        ->havingRaw("COUNT(*) > 1");
})
->orderBy("cloned", "DESC")
->limit(20);