Skip to content

Instantly share code, notes, and snippets.

@freshface
Last active August 29, 2015 14:21
Show Gist options
  • Save freshface/07490f45ff58766de64a to your computer and use it in GitHub Desktop.
Save freshface/07490f45ff58766de64a to your computer and use it in GitHub Desktop.
Treepaths
public static function getTreeByParent($ids)
{
$ids = (array) $ids;
$ids = implode($ids, ',');
// @todo see http://stackoverflow.com/questions/8252323/mysql-closure-table-hierarchical-database-how-to-pull-information-out-in-the-c/8288201#8288201 for visual
// the whole tree
// http://karwin.blogspot.be/2010/03/rendering-trees-with-closure-tables.html
// build query
$query = 'SELECT d.id, d.child_of, c.name, d.sequence,
CONCAT(REPEAT("-- ", p.length), c.name) as hier,
p.length, p.ancestor, p.descendant,
GROUP_CONCAT(DISTINCT crumbs.ancestor ORDER BY crumbs.ancestor) AS breadcrumbs
FROM shop_categories AS d
JOIN shop_categories_treepaths AS p ON d.id = p.descendant
JOIN shop_categories_treepaths AS crumbs ON crumbs.descendant = p.descendant
LEFT JOIN shop_categories_content as c ON c.category_id = d.id
WHERE p.ancestor IN (' . $ids . ') AND c.language = ?
GROUP BY d.id
ORDER BY breadcrumbs';
// execute the query
return BackendModel::get('database')->getRecords($query, array(Language::getWorkingLanguage()));
}
array(6) {
[0] => array(9) {
["id"] => string(1) "2"
["child_of"] => NULL
["name"] => string(8) "Parent B"
["sequence"] => string(1) "2"
["hier"] => string(8) "Parent B"
["length"] => string(1) "0"
["ancestor"] => string(1) "2"
["descendant"] => string(1) "2"
["breadcrumbs"] => string(1) "2"
}
[1] => array(9) {
["id"] => string(1) "5"
["child_of"] => string(1) "2"
["name"] => string(16) "first child of B"
["sequence"] => string(1) "2"
["hier"] => string(19) "-- first child of B"
["length"] => string(1) "1"
["ancestor"] => string(1) "2"
["descendant"] => string(1) "5"
["breadcrumbs"] => string(3) "2,5"
}
[2] => array(9) {
["id"] => string(1) "6"
["child_of"] => string(1) "2"
["name"] => string(36) "second child of B but first in order"
["sequence"] => string(1) "1"
["hier"] => string(39) "-- second child of B but first in order"
["length"] => string(1) "1"
["ancestor"] => string(1) "2"
["descendant"] => string(1) "6"
["breadcrumbs"] => string(3) "2,6"
}
[3] => array(9) {
["id"] => string(2) "20"
["child_of"] => NULL
["name"] => string(8) "Parent A"
["sequence"] => string(1) "1"
["hier"] => string(8) "Parent A"
["length"] => string(1) "0"
["ancestor"] => string(2) "20"
["descendant"] => string(2) "20"
["breadcrumbs"] => string(2) "20"
}
[4] => array(9) {
["id"] => string(1) "3"
["child_of"] => string(1) "1"
["name"] => string(17) "second child of A"
["sequence"] => string(1) "2"
["hier"] => string(20) "-- second child of A"
["length"] => string(1) "1"
["ancestor"] => string(2) "20"
["descendant"] => string(1) "3"
["breadcrumbs"] => string(4) "3,20"
}
[5] => array(9) {
["id"] => string(1) "4"
["child_of"] => string(1) "1"
["name"] => string(16) "first child of A"
["sequence"] => string(1) "1"
["hier"] => string(19) "-- first child of A"
["length"] => string(1) "1"
["ancestor"] => string(2) "20"
["descendant"] => string(1) "4"
["breadcrumbs"] => string(4) "4,20"
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment