I operate a website where customers can publish products (e.g. pictures). The products are saved inside a MySQL database.
I wish to query going back ten published products However with the constraint of no more than 3 products may come from the single user.
What's the easiest way to do it? My preferred option would be a constraint that's placed on the SQL query asking for the final ten products. But ideas regarding how to setup the database design is extremely welcome.
Thanks ahead of time!
It's pretty easy having a correlated sub-query:
SELECT `img`.`id` , `img`.`userid` FROM `img` WHERE 3 > ( SELECT count( * ) FROM `img` AS `img1` WHERE `img`.`userid` = `img1`.`userid` AND `img`.`id` > `img1`.`id` ) ORDER BY `img`.`id` DESC LIMIT 10
The query assumes that bigger
id means added later
Correlated sub-queries really are a effective tool! :-)
This really is difficult because MySQL doesn't offer the LIMIT clause on sub-queries. Whether it did, this is rather trivial... But alas, here's my naïve approach:
SELECT i.UserId, i.ImageId FROM UserSuppliedImages i WHERE /* second valid ImageId */ ImageId = ( SELECT MAX(ImageId) FROM UserSuppliedImages WHERE UserId = i.UserId ) OR /* second valid ImageId */ ImageId = ( SELECT MAX(ImageId) FROM UserSuppliedImages WHERE UserId = i.UserId AND ImageId < ( SELECT MAX(ImageId) FROM UserSuppliedImages WHERE UserId = i.UserId ) ) /* you get the picture... the more "per user" images you want, the more complex this will get */ LIMIT 10;
You didn't discuss getting a frequent result order, which means this chooses the most recent images (presuming
ImageId is definitely an climbing auto-incrementing value).
To compare, on SQL Server exactly the same would seem like this:
SELECT TOP 10 img.ImageId, img.ImagePath, img.UserId FROM UserSuppliedImages img WHERE ImageId IN ( SELECT TOP 3 ImageId FROM UserSuppliedImages WHERE UserId = img.UserId )
I'd first choose 10 distinct customers, then choosing images from all of individuals customers having a LIMIT 3, possibly with a union of individuals and limit that to 10.
That will atleast limit the information you have to process to some fair amount.