Jean-Luc Tallis Jean-Luc Tallis - 1 year ago 349
MySQL Question

Laravel Eloquent - Select MAX with other columns

I'm trying to select a number of columns along with MAX. The raw query would be something like:

SELECT, ..., MAX(ur.rank) AS rank
but I cannot figure out how to do it using the query builder supplied by Laravel in Eloquent.

This is my attempt:

$user = User::join('users_ranks AS ur', function($join) {
$join ->on('ur.uid', '=', '');
->where('', '=', 7)
->first(['', 'users.username', 'MAX(ur.rank) AS rank']);

I simply cannot figure it out. What I want to achieve is I'm selecting a user where = 7, and I'm wanting to select the MAX rank that's in users_ranks where their users_ranks.uid =

I was told to avoid sub-queries as when working with large result sets, it can slow things down dramatically.

Can anyone point me in the right direction? Thanks.

Answer Source

I think you should rewrite it like this:

    ->select(['', 'users.username', DB::raw('MAX(ur.rank) AS rank')])
    ->leftJoin('users_ranks AS ur', 'ur.uid', '=', '')
    ->where('', '=', 7)

No sense to use User:: if you use table names later and want to fetch not all of the fields ( '', 'users.username' ).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download