How to sort merged collections in Laravel 5?

Question

I am working on a chat application where I have used Pusher for a realtime chat, meanwhile storing every message in my DB too.

Messages table schema looks like so:

public function up()
{
    Schema::create('messages', function(Blueprint $table)
    {
        $table->increments('id');
        $table->integer('sender_id')->unsigned();
        $table->foreign('sender_id')->references('id')->on('users')->onDelete('cascade');
        $table->integer('receiver_id')->unsigned();
        $table->foreign('receiver_id')->references('id')->on('users')->onDelete('cascade');
        $table->text('body');
        $table->timestamps();
    });
}

It is very simple. Two foreign keys that refer to the sender and receiver.

The chat is working fine and the users can chat with each other. But now I also need that users can see older messages.

I have a MessageController in which the index method looks like so:

public function index()
{
    $data = $this->request->all();

    $receivedMessages = $this->user->currentUser()->receivedMessages()
                ->where('sender_id', $data['chatUserId'])
                ->get();

    $sentMessages = $this->user->currentUser()->sentMessages()
                ->where('receiver_id', $data['chatUserId'])
                ->get();

    $messages = $receivedMessages->merge($sentMessages)->sortBy('created_at');

    return $messages;
}

Basically I am fetching the all the messages where the current user is either a sender or a receiver and vice versa for the the other user, the id of which I supply from the front end in chatUserId.

I have used merge method to merge these collection and I am trying to run the sortBy or sortByDesc methods, but they don't seem to be working.

I have a simple dummy chat with a couple of users as follows:

The web service at MessageController@index returns this:

{
   "0":{
      "id":"10",
      "sender_id":"3",
      "receiver_id":"1",
      "body":"Hi Rohan. How are you?",
      "created_at":"2015-06-19 08:32:49",
      "updated_at":"2015-06-19 08:32:49"
   },
   "1":{
      "id":"12",
      "sender_id":"3",
      "receiver_id":"1",
      "body":"I have been good too.",
      "created_at":"2015-06-19 08:33:06",
      "updated_at":"2015-06-19 08:33:06"
   },
   "2":{
      "id":"9",
      "sender_id":"1",
      "receiver_id":"3",
      "body":"Hi Ava",
      "created_at":"2015-06-19 08:32:41",
      "updated_at":"2015-06-19 08:32:41"
   },
   "3":{
      "id":"11",
      "sender_id":"1",
      "receiver_id":"3",
      "body":"I am good Ava. How have you been?",
      "created_at":"2015-06-19 08:32:59",
      "updated_at":"2015-06-19 08:32:59"
   }
}

This clearly is not sorted as the way I want it to be. How to sort merged collections in Laravel by the created_at field?

Answer 1

If you don't need the sent and received messages in two differents collections, you can do something like:

$messages = Messages::where(function ($query) {
                          $query->where('sender_id', USER_ID)
                          ->where('receiver_id', $data['chatUserId']);
                      })
                      ->orWhere(function ($query) {
                          $query->where('sender_id', $data['chatUserId'])
                          ->where('receiver_id', USER_ID);
                      })
                      ->sortBy('created_at)
                      ->get();