获取属于多个类别的帖子及其子类别会带来重复的记录。

Question

我试图在PHP/MySQL的类别页面中获取属于多个类别及其子类别的所有帖子。该操作涉及三个表，其结构/示例数据如下：

tb_categories

​

​

tb_posts

​

​

tb_posts_to_categories

​

​

PHP代码和SQL查询一起获取属于主类别及其子类别的所有帖子，它们是：

public function get_all_posts_by_category(&$output, $category_id)
{
    // Build database query
    $sql = "SELECT `p`.*, `ptc`.`post_id`, `ptc`.`category_id`, `c`.`category_id`, `c`.`category_name`, `c`.`category_url`  
FROM `tb_posts` AS `p` INNER JOIN `tb_posts_to_categories` AS `ptc` ON `p`.`post_id` = `ptc`.`post_id` INNER JOIN `tb_categories` AS `c` ON `ptc`.`category_id` = `c`.`category_id` 
WHERE `c`.`category_id` = $category_id AND `p`.`post_status` = 1 ORDER BY `p`.`post_id` DESC";

    // Execute database query
    $rows = $this->get_by_sql($sql);

    if(is_array($rows))
    {
        foreach($rows as $row)
        {
            $output[] = $row;
        }
    }

    // Build database query
    $sql = "SELECT * FROM `tb_categories` WHERE `category_parent` = $category_id";

    // Execute database query
    $rows = $this->get_by_sql($sql);

    if(is_array($rows))
    {
        foreach($rows as $row)
        {
            $category_id = $row->category_id;
            $this->get_all_posts_by_category($output, $category_id);
        }
    }
    return $output;
}

在类别页面上，我将上述功能称为：

    // Get all posts of a category and its subcategories
    $posts = $post_obj->get_all_posts_by_category($output, $category_id);

这将产生以下数组结果：

Array
(
    [0] => stdClass Object
        (
            [post_id] => 5
            [post_name] => Post 5
            [post_url] => post-5
            [category_id] => 2
            [category_name] => Nature
            [category_url] => nature
        )

    [1] => stdClass Object
        (
            [post_id] => 2
            [post_name] => Post 2
            [post_url] => post-2
            [category_id] => 2
            [category_name] => Nature
            [category_url] => nature
        )

    [2] => stdClass Object
        (
            [post_id] => 1
            [post_name] => Post 1
            [post_url] => post-1
            [category_id] => 2
            [category_name] => Nature
            [category_url] => nature
        )

    [3] => stdClass Object
        (
            [post_id] => 5
            [post_name] => Post 5
            [post_url] => post-5
            [category_id] => 3
            [category_name] => Lakes
            [category_url] => lakes
        )

    [4] => stdClass Object
        (
            [post_id] => 4
            [post_name] => Post 4
            [post_url] => post-4
            [category_id] => 3
            [category_name] => Lakes
            [category_url] => lakes
        )

    [5] => stdClass Object
        (
            [post_id] => 3
            [post_name] => Post 3
            [post_url] => post-3
            [category_id] => 3
            [category_name] => Lakes
            [category_url] => lakes
        )

)

很明显，我们可以看到[post_id] => 5有一个重复的记录，因为带有id 5的post被分配到两个不同的类别--类别2(父类别-自然)和类别3(子类别-湖泊)。但是在主类别页面(性质)上，我在foreach迭代中有两次Post 5。

如何避免这些重复的记录？正确的SQL查询将是什么，或者我需要对PHP函数进行什么调整才能只获取唯一的记录？请帮我搞定这家伙。

Answer 1

只需获取父id的所有子标记id，并将它们放在一个简单的数组中即可。然后在查询中使用in()进行distinct选择。请看下面：

public function get_all_posts_by_category(&$output, $category_id)
{
    //Array of categories with parent category at top
    $cats = array();
    $cats[] = $category_id;

    //Fetch all sub categories
    $sql = "SELECT * FROM `tb_categories` WHERE `category_parent` = $category_id";

    // Execute database query
    $rows = $this->get_by_sql($sql);

    if(is_array($rows))
    {
        foreach($rows as $row)
        {
            $cats[] = $row->category_id; //Push category id to array
        }
    }


    // Build database query to fetch posts
    $sql = "SELECT distinct `p`.*, `ptc`.`post_id`, `ptc`.`category_id`, `c`.`category_id`, `c`.`category_name`, `c`.`category_url`  
    FROM `tb_posts` AS `p` INNER JOIN `tb_posts_to_categories` AS `ptc` ON `p`.`post_id` = `ptc`.`post_id` INNER JOIN `tb_categories` AS `c` ON `ptc`.`category_id` = `c`.`category_id` 
    WHERE `c`.`category_id` in (".implode(',',$cats).") AND `p`.`post_status` = 1 ORDER BY `p`.`post_id` DESC";

    // Execute database query
    $rows = $this->get_by_sql($sql);

    if(is_array($rows))
    {
        foreach($rows as $row)
        {
            $output[] = $row;
        }
    }

    return $output;
}