You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
eager loading ChatRoom::query()->with('unseen_messages')->get() such relation will produce this query:
select * from `chat_messages`
left join `chat_user_pivot` on `chat_user_pivot`.`room_id` = `chat_messages`.`room_id` and `chat_user_pivot`.`user_id` = ?
where `chat_messages`.`created_at` > `chat_user_pivot`.`updated_at` and `chat_messages`.`room_id` in (3) and `chat_messages`.`deleted_at` is null
this query works well
but using withCount on the same relation throws an sql error (debugged with enableQueryLog):
Unknown column 'chat_user_pivot.updated_at' in 'where clause'
(SQL: select `chat_rooms`.*,
(select count(*) from `chat_messages` where `chat_rooms`.`id` = `chat_messages`.`room_id` and `chat_messages`.`created_at` > `chat_user_pivot`.`updated_at` and `chat_messages`.`deleted_at` is null)
as `unseen_messages_count` from `chat_rooms` where `chat_rooms`.`id` = 3 and `chat_rooms`.`deleted_at` is null limit 1
clearly the join part in the relation is excluded when creating the count query.
This is clearly a bug
this can be overcome by putting the join part in eager loading callback function like so:
ChatRoom::query()->withCount(['unseen_messages' => function ($q) {
$q->leftJoin('chat_user_pivot', function (JoinClause$join) {
$join->on('chat_user_pivot.room_id', '=', 'chat_messages.room_id');
$join->where('chat_user_pivot.user_id', Auth::user()->id);
})
->where('chat_messages.created_at', '>', DB::raw('`chat_user_pivot`.`updated_at`'));
}])->get()
but!
I am building an API and I have some kind of automatic query system which I would like to work on counts too.
So I would appreciate if anyone could guide me to the right path to resolve this in Laravel code iteself and create a pull request maybe!
reacted with thumbs up emoji reacted with thumbs down emoji reacted with laugh emoji reacted with hooray emoji reacted with confused emoji reacted with heart emoji reacted with rocket emoji reacted with eyes emoji
-
Hi
Consider this relation, a relation with a join on a pivot table
eager loading
ChatRoom::query()->with('unseen_messages')->get()
such relation will produce this query:this query works well
but using withCount on the same relation throws an sql error (debugged with enableQueryLog):
clearly the join part in the relation is excluded when creating the count query.
This is clearly a bug
this can be overcome by putting the join part in eager loading callback function like so:
but!
I am building an API and I have some kind of automatic query system which I would like to work on counts too.
So I would appreciate if anyone could guide me to the right path to resolve this in Laravel code iteself and create a pull request maybe!
Beta Was this translation helpful? Give feedback.
All reactions