创建SQL查询,按满足的条件对结果排序

这是MySQL和PHP的

我有一个包含以下列的表:

navigation_id (unsigned int primary key)
navigation_category (unsigned int)
navigation_path (varchar (256))
navigation_is_active (bool)
navigation_store_id (unsigned int index)

数据的填写方式如下:

1, 32, "4/32/", 1, 32
2, 33, "4/32/33/", 1, 32
3, 34, "4/32/33/34/", 1, 32
4, 35, "4/32/33/35/", 1, 32
5, 36, "4/32/33/36/", 1, 32
6, 37, "4/37/", 1, 32
... another group that is under the "4/37" node
... and so on

所以这将表示一个树状结构。我的目标是编写一个SQL查询,如果存储ID为32,类别ID为33,将返回

首先,属于33类父类的一组元素(在本例中是4和32)

然后,一组属于33类子元素(在本例中是34、35和36)

然后是类别4下的其余“根”类别(在本例中是37)。

因此,下面的查询将返回正确的结果:

SELECT * FROM navigation 
WHERE navigation_store_id = 32 
AND (navigation_category IN (4, 32) 
    OR navigation_path LIKE "4/32/33/%/" 
    OR (navigation_path LIKE "4/%/" 
        AND navigation_category <> 32))

我的问题是,我想按上面列出的顺序对类别的“组”排序(先是33个的父节点,后是33个的子节点,最后是根节点的父节点)。因此,如果它们满足第一个条件,首先对它们排序,如果它们满足第二个条件,则第二个条件,如果它们满足第三个(和第四个)条件,则最后对它们排序。

您可以看到类别结构如何在此网站上工作的示例:

网址:www.eanacortes.net

你可能会注意到它相当慢。目前我这样做的方式是使用Magento的原始类别表,并对其执行三个特别慢的查询;然后将结果放在PHP中。使用这个新的表,我正在解决另一个问题,我与马根托,但也希望提高我的表现在同一时间。我认为实现这一点的最好方法是将所有三个查询放在一起,通过正确地对结果进行排序,让PHP工作得更少。

谢谢

编辑

好吧,现在效果很好。把它从4秒减到500毫秒。现在速度很快:)

这是我在大学课堂上的代码:

    function addCategoryFilter($cat)
    {
        $path = $cat->getPath();
        $select = $this->getSelect();
        $id = $cat->getId();
        $root = Mage::app()->getStore()->getRootCategoryId();
        $commaPath = implode(", ", explode("/", $path));

        $where = new Zend_Db_Expr(
            "(navigation_category IN ({$commaPath}) 
                    OR navigation_parent = {$id}
                    OR (navigation_parent = {$root}
                    AND navigation_category <> {$cat->getId()}))");

        $order = new Zend_Db_Expr("
                CASE
                    WHEN navigation_category IN ({$commaPath})  THEN 1
                    WHEN navigation_parent = {$id} THEN 2
                    ELSE 3
                END, LENGTH(navigation_path), navigation_name");

        $select->where($where)->order($order);
        return $this;
    }

然后我使用在分类块中找到的以下代码来使用它:

        // get our data
        $navigation = Mage::getModel("navigation/navigation")->getCollection();
        $navigation->
            addStoreFilter(Mage::app()->getStore()->getId())->
            addCategoryFilter($currentCat);

        // put it in an array
        $node = &$tree;
        $navArray = array();
        foreach ($navigation as $cat)
        {
            $navArray[] = $cat;
        }
        $navCount = count($navArray);

        $i = 0;

        // skip passed the root category
        for (; $i < $navCount; $i++)
        {
            if ($navArray[$i]->getNavigationCategory() == $root)
            {
                $i++;
                break;
            }
        }

        // add the parents of the current category
        for (; $i < $navCount; $i++)
        {
            $cat = $navArray[$i];

            $node[] = array("cat" => $cat, "children" => array(), 
                "selected" => ($cat->getNavigationCategory() == $currentCat->getId()));
            $node = &$node[0]["children"];

            if ($cat->getNavigationCategory() == $currentCat->getId())
            {
                $i++;
                break;
            }
        }

        // add the children of the current category
        for (; $i < $navCount; $i++)
        {
            $cat = $navArray[$i];
            $path = explode("/", $cat->getNavigationPath());
            if ($path[count($path) - 3] != $currentCat->getId())
            {
                break;
            }

            $node[] = array("cat" => $cat, "children" => array(), 
                "selected" => ($cat->getNavigationCategory() == $currentCat->getId()));
        }

        // add the children of the root category
        for (; $i < $navCount; $i++)
        {
            $cat = $navArray[$i];
            $tree[] = array("cat" => $cat, "children" => array(), 
                "selected" => ($cat->getNavigationCategory() == $currentCat->getId()));
        }

        return $tree;

如果我能接受两个答案,我会接受第一个和最后一个,如果我能接受一个“有趣/有用”的答案,我会接受第二个。 :)