Gamer Gamer - 4 months ago 13
MySQL Question

Laravel : highest id used in a column

I have a table

gifts_sent
with a model
GiftSent
and having the following columns.

id
,
gift_id
,
sender_id
,
receiver_id


Note :
sender_id
and
receiver_id
are foreign keys.

suppose that user with id
1
have sent 11 gifts and user with id
2
have sent 9 gifts and so on for the other users.

How can i achieve that with laravel eloquent or query builder ?

Note : Its for ranking, That which user have sent most gifts.

I have came up with the following logic but its not correct.

App\User::join('gift_sents', function($builder){
$builder->on('gift_sents.receiver_id', '=', 'users.id');
})
->select('users.*', DB::raw('COUNT(gift_sents.receiver_id) as total_posts'))
->groupBy('gift_sents.id')
->orderBy('total_posts', 'ASC')
->limit(3)->get();

Answer

First you will have to define relationship with User and GiftSent Model. Get the sender_id who has sent maximum count of gifts. Fetch all the user using relation ('with').

App\GiftSent::with('userRelationName')
->select('sender_id', DB::raw('COUNT(*) as giftCount'))
->groupBy('sender_id')
->orderBy(DB::raw('COUNT(*)'))
->get();

Other solution is to use join but it will be complicated and costly.