MySQL: How do I retrieve n (number of) data from an array?

Question

I have a messages table.

+----+---------+----------+
| id | conv_id | body     |
+----+---------+----------+
|  1 |       1 |     haha |
|  2 |       1 |     blabl|
| ...|     ... |    ...   |
|  25|       2 |     hehe |
+----+---------+----------+

... = rest of messages with conv_id of 2's or 1's or 3's or n's.

Let's say I have conv_id = array(2,1) and I want to obtain 10 messages after matched with an array of ids in conv_id so I did

select * from `messages` where `conv_id` in (2, 1) order by `created_at` desc limit 10

The sql above gave me 10 messages after matching both conv_ids and getting all combined messages. However, this is NOT what I wanted. Instead, I wanted 10 messages of EACH conv_id matched.

How do I get 10 messages of EACH conv_id matched? No PHP for loop, please. Thank you!

NOTE : the array conv_id can easily be extended to include many other values unique to each other, not only 2s or 1s.

---

P.s., bonus points for Laravel Eloquent answer! Here are the details :

1. Two models, Conversations and Messages linked by Conversations hasMany Message and Message belongsTo a Conversation.
2. My sql above was translated from Messages::with('User')->whereIn('conv_id',$conv_id)->orderBy('created_at','desc')->take(10);

Answer 1

I think Jared is right but if you can add another column to the table, solution would be more efficient. Add a column which indicates message number for each conv_id (earliest will have 1 and the latest will have number of messages conversation have). After that, you can achieve your goal by scanning table twice with HAVING clause.

SELECT * FROM messages JOIN
  (SELECT conv_id, MAX(msg_no) FROM messages WHERE conv_id IN (2,1) GROUP BY conv_id) as M 
ON messages.conv_id=M.conv_id HAVING messages.msg_no > M.msg_no-10

Answer 2

Another possibility. Get last conv_ids and their 10th (message)id with a group_concat - substring_index trick and re-join of message-table.

SELECT `messages`.*
FROM (
    SELECT 
    conv_id,
    substring_index(
        substring_index(
            group_concat(`messages`.id),
            ',',
            10
        ),
        ',',
        -1
    ) AS lastMessageId
    FROM `messages` 
    WHERE `conv_id` in (2, 1)
    GROUP BY `conv_id`
) AS msub
INNER JOIN `messages` ON `messages`.conv_id = msub.conv_id AND `messages`.id <= msub.lastMessageId

Answer 3

Warning: This approach is potentially wrong. I'm trying to validate it and will update it once I reach a conclusion

Yesterday I just learnt something new about Eloquent relationship from an answer by deczo in Getting just the latest value on a joined table with Eloquent. And I think we can adapt it to your case as well.

What you're essentially trying to do, I put in my view as:

• A Conversation model that has many Messages.
• But I want only 10 latest messages per each conversation. All come eager-loaded.

In Eloquent, I would probably do something along the line of:

Conversation::with('messages')->whereIn('conv_id', [1, 2])->get();

But two things I note of your question.

• I assume you don't have another table called conversations.
• The code above does not limit to how many messages per conversation.

So I decided that here should be two models, one called Message, another called Conversation. Both call to the same table, but we will tell Eloquent to use different columns as primary key. So to get a Conversation model, I added a distinct() to my newQuery function, and select only conv_id out since that's the only information about a conversation.

So in the end I have this:

models/Message.php

class Message extends Eloquent
{
    protected $table = 'messages';
    protected $primaryKey = 'id';
    public $timestamps = false;
}

models/Conversation.php

class Conversation extends Eloquent
{
    protected $table = 'messages';
    protected $primaryKey = 'conv_id';
    public $timestamps = false;

    public function newQuery($excludeDeleted = true)
    {
        return parent::newQuery()
            ->select('conv_id')
            ->distinct();
    }

    public function messages()
    {
        return $this->hasMany('Message', 'conv_id')
            ->orderBy('id', 'desc')
            ->take(10);
    }
}

Now I can do:

Conversation::with('messages')->whereIn('conv_id', [1, 2])->get();

which gives me a list of conversations with conv_id 1 and 2, along with the 10 latest messages for each of them.

This is the first time I do something like this. So I code-tested and it works.

---

Update: The code in my answer perform these two queries only (retrieved from DB::getQueryLog()):

select distinct `conv_id` from `messages` where `conv_id` in (?, ?);
select * from `messages` where `messages`.`conv_id` in (?, ?) order by `id` desc limit 10;