1

Given the following models

Conversation: id, user_id,

Message: id, conversation_id, author_id, created_at

I wanted to query all conversations who have messages between $start and $end, so i did this:

$filterMessages = function($query) use ($start, $end) {
    $query->whereBetween("created_at", [$start, $end]);
};

$convs = Conversation::whereHas("messages", $filterMessages)
->with(["messages"  => $filterMessages]);

And it works. Now i need to add a condition: the message.user_id must match his conversation's user_id (both in whereHas and with). So i changed my callback to this:

$filterMessages = function($query) use ($start, $end) {
    $query->whereBetween("created_at", [$start, $end]);
    $query->where("conversations.user_id", "author_id");
};

But it does not work. How could i do that?

Thanks

jarlh
  • 42,561
  • 8
  • 45
  • 63
pelomedusa
  • 47
  • 1
  • 11

1 Answers1

1

Try this

Conversation Model define relationship

public function userMessages()
{
   return $this->hasMany(Message::class, 'author_id', 'user_id'); //your relationship based on data structure
}


$convs = Conversation::whereHas('userMessages',function($query) use ($start, $end) {
                           $query->whereBetween("created_at", [$start, $end]);
                       })
                      ->with("userMessages")
                      ->get();
bhavinjr
  • 1,663
  • 13
  • 19
  • I think you misunderstood my question, as i said i need "the message.user_id must match his conversation's user_id". The date query already works. Thanks – pelomedusa Mar 21 '19 at 09:54
  • Then you can define a relationship with `Conversation` check my latest post – bhavinjr Mar 21 '19 at 10:00
  • it's a good idea, however the userMessages relationship ignore the conversation_id field, so it also returns other conversations messages :( – pelomedusa Mar 21 '19 at 10:22
  • `message.user_id==conversation.user_id` or `message.author_id==conversation.user_id` can you specified? so i can update my post – bhavinjr Mar 21 '19 at 10:38
  • it's `author_id`! – pelomedusa Mar 21 '19 at 10:41
  • Ok i managed to do it using the `topclaudy/compoships` package: `return $this->hasMany('App\Models\Chat\Message', ['author_id', 'conversation_id'], ['user_id', 'id']);` – pelomedusa Mar 21 '19 at 10:57
  • after update relationship. above example is working or not? – bhavinjr Mar 21 '19 at 11:37
  • No it did not, because it's ignoring the first relationship rule i need (conversation.id -> messages.conversation_id). Thank you for your time tho – pelomedusa Mar 21 '19 at 12:49