[SOLVED] Limiting number of results from a user

[JSHINER]

I have a query that looks like this:

 

SELECT item.user, item.id, item.text, item.posted FROM item WHERE item.posted = 1 ORDER BY item.id DESC LIMIT 10

 

And this works fine returning the 10 most recent results. However, I want to limit it to 3 per user.

 

Right now if say a user has made the last 10 posts, you see only their results in the recent posts. I want a MAX of 3 of each users to show up.

 

How can I do this?

 

(I am posting this in PHP because I don't think it can be done via MySQL, but I may be wrong)

 

 

[.josh]

Okay maybe I'm reading that wrong, but are you saying that if random JoeUser clicks on some "most recent posts" button, and it shows the 10 most recent posts, but if all 10 of them were from say JaneDoe you only want to show 3 of them, skipping the other 7, and displaying the next 7 after that?

 

[JSHINER]

Correct.

 

Let's say the results are as follows for the 13 most recent:

 

JoeSmith

JoeSmith

JoeSmith

JoeSmith

JoeSmith

JoeSmith

JoeSmith

JoeSmith

JoeSmith

JoeSmith

MikeBoss

JenMan

KyleEat

etc...

 

I would like it do display:

 

JoeSmith

JoeSmith

JoeSmith

MikeBoss

JenMan

KyleEat

etc...

[.josh]

But...why would you want to do that? That kind of defeats the purpose of it being the "most recent" posts.

 

But whatever... I think I would first do the query to pull the first 10, then make a loop to check how many times JoeSmith is in the returned array.  If more than 3, I would slice out the extra ones, reset the keys of the array to fill up the blank spaces, go back and query the database where name != JoeSmith desc limit x (where x is how many you need to get to 10).

 

I suppose you would then have to take that as a building block and expand on it, because JoeSmith and MikeBoss could have both posted 5 times each...

 

[discomatt]

Yes, I don't think this is possible purely in MySQL (I'd love to be corrected).

 

It will become very query-intensive with the method posted above.

 

IMO the best approach to this would be to use SELECT DISTINCT and limit it to a single result per user.

[metrostars]

It could be difficult to this, you will have to echo a large amount of rows to make sure that you diffinetly get the distinct rows that you want.

 

The PHP behind it is easy tho.

[JSHINER]

It does defeat the purpose of most recent, but also does not allow users to game the system by posting 10 in a row to make sure they're all over the most recent.

 

Would it be easiest to just pull say 30 results, and inside a foreach do some work to cut it down to 10 unique? Based on each user not having more than 3?

[JSHINER]

How can I use SELECT DISTINCT to only allow one user id ?

[discomatt]

SELECT DISTINCT `user` FROM `item` WHERE `posted` = 1 ORDER BY `id` DESC LIMIT 10

 

Try that

[JSHINER]

Thing is I have other items in the SELECT such as item.user, item.id, etc.

[.josh]

It does defeat the purpose of most recent, but also does not allow users to game the system by posting 10 in a row to make sure they're all over the most recent.

 

Would it be easiest to just pull say 30 results, and inside a foreach do some work to cut it down to 10 unique? Based on each user not having more than 3?

no that wouldn't work any better than the top 10. JoeUser could have posted that last 30 times...

[discomatt]

Thing is I have other items in the SELECT such as item.user, item.id, etc.

 

Should still work fine.. just add them to the query.

 

SELECT DISTINCT `user`, `col1`, `col2` FROM `item` WHERE `posted` = 1 ORDER BY `id` DESC LIMIT 10

[.josh]

okay but that will only limit the users returned to 1 post each how would you up that to a max of 3 each? like, some kind of distinct (max(3)) or distinct limit(3) blahblah or something.

[.josh]

well regardless, my gut tells me that you can do this with sql so I'm gonna move it over there where our resident sql gurus hang out I bet they bust out something for you.

[sasa]

try

SELECT a.user, a.id, a.text, a.posted FROM item a 
WHERE a.posted = 1 AND
(SELECT COUNT(b.id) FROM item b WHERE b.id>a.id AND a.user=b.user AND b.posted=1)<3
ORDER BY a.id DESC LIMIT 10

[DarkerAngel]

This is what I'm looking at now, and Yes I think there has to be a way to do it within SQL

 

http://www.artfulsoftware.com/infotree/queries.php#104

[DarkerAngel]

this is what I came up with (assuming your Table Structure)

 

SELECT t1.user, t1.id, t1.posted, t1.text count(*) AS earlier
FROM item AS t1
JOIN item AS t2 ON t1.user=t2.user AND t1.posted >= t2.posted
GROUP BY t1.user, t1.posted
HAVING earlier <= 3
LIMIT 10;

[JSHINER]

Here's the actual one:

 

	SELECT
	blog_posts.id AS id,
	blog_posts.title AS title,
	blog_posts.user_id AS user_id,
	blog_posts.date AS date,
	blog_posts.content AS content,
	blog_posts.views AS views,
	user.first_name AS first_name,
	user.last_name AS last_name
FROM blog_posts, user WHERE blog_posts.user_id = user.id AND blog_posts.published = 1 AND blog_posts.user_id != 50 ORDER BY blog_posts.id DESC LIMIT 10'

[DarkerAngel]

SELECT t1.id, t1.title, t1.user_id, t1.date, t1.content, t1.views, user.first_name, user.last_name, COUNT(*) AS limiter
FROM blog_posts AS t1, user
JOIN blog_posts AS t2 ON t1.user_id=t2.user_id AND t1.id <= t2.id
WHERE t1.published = 1 AND user.user_id = t1.user_id AND t1.user_id != 50
GROUP BY t1.id
HAVING limiter <=3
ORDER BY t1.id
LIMIT 10

 

That was fun have any thing else for me :3

[fenway]

I suppose you could use user variables to figure out when you're reached three, and filter accordingly.