Gamer Gamer - 4 months ago 7
MySQL Question

Laravel : How to count in query

I have the following query :

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


The count isn't working, Its supposed to be working !

The following error comes up :

Column not found: 1054 Unknown
column 'COUNT(gift_sents.receiver_id' in 'field list' (SQL: select
users
.*,
COUNT(gift_sents
.
receiver_id
as
total_posts
from
users
inner join
gift_sents
on
gift_sents
.
receiver_id
=
users
.
id
group by
gift_sents
.
id
order by
total_posts
asc limit 3)

Answer

Instead of:

->select('users.*', 'COUNT(gift_sents.receiver_id as total_posts')

you should use:

->selectRaw('users.*, COUNT(gift_sents.receiver_id) as total_posts')