MySQL：如何从数组中检索n（数量）数据？

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