左联接仅获取一行 [英] left join only fetches one row

问题描述

当我使用fetchAll()时，此SQL仅从表category中提取一行，我知道至少有两行具有相同的信息.有什么想法吗?

This SQL only fetches one row from the table category when I'm using fetchAll(), I know there is atleast two rows with the same info. Any ideas?

点击此处查看有关我认为的内容的详细说明问题是

        $query = "        SELECT        
                                category.*,
                                GROUP_CONCAT('category_hierarchy.category_id' SEPARATOR ',') AS subcategories   

                FROM            category
                LEFT JOIN       category_hierarchy      ON      category.category_id = category_hierarchy.category_parent_id
                WHERE           category.type = '1'
                ORDER BY        category.sort_order ASC";


// Prepare.
    $stmt = $dbh->prepare($query);

// Execute.
    $stmt->execute();

// Fetch results.
    $categories = $stmt->fetchAll();

    $countedRows = count($categories);

    foreach($categories as $category) {
        $parent_arr = '';
        if(!empty($category['subcategories'])) {
            $parent_arr = array(display_children($category['subcategories']));
        } 

        $arr[] = array(
                'category_id'   => $category['category_id'],
                'title'         => $category['title'],
                'slug'          => $category['slug'],
                'url'           => $category['url'],
                'type'          => $category['type'],
                'sort_order'    => $category['sort_order'],
                'categories'    => $parent_arr
        );
    }

推荐答案

您有聚合函数group_concat，并且没有group by子句，它将始终返回一行，您可能需要在末尾添加group by

You have a aggregate function group_concat and without group by clause it will always return one row, you may need to add a group by at the end.

SELECT      
category.*,
GROUP_CONCAT('category_hierarchy.category_id' SEPARATOR ',') AS subcategories   
FROM            category
LEFT JOIN       category_hierarchy      ON      category.category_id = category_hierarchy.category_parent_id
WHERE           category.type = '1'
group by         category.category_id
ORDER BY        category.sort_order ASC

这篇关于左联接仅获取一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！