Is it possible to grouping by from and to user id in Laravel 8

user_6309457 0
Viewed 172

I am having One-to-One chat simple table, where message store as from user id and to user id. here is schema.

enter image description here

I am retrieving conversation between two user by matching either from_user_id match with login user or to_user_id as

->where(function($q) use($user_id) {
       $q->where('from_user_id', $user_id)
       ->orWhere('to_user_id', $user_id);
})

Now i want a unique conversation between login user and other users. i have tried as

->groupBy('from_user_id', 'to_user_id')

but it return both record id : 1 and 6 because it stored as 65 - 66 and 66 - 65.

So any way that i can group by with unique conversasion and get only one record for the same. or i need to change schema of table as conversation and conversation_message.

Please guide me. Thanks.

laravel-8 laravel database-design mysql php
2 Answers

I would recommend adding a Conversation model. You basically want a unique identifier for each user-to-user relationship (and all messages therein), and a Conversation model will be the most succinct way to accomplish that.

That being said, there are clever ways of doing what you want to do without an extra model. Since you know the logged in user's ID, you can pass a callback to the Collection's groupBy method that groups by the ID that isn't the logged-in user. For example:

->where(function($query) {
    $query->where('from_user_id', $user_id)
    ->orWhere('to_user_id', $user_id)
})
// So the messages appear in order.
->orderByDesc('created_at')
->get()
->groupBy(function($item) use ($user_id) {
    // If the message was sent by the user, it groups on the recipient's ID.
    // If the message was received by the user, it groups on the sender's ID.
    return $item->from_user_id == $user_id ? $item->to_user_id : $item->from_user_id;
});

This example doesn't account for a user sending messages to themselves (which may be possible in your system), so it may need fine-tuned.

user_143815550

Recently, I coded a chat system for my company. I tried many schemas but finally found the best one. I can share it with you.

enter image description here

This schema may consist of several tables and will force you to send more requests. But in this structure you will be able to handle all tasks easily. Here you are model and controller structure:

ChatMessage:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class ChatMessage extends Model
{
    use HasFactory;
    
    public $table = "chat_messages";

    protected $fillable = ['chat_id', 'user_id','content'];
    
    public function chat()
    {
        return $this->belongsTo(ChatUser::class, 'chat_id', 'id');
    }
    
    public function user()
    {
        return $this->belongsTo(User::class);
    }
}

ChatUser:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class ChatUser extends Model
{
    use HasFactory;

    public $table = "chat_users";

    protected $fillable = ['user_id_from', 'user_id_to','last_activity'];
    protected $dates = ['last_activity']; 

    public function user_from()
    {
        return $this->belongsTo(User::class, 'user_id_from', 'id');
    }
    
    public function user_to()
    {
        return $this->belongsTo(User::class, 'user_id_to', 'id');
    }
    
    public function messages()
    {
        return $this->hasMany(ChatMessage::class, 'chat_id', 'id')->latest('id')->take(15);
    }
}

ChatController:

    public function index()
    {
        $chats = ChatUser::has('messages')
            ->where('user_id_from', $this->user->id)
            ->orWhere(function ($query)  {
                $query->where('user_id_to' , $this->user->id);
            })
            ->with(['user_to','user_from'])
            ->orderBy('last_activity', 'DESC')
            ->get();

        return $this->successResponse(new ChatCollection($chats));
    }

If you have any questions don't hesitate to ask.

user_121691990
Related