Level 102
Oct 15, 2022
4
Level 1
Query doesn't work inside Laravel
The Eloquent query:
$users = Message::join('users', function ($join) { $join->on('ch_messages.from_id', '=', 'users.id') ->orOn('ch_messages.to_id', '=', 'users.id'); }) ->where(function ($q) { $q->where('ch_messages.from_id', Auth::user()->id) ->orWhere('ch_messages.to_id', Auth::user()->id); }) ->where('users.id','!=',Auth::user()->id) ->select('users.*',DB::raw('MAX(ch_messages.created_at) max_created_at')) ->orderBy('max_created_at', 'desc') ->groupBy('users.id') ->paginate($request->per_page ?? $this->perPage);
users table:
ch_messages table:
I'm logged in with user of id 1. Error I get:
SQLSTATE[42000]: Syntax error or access violation: 1055 'chat.users.name' isn't in GROUP BY (SQL: select count(*) as aggregate from (select `users`.*, MAX(ch_messages.created_at) max_created_at from `ch_messages` inner join `users` on `ch_messages`.`from_id` = `users`.`id` or `ch_messages`.`to_id` = `users`.`id` where (`ch_messages`.`from_id` = 1 or `ch_messages`.`to_id` = 1) and `users`.`id` != 1 group by `users`.`id`) as `aggregate_table`)
If I run the query outside Laravel I get the response fine. The query:
select count(*) as aggregate from (select `users`.*, MAX(ch_messages.created_at) max_created_at from `ch_messages` inner join `users` on `ch_messages`.`from_id` = `users`.`id` or `ch_messages`.`to_id` = `users`.`id` where (`ch_messages`.`from_id` = 1 or `ch_messages`.`to_id` = 1) and `users`.`id` != 1 group by `users`.`id`) as `aggregate_table`
The response:
Where's the problem? How should I investigate this furthermore?
Level 1
Thank you but I still can't figure out how to fully rewrite the Eloquent query. I need all columns. I tried this:
$users = Message::join('users', function ($join) { $join->on('ch_messages.from_id', '=', 'users.id') ->orOn('ch_messages.to_id', '=', 'users.id'); }) ->where(function ($q) { $q->where('ch_messages.from_id', Auth::user()->id) ->orWhere('ch_messages.to_id', Auth::user()->id); }) ->where('users.id','!=',Auth::user()->id) ->get() ->groupBy('users.id') ->paginate($request->per_page ?? $this->perPage);
I get that "Method Illuminate\Database\Eloquent\Collection::paginate does not exist".
Level 102
@imawesome You cannot paginate after getting all data. Just make sure that every single column you want to select is either in the group by or is an aggregate (sum, count, min, max)
Level 5
First of all, you must understand what is Eloquent.
Eloquent is an ORM: Object-Relation Mapper. It is an abstraction layer between Laravel and the underlying database, it is created so you don't have to write query. So either you write a query, OR you use Eloquent, you cannot do both at the same time
If you want to use Eloquent, then you must first define the relationship in your Model:
User model:
class User extends Model
{
// blablabla
function sentMessages(){
return $this->hasMany(Message::class, 'from_id');
}
function receivedMessages(){
return $this->hasMany(Message::class, 'to_id');
}
}
Message Model: User model:
class Message extends Model
{
// blablabla
function sender(){
return $this->belongsTo(User::class, 'from_id');
}
function receiver(){
return $this->hasMany(User::class, 'to_id');
}
}
And then in you Controller, you get what you want, examples:
//get the message with id 1, then get its sender and receiver
$message = Message::find(1);
$user1 = $message->sender; //or $message->sender()
$user2 = $message->receiver; //or $message->receiver()
//find all messages that user with id 1 sent
$user = User::find(1);
$messageThatUserSent = $user->sentMessages; //or $user->sentMessages()
There is no Join, no select,... because, well, you are using an ORM
Please or to participate in this conversation.