CODEWITHSUNDEEP
phpmysqllaraveleloquent
Jean-Luc Tallis
Jean-Luc Tallis

Reputation: 325

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 users.id, ..., 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', '=', 'users.id');
})
->where('users.id', '=', 7)
->groupBy('users.id')
->first(['users.id', '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 users.id = 7, and I'm wanting to select the MAX rank that's in users_ranks where their users_ranks.uid = users.id.

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.

Upvotes: 3

Views: 27084

Answers (1)

M0rtiis
M0rtiis

Reputation: 3784

I think you should rewrite it like this:

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

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

Upvotes: 6

Related Questions