I have following tables

Messages:

• user_id
• group_id
• text
• created_at

And group_users:

• user_id
• left_at
• group_id

These tables have relationship via "group_id".

I need "Messages" that created before "group_user" left group. In other word, i need a query like this:

$messages = Message::where('created_at','<',group_user.left_at)->get(); How can i achieve this?

CodePudding user response：

it can be like the code below(untested):
in the Message model

public function group(){return $this->belongsTo(Group::class);}

in the controller

$messages = Message::whereHas('group', function($q){$q->where('left_at','>','messages.created_at');})->get();

CodePudding user response：

You can simply do it like this assuming you have the user ID at hand:

Message::join('group_users', 'messages.group_id', '=', 'group_users.group_id')
    ->where('group_users.user_id', $id)
    ->where('group_users.left_at', '>', 'messages.created_at')
    ->get();