选择具有父子关系的类别数据查询 [英] select query for category data with parent child relationship

问题描述

我有这个数据库表

CREATE TABLE IF NOT EXISTS `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `inherit` enum('Y','N') NOT NULL DEFAULT 'N',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;

--
-- Dumping data for table `category`
--

INSERT INTO `category` (`id`, `parent_id`, `name`, `inherit`) VALUES
(1, 0, 'Fruits', 'N'),
(2, 0, 'Electronics', 'N'),
(3, 0, 'Furniture', 'N'),
(4, 0, 'Garden', 'N'),
(5, 1, 'Apples', 'N'),
(6, 1, 'Bananas', 'N'),
(7, 5, 'Green Apples', 'Y'),
(8, 5, 'Red Apples', 'N'),
(9, 2, 'Mobiles', 'Y'),
(10, 2, 'Televisions', 'N'),
(11, 9, 'Android', 'N'),
(12, 9, 'iPhone', 'Y'),
(13, 7, 'One Dozen Green Apples', 'Y'),
(14, 7, 'Two Dozens Green Apples', 'N');

还有另一个表，其中保存了user_id，category_id，例如 user_id 1000可以看到1和5，我在会话中输入了此信息，以便查询成为

There is another table where I keep user_id, category_id e.g. user_id 1000 can see 1 and 5, I put this info in the sessions so that my query becomes

SELECT *
FROM `category`
WHERE id
IN ( 1, 5 )

此查询显示Fruits > Apples-一切正常.但是，我已将"Green Apples"标记为Inherit = 'Yes'，因此用户1000也应该看到"Green Apples"，但没有看到"Red Apples".如果在绿色苹果下有子类别标记为继承='Y'...例如一打青苹果"也应该列出！

this query shows Fruits > Apples - all this works fine. But, I have marked "Green Apples" as Inherit = 'Yes' so user 1000 should see "Green Apples" too but not "Red Apples". If there are sub-categories under Green Apples marked as inherit = 'Y'... e.g. "One Dozen Green Apples" should be listed too!!

我本以为可以让UNION试一试，但不知道如何将深度提高2个以上……

I thought to give UNION a go but can't figure out how to get more than 2 levels deeper ...

SELECT * FROM (
    SELECT *
    FROM `category`
    WHERE id
    IN ( 1, 5 )

    UNION

    SELECT c.*
    FROM `category` c
    INNER JOIN `category` parent ON parent.id = c.id AND c.inherit = 'Y'
    WHERE c.parent_id
    IN ( 1, 5 )
) all_cats

您对我有什么建议?我是开放式的表结构更改，如果这样做会使查询变得更容易吗?谢谢

What would you suggest to me? I am open table structure changes if that'd make querying any easier? thanks

推荐答案

尝试自我加入:

编辑:我忘了放WHERE子句

I forgot to put the WHERE clause

SELECT
    a.name,
    b.name,
    c.name
FROM 
    category as a
LEFT JOIN category as b
    ON b.parent_id = a.id
INNER JOIN category as c
    ON c.parent_id = b.id
    AND c.inherit = 'Y'
WHERE
    a.id = 1

但是理想的解决方案是具有一个递归函数来执行此操作，因为在该表中您正在描述类别树.上面的查询是静态的，它可以追溯到2个级别(2个子类别)，据我了解，您需要动态的东西.

But the ideal solution is to have a recursive function that will do this since in that table you are describing a tree of categories . The query above is static it goes back 2 levels (2 subcategories) and from what i understand you need something dynamic .

类似于下面的功能:

public String getCategory(int categId){

    String sSql = "SELECT name FROM category WHERE id = " + categId ;
    String name = oDb.exec(sql).get("name");

    sSql = "SELECT id FROM category WHERE inherit = 'Y' AND parent_id = " + categId ;
    int nextCategId = oDb.exec(sql).get("id");

    if(nextCategId != null){
        return name + "," + getCategory(nextCategId);
    }else{
        return name;
    }

}

因此，假设Apples categ是继承的，则getCategory(1)的结果应为Fruits,Apples,Green Apples

So assuming that Apples categ is inherited the result for getCategory(1) should be Fruits,Apples,Green Apples

这篇关于选择具有父子关系的类别数据查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！