从MySQL中的两个连接表中获取多个计数

So I have two tables, categories and designs. I want to construct a query that will fetch all categories, along with the count of any sub categories (categories.parent_id equal to the categories.id) AND the count of any designs (design.category_id equal to categories.id)

所以我有两个表，类别和设计。我想构建一个查询，它将获取所有类别，以及任何子类别的计数（categories.parent_id等于categories.id）和任何设计的计数（design.category_id等于categories.id）

If I try to just get one of these counts, everything works fine, but when I try for both with the following code, the count for both is the same number (and not the correct number) for either.

如果我试图获得其中一个计数，一切正常，但当我尝试使用以下代码时，两者的计数是相同的数字（而不是正确的数字）。

        $this->db->select('categories.id AS id, categories.parent_id AS parent_id, categories.title AS title, 
        categories.description AS description, categories.img_path AS img_path, COUNT(designs.id) AS design_count, 
        COUNT(sub_categories.id) as sub_category_count');
        $this->db->from('categories');
        $this->db->join('designs',                          'categories.id = designs.category_id', 'left');
        $this->db->join('categories as sub_categories',     'categories.id = sub_categories.parent_id', 'left');
        $this->db->group_by('categories.id');

Any help will be much appreciated, cheers!

任何帮助将不胜感激，欢呼！

2 个解决方案

#1

Assuming that the root categories do not contain designs, here is the query that returns the necessary information:

假设根类别不包含设计，这里是返回必要信息的查询：

SELECT category.id, category.title, subcategory.id, designs.id
FROM categories category
LEFT JOIN categories subcategory ON category.id = subcategory.parent_id
LEFT JOIN designs ON subcategory.id = designs.category_id
WHERE category.parent_id IS NULL

Now all you need to do is to apply grouping:

现在您需要做的就是应用分组：

SELECT category.id, category.title, COUNT(DISTINCT subcategory.id), COUNT(designs.id)
FROM categories category
LEFT JOIN categories subcategory ON category.id = subcategory.parent_id
LEFT JOIN designs ON subcategory.id = designs.category_id
WHERE category.parent_id IS NULL
GROUP BY category.id, category.title

The key here is the use of COUNT(DISTINCT ...).

这里的关键是使用COUNT（DISTINCT ...）。

#2

SELECT c.id,c.title,
       IFNULL(sc.counted,0) AS subcategories,
       IFNULL(d.counted,0) AS designs
FROM categories c 
     LEFT JOIN 
     ( SELECT parent_id,COUNT(*) AS counted 
         FROM categories GROUP BY parent_id ) sc
       ON c.id=sc.parent_id
     LEFT JOIN
     ( SELECT category_id,COUNT(*) AS counted 
         FROM designs GROUP BY category_id ) d
       ON c.id=d.category_id
WHERE c.parent_id IS NULL ;

should get you the desired numbers as raw SQL.

应该为原始SQL提供所需的数字。

#1