CODEWITHSUNDEEP
phpmysqldatabaseperformance
Saleh
Saleh

Reputation: 2719

Get latest N posts for each user

I have 2 tables in my db, User and Post. the first table is storing users personal data and the second table is storing posts by users.

I want to build 1 query to get the latest N posts for each user, I was able to get them with multiple queries by looping through each user and get the latest posts and store them in array for later but the performance was too bad and I can't relay on it because too many users and posts (more than 20000 users).

So I need some dynamic query to get these posts, of course I will add some pagination for it.

Thanks in advance.

Upvotes: 1

Views: 153

Answers (3)

gbjbaanb
gbjbaanb

Reputation: 52679

You need to read up on JOIN to write a single query against multiple tables.

for example: select post from posts join users on posts.poster_id = users.user_id

For good performance, make sure your indexes are set correctly, typically on poster_id but it also depends on what columns you use in your where clause.

You can add the where clause to the end of that to only return posts from a certain time or user. Pagination is more tricky and depends on the DB you're using.

Pagination I use (in SqlServer 2008r2, using Sql2012 can be done differently):

SELECT <columns>
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Time DESC) as RowNum, * from <query>) as RowConstrainedResult 
WHERE RowNum >= X AND RowNum < Y 
ORDER BY RowNum

where you replace columns, query, and X and Y with relevant row numbers (eg 101 and 200)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

This is a bit of a pain in MySQL. The following will get you the last ten posts for each user, with the post ids in a comma separated list:

select p.userid,
       substring_index(group_concat(p.postid order by postdate desc), ',', 10) as lastten
from posts p
group by p.userid;

Upvotes: 0

Daan
Daan

Reputation: 12246

SELECT u.user, p.posts FROM user u
LEFT JOIN Post p
ON u.id = p.user_id
WHERE u.id = ?

Upvotes: 0

Related Questions