I've been stuck in a complex MySQL query. Here is my table:
+---------------------------------------------------+
| id | user_id | category_id | post_id | view_count |
+---------------------------------------------------+
| 1 | 23 | 5 | 213 | 0 |
| 2 | 23 | 5 | 214 | 0 |
| 3 | 23 | 5 | 215 | 0 |
| 4 | 23 | 5 | 216 | 0 |
| 5 | 23 | 6 | 217 | 0 |
| 6 | 23 | 6 | 218 | 0 |
| 7 | 23 | 6 | 219 | 0 |
| 8 | 23 | 6 | 220 | 0 |
| 9 | 55 | 13 | 221 | 0 |
| 10 | 55 | 13 | 222 | 0 |
| 11 | 55 | 16 | 223 | 0 |
| 12 | 55 | 16 | 234 | 0 |
| 13 | 55 | 22 | 235 | 0 |
| 14 | 55 | 22 | 256 | 0 |
| 15 | 55 | 22 | 261 | 0 |
| 16 | 62 | 13 | 272 | 0 |
| 17 | 62 | 13 | 273 | 0 |
| 18 | 62 | 24 | 277 | 0 |
| 19 | 62 | 24 | 278 | 0 |
| 20 | 62 | 24 | 288 | 0 |
| 21 | 62 | 31 | 289 | 0 |
| 22 | 62 | 31 | 290 | 0 |
+---------------------------------------------------+
Now what I wish is for each user_id I want 3 rows of data but each row should have a different category_id, like the below resultset:
+--------------------------------------+
| id | user_id | category_id | post_id |
+--------------------------------------+
| 1 | 23 | 5 | 213 |
| 5 | 23 | 6 | 217 |
| 9 | 55 | 13 | 221 |
| 11 | 55 | 16 | 223 |
| 16 | 62 | 13 | 272 |
| 18 | 62 | 24 | 277 |
+--------------------------------------+
The query I've used is as follows:
SELECT id,user_id,category_id,post_id,view_count,rank
FROM (
SELECT tt.* ,@rank:= CASE WHEN @group = user_id THEN @rank + 1 ELSE 1 END rank,@group:= tt.user_id
FROM (
SELECT id,user_id,category_id,post_id, view_count
FROM product_table
WHERE view_count=(SELECT MIN(view_count) FROM product_table
)
GROUP BY user_id,category_id
ORDER BY user_id,category_id
) tt
JOIN (SELECT @rank:=0,@group:=0) t1) new_t
WHERE rank <=3 LIMIT 9
This query was suggested to me by @m-khalid-junaid in my previous question: SELECT 3 records per user group by on two columns And I tweaked that query to incorporate the MIN(view_count) value.
What I do now is after the resultset is retrieved I increment the view_count by 1
But this query runs as follows:
if "User 1" has 6 posts in 3 categories, having each category contain 2 posts
And
if "User 2" has 9 posts in 3 categories, having each category containing 3 posts
And
if "User 3" has 7 posts in 3 categories, having 2 categories containing 2 posts and the last category which has 3 posts
Then the SQL when running repeatedly uses following cycle:
Cycle 1:
Show first 3 posts of "User 1" from each of the category [1-3]
Show first 3 posts of "User 2" from each of the category [1-3]
Show first 3 posts of "User 3" from each of the category [1-3]
Cycle 2:
Show next 3 posts of "User 1" from each of the category [4-6]
Show next 3 posts of "User 2" from each of the category [4-6]
Show next 3 posts of "User 3" from each of the category [4-6]
Cycle 3:
Show next 3 posts of "User 2" from each of the category [7-9]
Show remaining 1 post of "User 3" from last category [7]
What I wish is if no more "User 1" posts are available in "Cycle 3", then fetch the posts used in Cycle 1 for "User 1" And in "Cycle 3" there is only 1 post of "User 3" so in that case I wish that Cycle 3 uses last 1 post and fetches Cycle 1's 2 posts for "User 3". Note that these posts will now be having view_count incremented to 1 in last cycle.
I hope I explained it correctly.
