从每个类别的帖子中限制10条记录[重复]

Question

This question already has an answer here:

• Using LIMIT within GROUP BY to get N results per group? 13 answers

I have two table categories and posts, I don't want to get all records for each category. I want to get limited rows from each category.

categories table as below :-

1. ID
2. Name
3. Slug

posts table as below :-

1. ID [ PK ]
2. title
3. slug
4. content
5. category [Key - Foreign key]
6. publish_date

What i am trying to achieve is , I want to get 10 records from posts for each category.

What i am doing at the moment is so dangerous, that it runs lots of query, i want to minimize it to 1 query.

<?php

    $fetchCat = $mysqli->query("SELECT * from categories");
    while($row = $fetchCat->fetch_assoc()) {
        $fetchPost = $mysqli->query("SELECT id, title, slug from posts where category=".$mysqli->real_escape_string($row['id'])." limit 10");
        // Processing my code.
    }
?>

can i have some "inner join" query, which can reduce my query to 1-2 query and get me same results as above one ?

I want to have 10 articles for each category to be fetched. In future, i may have 40-45 category, and for each category , on an average, i may have 80-90 posts. While fetching all posts for 40-45 category from above method, can take my application on coaster ride. So i need some feasible method, where i can limit my posts record for each 40-45 category.

This is not simple inner join, where i am fetching posts, but this is actually limiting the inner join records to display for each parent table.

</div>

Answer 1

I finally found the solution in 2 query. There was little improvement.

1st Query, I ran for category and stored them in an array.

$cat_array = array();
$fetchCat = $mysqli->query("SELECT * from categories");
while($rowCat = $fetchCat->fetch_assoc()) {
   // Category processing....
}

Second Query , i ran against post using group_concat and SUBSTRING_INDEX to get 10 records of each category.

$post_array = array();
$fetchPost = $mysqli->query("select category, 
             SUBSTRING_INDEX(group_concat(id), ',', 10) as post_id, 
             SUBSTRING_INDEX(group_concat(title), ',', 10) as post_title, 
             SUBSTRING_INDEX(group_concat(slug), ',', 10) as post_slug from posts 
             group by category;");

while($rowPost = $fetchPost->fetch_assoc()) {
    $post_array[ $rowPost['category'] ] [id] = $rowPost['post_id'];
    $post_array[ $rowPost['category'] ] [title] = $rowPost['post_title'];
    $post_array[ $rowPost['category'] ] [slug] = $rowPost['post_slug'];
}

2 Query and all required data [ categories table data, 10 posts table data from each category ]

I had to do some explode for post_id , post_title, post_slug and use it in my application.

Now, to get list of all title and slug for any category, it was simple, eg, for category id "1" all i had to do is :-

$post_array[1][id]  // Returns all post_id associated with category 1.

A great thank for @billynoah, for pointing me in "group wise" direction, "AsConfused" for going through my query and letting me know, there is command analyze table posts too.

Thanks