Need Help Setting up Messaging Database correctly

Hi, I am struggling to setup my messaging database. I want users to be able to message each other and then see those conversations. I originally setup my database like

$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');

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, but now I am struggling to correctly display those messages. I can easily get all the sent messages and received messages for a user but when I try to create a method to group them into only the messages between two users it is getting more complicated. Id like to return a collection from laravel that has

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
]

Am I going about this the correct way or should I be looking at pivot tables?

pom

6 years ago

Level 21

You could filter your collection of messages.

$messages = $message->filter(function ($message) use ($user) {
    if ($message->sender_id === $user->id || $message->sent_to_id === $user->id {
        return $message;
    }
});

chrisgrim

6 years ago

Level 10

@pom I believe this gets me all the messages that I have sent and received. How do I group it into groups like these are all the messages that user with an id of 4 and me have sent and received?

Loach

6 years ago

Level 11

Try using the whereIn()method on your messages collection. You would place your user_id and id of 4. Something like the following should work. This is how I have me messaging system setup.

$messages = Message::whereIn('sender_id', [$otheruser->id, Auth::user()->id])
            ->whereIn('sent_to_id', [$otherruser->id, Auth::user()->id]);

chrisgrim

6 years ago

Best Answer

Level 10

I figured out how to do this by creating a conversations model and then a conversation_user pivot table

Schema::create('conversations', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->timestamps(); 
        });
        Schema::create('conversation_user', function(Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedBigInteger('conversation_id');
            $table->unsignedBigInteger('user_id');
            $table->timestamps();
            $table->foreign('conversation_id')->references('id')->on('conversations');
            $table->foreign('user_id')->references('id')->on('users');
        });

this way I can have a belongsToMany relationship between the users and the conversations. Then I have a messages table

$table->bigIncrements('id');
            $table->unsignedBigInteger('user_id');
            $table->unsignedBigInteger('conversation_id');
            $table->string('message');
            $table->timestamps();
            $table->foreign('conversation_id')
                    ->references('id')->on('conversations')
                    ->onDelete('cascade');
        });

Which has a belongsTo relationship with the conversation table.

Please or to participate in this conversation.