CODEWITHSUNDEEP
mysqlsqldatabase
Symphony0084
Symphony0084

Reputation: 1435

MySQL - How to limit query results based on foreign key

I have a users table, a posts table, and a search feature that queries MySQL for posts.

I want it to return all posts for 100 specific users (with a maximum of 6 posts per user).

I have a column called user_id on the posts table which is a foreign key.

Right now I can return all posts for those users like this:

SELECT id FROM posts WHERE user_id IN (1,2,3,4 .... 98,99,100);

However many of these users have hundreds of posts. I want to cap it at 6 per user. How can I add such a constraint to my query?

I am using MySQL version 8.0.15

Upvotes: 1

Views: 287

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Lukasz's answer is fine. But, you don't need to use row_number():

SELECT p.*
FROM posts p
WHERE p.user_id IN (1,2,3,4 .... 98,99,100) AND
      p.col_time >= ALL (SELECT p2.col_time
                         FROM posts p2
                         WHERE p2.user_id = p.user_id
                         ORDER BY p2.col_time DESC
                         LIMIT 1 OFFSET 5
                        );

(Note: The ALL handles the case when there are fewer than 6 posts for the user.)

With an index on posts(user_id, col_time), it would be interesting to know if this is faster or slower than ROW_NUMBER().

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

ROW_NUMBER(MySQL 8.0+) could be used:

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY col_time DESC) AS rn
                                                   -- taking the newest posts
  FROM posts 
  WHERE user_id IN (1,2,3,4 .... 98,99,100)
) sub
WHERE rn <= 6;

Upvotes: 4

Related Questions