左联接只获取一行 - left join only fetches one row

left join only fetches one row

本文关键字：一行获取 | 更新日期: 2023-09-27

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

点击此处查看我认为问题是的更详细解释

        $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。

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