This is for MySQL and PHP
I have a table that contains the following columns:
navigation_id (unsigned int primary key)
navigation_category (unsigned int)
navigation_path (varchar (256))
navigation_is_active (bool)
navigation_store_id (unsigned int index)
Data will be filled like:
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
So this will represent a tree like structure. My goal is to write a SQL query that, given the store ID of 32 and category ID of 33, will return
First, a group of elements that are the parents of the category 33 (in this case 4 and 32)
Then, a group of elements that are a child of category 33 (in this case 34, 35, and 36)
Then the rest of the "root" categories under category 4 (in this case 37).
So the following query will return the correct results:
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))
My problem is that I want to order the "groups" of categories in the order listed above (parents of 33 first, children of 33 second, and parents of the root node last). So if they meet the first condition, order them first, if they meet the second condition order them second and if they meet the third (and fourth) condition order them last.
You can see an example of how the category structure works at this site:
www.eanacortes.net
You may notice that it's fairly slow. The current way I am doing this I am using magento's original category table and executing three particularly slow queries on it; then putting the results together in PHP. Using this new table I am solving another issue that I have with magento but would also like to improve my performance at the same time. The best way I see this being accomplished is putting all three queries together and having PHP work less by having the results sorted properly.
Thanks
EDIT
Alright, it works great now. Cut it down from 4 seconds down to 500 MS. Great speed now :)
Here is my code in the Colleciton class:
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;
}
Then I consume it with the following code found in my Category block:
// 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;
If I could accept two answers I would accept the first and last one, and if I could accept an answer as "interesting/useful" I would do that with the second. :)