Laravel Eloquent groupBy orderBy gives different results in MariaDB

I have read about this already in SO and MariaDB knowledgeable about this incompatibility between Mysql and Mariadb. But I am not sure how to resolve this issue in Laravel Eloquent / DB queries.

My Problem: The groupBy orderBy query gives different results in MariaDB and MySql. It works fine in mySql by the results are in different order in MariaDB.

This is my query:

$messages = ChatMessages::select(DB::raw('t.*'))
          ->from(DB::raw('(SELECT * FROM chat_messages ORDER BY created_at DESC) t'))
          ->whereIn('message_id', $messageIds)
          ->groupBy('message_id')
          ->orderBy('created_at', 'DESC')
          ->paginate(3);

For example, lets say this is the chat_messages table:

+----+----------+---------------------+-----------+
| id | message_id | created_at        | name      |
+----+----------+---------------------+-----------+
| 1  | 1000     | 2017-01-01 06:03:40 | Anna      |
+----+----------+---------------------+-----------+
| 2  | 1007     | 2017-01-02 07:13:20 | Becky     |
+----+----------+---------------------+-----------+
| 3  | 1000     | 2017-01-03 08:20:12 | Christina |
+----+----------+---------------------+-----------+
| 4  | 1004     | 2017-01-03 08:20:15 | Dorothy   |
+----+----------+---------------------+-----------+
| 5  | 1004     | 2017-01-04 09:25:45 | Emma      |
+----+----------+---------------------+-----------+
| 6  | 1000     | 2017-01-05 10:30:10 | Fiona     |
+----+----------+---------------------+-----------+
| 7  | 1007     | 2017-01-05 10:33:23 | Gigi      |
+----+----------+---------------------+-----------+
| 8  | 1007     | 2017-01-06 12:46:34 | Heidi     |
+----+----------+---------------------+-----------+
| 9  | 1000     | 2017-01-06 12:46:34 | Irene     |
+----+----------+---------------------+-----------+
| 10 | 1007     | 2017-01-07 14:58:37 | Jane      |
+----+----------+---------------------+-----------+
| 11 | 1007     | 2017-01-07 14:58:37 | Katy      |
+----+----------+---------------------+-----------+

The query works fine in MySql database and the results are returned as this:

+----+----------+---------------------+-----------+
| id | message_id | created_at        | name      |
+----+----------+---------------------+-----------+
| 11 | 1007     | 2017-01-07 14:58:37 | Katy      |
+----+----------+---------------------+-----------+
| 9  | 1000     | 2017-01-06 12:46:34 | Irene     |
+----+----------+---------------------+-----------+
| 5  | 1004     | 2017-01-04 09:25:45 | Emma      |
+----+----------+---------------------+-----------+

However, in MariaDB database, the results are returned incorrectly like this. It seems to group the message_id in ascending order first and then adding the orderBy to that:

+----+----------+---------------------+-----------+
| id | message_id | created_at        | name      |
+----+----------+---------------------+-----------+
| 4  | 1004     | 2017-01-03 08:20:15 | Dorothy   |
+----+----------+---------------------+-----------+
| 2  | 1007     | 2017-01-02 07:13:20 | Becky     |
+----+----------+---------------------+-----------+
| 1  | 1000     | 2017-01-01 06:03:40 | Anna      |
+----+----------+---------------------+-----------+

I tried changing the query thought of using unique() instead like this:

AppConversationMessages::whereIn('message_id', $messageIds)
                       ->orderBy('created_at', 'DESC')
                       ->paginate(3)
                       ->unique('message_id');

Although it works in MariaDB and MySql the same way, but the pagination is applied before the unique check and therefore returned lesser results:

+----+----------+---------------------+-----------+
| id | message_id | created_at        | name      |
+----+----------+---------------------+-----------+
| 11 | 1007     | 2017-01-07 14:58:37 | Katy      |
+----+----------+---------------------+-----------+
| 9  | 1000     | 2017-01-06 12:46:34 | Irene     |
+----+----------+---------------------+-----------+

How can I resolve this?

via Chebli Mohamed