Problem
I have messages in the system sent between multiple people as a group chat. Every time someones goes to load messages (opens their inbox), I need to get those messages flagged as READ. I don’t have an Eloquent model for the direct_message_read_at pivot table, and I am using a class that encapsulates DB Laravel class to write custom MYSQL query to do this.
My problem is, how do I prevent duplicate entries if someone opens the message thread 10 times, and have the UPDATED_AT timestamp change every time they read the message? (Since they will be opening the same message thread multiple times)
Solution
To help with the setup of this solution, let’s first show how we create this table using Laravel migration:
Before the pivot, we would create a messages table to store all the messages from people. After that we create the pivot table.
$table->increments('id');
$table->integer('message_id')->unsigned()->nullable();
$table->foreign('message_id')->references('id')->on('direct_messages')->onDelete('cascade');
$table->integer('user_id')->unsigned()->nullable();
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
$table->integer('organization_id')->unsigned()->nullable();
$table->foreign('organization_id')->references('id')->on('organizations')->onDelete('cascade');
$table->timestamps();
$table->unique(['message_id', 'user_id', 'organization_id']); // This is really important to
prevent duplicate entries by the same person
});
Now, we want to create an Event and a Listener that will be processing the loaded messages.
Imagine you have a class that is responsible for loading all of your messages (when you open your inbox)
{
$thread_messages = DirectMessage::all();
$message_ids = $this->removeMyMessages($thread_messages)
event(new MessagesRead($messages_ids));
}
protected function removeMyMessages($messages)
{
$message_ids = [];
// Simply filter out all the messages that are sent by you using 'where('sender_id',
auth()->user()->id) - use your own code logic to do that
return $message_ids;
}
Now inside the MessagesRead you can define these and pass them to the listener
{
use Dispatchable, InteractsWithSockets, SerializesModels;
public $messages_ids = [], $user_id, $organization_id;
/**
* Create a new event instance.
*
* @return void
*/
public function __construct($message_ids = [])
{
$this->messages_ids = $message_ids;
$this->user_id = auth()->user()->id;
$this->organization_id = auth()->user()->organization_id;
}
/**
* Get the channels the event should broadcast on.
*
* @return \Illuminate\Broadcasting\Channel|array
*/
public function broadcastOn()
{
return new PrivateChannel('channel-name');
}
}
Inside the Listener that you previously defined in EventServiceProvider you can call your class to process the updating of the pivot table
{
/**
* Create the event listener.
*
* @return void
*/
public function __construct()
{
//
}
/**
* Handle the event.
*
* @param MessagesRead $event
* @return void
*/
public function handle(MessagesRead $event)
{
$message_ids = $event->messages_ids;
$user_id = $event->user_id;
$organization_id = $event->organization_id;
(new CreateDirectMessageReadIndicator(new DB))->execute($message_ids, $user_id,
$organization_id);
}
}
And finally, we are coming closer to the end. All we need to do now is to actually look at the MySQL query
{
protected $db;
function __construct(DB $db)
{
$this->db = $db;
}
/**
* Build and return the select clause for the query
*
* @return string
*/
public function execute($message_ids = [], $user_id, $organization_id)
{
if (count($message_ids) <= 0) {
return false;
}
$created_at = date('Y-m-d H:i:s');
$updated_at = date('Y-m-d H:i:s');
$parameters = [];
foreach ($message_ids as $message_id) {
array_push($parameters, "($message_id, $user_id, $organization_id,
'$created_at')");
}
$parameters_string = implode(",", $parameters);
$query = "
INSERT INTO direct_message_read_at (message_id, user_id, organization_id,
created_at)
VALUES
$parameters_string
ON DUPLICATE KEY UPDATE updated_at='$updated_at';
";
$this->db::select($query);
}
}
So what just happened here. Basically we marked message_id, user_id and organization_id as the unique combination. In case the same user_id that belongs to the same organization organization_id opens the same message from someone that has that message_id it will throw MySQL duplication error.
When you insert a new row into a table if the row causes a duplicate in UNIQUE index or PRIMARY KEY , MySQL will issue an error.
However, if you specify the ON DUPLICATE KEY UPDATE option in the INSERT statement, MySQL will update the existing row with the new values instead.
https://www.mysqltutorial.org/mysql-insert-or-update-on-duplicate-key-update/
Let me share a few screenshots.
The first message being read:
VALUES
(75, 3, 1, '2020-01-16 15:00:00')
ON DUPLICATE KEY UPDATE updated_at='2020-01-17 22:00:00'
It will produce this entry in the database:
Then you come back and read the same message tomorrow, it will cause only the updated_at column to be updated:
This way you know when the message was seen for the first time, and when was the last time when the message was read.
