How to set up an internal laravel website Messaging Database correctly

Question

I am struggling to setup my messaging database. I am allowing users to message each other through my site. When a user goes to their message panel I want the database to return data like this

1[
Message with sender_id = 1 and sent_to_id = 2 body = text here
Message with sender_id = 2 and sent_to_id = 1 body = text here
Message with sender_id = 1 and sent_to_id = 2 body = text here
]
2[
Message with sender_id = 1 and sent_to_id = 4 body = text here
Message with sender_id = 4 and sent_to_id = 1 body = text here
]
3[
Message with sender_id = 16 and sent_to_id = 1 body = text here
Message with sender_id = 1 and sent_to_id = 16 body = text here
]

I started creating my database like this

$table->bigIncrements('id');
            $table->unsignedBigInteger('sender_id');
            $table->unsignedBigInteger('sent_to_id');
            $table->text('body');
            $table->timestamps(); 
            $table->foreign('sender_id')
                    ->references('id')->on('users')
                    ->onDelete('cascade');
            $table->foreign('sent_to_id')
                    ->references('id')->on('users')
                    ->onDelete('cascade');

Then in my user model I have

public function received()
    {
        return $this->hasMany(Message::class, 'sent_to_id');
    }
 public function sent()
    {
        return $this->hasMany(Message::class, 'sender_id');
    }

which works great but it I can't see a way to group the messages into conversations between users like my first example.

Did I setup my database correctly for what I am trying to achieve?
How would I set it up in my model to return messages grouped by conversation between users?

score 2 · Accepted Answer · answered Jan 25 '20 at 01:08

You could create a conversations table with sender_id and recipient_id and a separate messages table to store the messages that belong to that conversation.

/** Conversations **/
class Conversation extends Model
{

    protected $fillable = ['sender_id', 'sent_to_id'];

    public function messages()
    {
        return $this->hasMany(Message::class);
    }
    ...

/** Messages **/
class Message extends Conversation
{
    protected $fillable = [
        'user_id',// User that created this message
        'message', // Body of the message
        'conversation_id'
    ];

    public function conversation()
    {
        return $this->belongsTo(Conversation::class);
    }
    ...

With the above setup you can get conversations with messages:

/** Currently authenticated user **/
$userId = auth()->id();
$conversations = App\Conversation::where('sender_id', $userId)
    ->orWhere('sent_to_id', $userId)
    ->with('messages')->get();

score 0 · Answer 2 · answered Jan 25 '20 at 02:07

you can make messages table which contains:

sender_id int
receiver_id int
body varchar(255)
created_at datetime
seen_at datetime nullable

    /** Messages **/
    class Message extends Model
    {
        protected $fillable = [
            'sender_id',
            'receiver_id',
            'body',
            'seen_at',
        ];
    }

and retrieve it with :

// don't  forget to update seen attribute while fetching
App\Message::where('receiver_id',$userId)->update(['seen_at' => date('Y-m-d H:i:s') ]);
//
$userId = auth::user()->id(); // authenticated_user
$messages = App\Message::where('sender_id', $userId)->orWhere('receiver_id',$userId)->get();

and easily you can map on it to style it (on view)

@foreach($messages as $message)
  @if ($message->sender_id == Auth::user()->id)
    <p class='message_sent'> {{$message->body}} </p>
  @else
    <p class='message_received'> {{$message->body}} </p>
  @endif
 @if ($message->seen_at)
  <p> {{$message->seen_at}} </p>
 @endif
@endforeach

How to set up an internal laravel website Messaging Database correctly

2 Answers2