Codeigniter 上的 mySQL 分层查询

发布于 2024-10-17 12:04:32 字数 1195 浏览 4 评论 0原文

我在尝试使用 slugs 从该表中获取结果时遇到了麻烦。

| id | parent | slug       | name       |  
-----------------------------------------
|  1 | 0      | animations | animations |
|  2 | 1      | flash      | flash      |
|  3 | 2      | looped     | looped     |
|  4 | 1      | gif        | gif images |

例如,我需要获取父级为“动画”、子级为“闪存”的类别。

真正的问题是因为我需要使用category/$parent_slug/$child_slug 搜索结果,而不是使用ID (category/$id) 来获取|3|2|looped|looped|

这是我到目前为止所得到的:

function get_category_childrens($category_parent=null){
    $this->db->select('*');
    if(!is_null($category_parent)){
        $this->db->where('categories.slug', $category_parent);
        $this->db->join('categories as l1', 'l1.parent = categories.id', 'left');
    }
    else{
        $this->db->where('categories.parent', '0');
    }
    $query = $this->db->get('categories');
    return $query->result_array();
} 

生成的 sql:

SELECT *
FROM (`categories`)
LEFT JOIN `categories` as l1 ON `l1`.`parent` = `categories`.`id`
WHERE `categories`.`slug` = 'animations'  

如果您不了解 CI,没问题,如果您有查询或对此有想法,请发表评论。

I'm havin troubles trying to get the results from this table using slugs.

| id | parent | slug       | name       |  
-----------------------------------------
|  1 | 0      | animations | animations |
|  2 | 1      | flash      | flash      |
|  3 | 2      | looped     | looped     |
|  4 | 1      | gif        | gif images |

For example i need to get the categories where the parent is 'animations' and the child is 'flash'.

The real issue is because i need to search for results using category/$parent_slug/$child_slug instead use ID's (category/$id) to get |3|2|looped|looped|.

This what i've so far:

function get_category_childrens($category_parent=null){
    $this->db->select('*');
    if(!is_null($category_parent)){
        $this->db->where('categories.slug', $category_parent);
        $this->db->join('categories as l1', 'l1.parent = categories.id', 'left');
    }
    else{
        $this->db->where('categories.parent', '0');
    }
    $query = $this->db->get('categories');
    return $query->result_array();
} 

The sql generated:

SELECT *
FROM (`categories`)
LEFT JOIN `categories` as l1 ON `l1`.`parent` = `categories`.`id`
WHERE `categories`.`slug` = 'animations'  

No problem if you dont know CI, if you've the query or an idea of it please comment.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

若无相欠,怎会相见 2024-10-24 12:04:32
SELECT *
FROM (`categories` as l1)
LEFT JOIN `categories` as l2 ON `l2`.`parent` = `l1`.`id`
LEFT JOIN `categories` as l3 ON `l3`.`parent` = `l2`.`id`
WHERE `l1`.`slug` = 'animations'
AND `l2`.`slug` = 'flash'
SELECT *
FROM (`categories` as l1)
LEFT JOIN `categories` as l2 ON `l2`.`parent` = `l1`.`id`
LEFT JOIN `categories` as l3 ON `l3`.`parent` = `l2`.`id`
WHERE `l1`.`slug` = 'animations'
AND `l2`.`slug` = 'flash'
攒一口袋星星 2024-10-24 12:04:32
SELECT categories.*
FROM categories
LEFT JOIN categories AS parent ON categories.parent = parent.id
LEFT JOIN categories AS child ON categories.id = child.parent
WHERE (parent.name='animations') and (child.name = 'flash')

我想这就是你所追求的。

SELECT categories.*
FROM categories
LEFT JOIN categories AS parent ON categories.parent = parent.id
LEFT JOIN categories AS child ON categories.id = child.parent
WHERE (parent.name='animations') and (child.name = 'flash')

is what I think you're after.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文