php - 联合条件查询

Question

有谁知道我如何编写查询来从该数据库获得所需的结果，例如工具/电梯规划/cranimax

resource_category_id    category_name   parent_category     
    1                            Tools                  0    
    2                    product literature             0   
    3                   Terms and Conitions             0   
    4                        crane library              1   
    5                         geniune                   1   
    6                       lift planning               1   
    8                         cranimax                  6

为此，我编写了一个这样的查询，但尚未完成，例如，如果我从 cranimax 中选择，它就会有一个parent_category 6因此它应该与具有resource_category_id的行和具有parent_category1的行合并，它应该重复直到父类别0出现。

$data['breadcrumbs'] = $this->Manito_model->get_breadcrumbs_details($resource_id);

public function get_breadcrumbs_details($resource_id)
{
    $query=$this->db->query("select * from resource_category as m WHERE m.resource_category_id = $resource_id union (select * from resource_category where parent_category != 0) as m2 on m.parent_category = m2.resource_parent_category"); 


    return $query->result();


}

我希望我的结果是这样的

array (size=3)
 0 => 
   object(stdClass)[35]
    public 'resource_category_id' => string '8' (length=1)
    public 'category_name' => string 'cranimax' (length=8)
    public 'parent_category' => string '6' (length=1)
    public 'created_at' => string '2017-11-04 13:59:39' (length=19)
 1 => 
   object(stdClass)[35]
    public 'resource_category_id' => string '6' (length=1)
    public 'category_name' => string 'lift planning' (length=8)
    public 'parent_category' => string '1' (length=1)
    public 'created_at' => string '2017-11-04 13:59:39' (length=19)

 2 => 
   object(stdClass)[35]
    public 'resource_category_id' => string '1' (length=1)
    public 'category_name' => string 'Tools' (length=8)
    public 'parent_category' => string '0' (length=1)
    public 'created_at' => string '2017-11-04 13:59:39' (length=19)

Answer 1

UNION 用于将多个 SELECT 语句的结果组合成一个结果集(即使其变得非常简单:您需要执行两次选择，但您只想要一个结果，然后进行并集)在您的情况下，您不需要 UNION，而是需要一个简单的 JOIN，它可以使用其他数据“完成”您的结果:

select m.*, m2.category_name AS parent_name FROM resource_category AS m LEFT JOIN resource_category m2 ON m2.resource_category_id = m.resource_parent_category WHERE m.resource_category_id = $resource_id