I'm building a service where users can upload three types of content.

• Snippets
• Files
• Urls (shortened)

All three tables has a FK user_id, and relations defined in the model. A User has many snippets, files and urls. And they belong to one user.

Now I've come to the part where I have to create an administration panel where I can get an overview of things. And I'll start with getting three lists:

1. A list of users who created the most snippets
2. A list of users who created the most files
3. [...] the most shortened urls

Tech time

As of right now I've got something along the lines of:

$snippetsUsers = User::select(['users.*', \DB::raw('count(snippets.id) as snippetscount')])
    ->join('snippets', 'users.id', '=', 'snippets.user_id')
    ->groupBy('users.id')
    ->orderBy('snippetscount', 'desc')
    ->limit(15)
    ->get();

As of right now, this outputs a few diffrent results every time I reload, so I assume it's not correct since there is no activity on my local site.

A little extra

The above approach is using the Query Builder. But I was thinking if there is a way to use the magnificent power of eloquent? Here is what I was thinking (this code is not functional):

$snippetsUsers = User::with('snippets')
    ->orderBy(\DB::raw('COUNT(snippets.id)'))
    ->get();

Any ideas are welcome!