Custom Queries

Full tree

      SELECT n.name, COUNT(*)-1 AS level
      FROM md_components AS n,md_components AS p
      WHERE n.tree_left BETWEEN p.tree_left AND p.tree_left
      GROUP BY n.tree_left
      ORDER BY n.tree_left;
 

Tree with further info (level, children counter etc.):

 SELECT n.*,
 round((n.tree_right-n.tree_left-1)/2,0) AS countChildren,
 count(*)-1+(n.tree_left>1) AS level,
 ((min(p.tree_right)-n.tree_right-(n.tree_left>1))/2) > 0 AS countPredecessors,
 (((n.tree_left-max(p.tree_left)>1))) AS countSuccessors
 FROM md_components n, md_components p
 WHERE n.tree_left BETWEEN p.tree_left AND p.tree_right AND (p.component_id != n.component_id OR n.tree_left = 1)
 GROUP BY n.component_id
 ORDER BY n.tree_left;
 

Get parent nodes up to root node. Used for breadcrumbs.

SELECT p.*
FROM md_components n, md_components p
WHERE n.tree_left BETWEEN p.tree_left AND p.tree_right
AND n.component_id = 18
ORDER BY n.tree_left;
 

Getting the first level items of root 1

SELECT 
    o.*,
    COUNT(p.component_id)-1 AS level
FROM md_components AS n, 
    md_components AS p, 
    md_components AS o 
WHERE o.tree_left BETWEEN p.tree_left AND p.tree_right 
    AND o.tree_left BETWEEN n.tree_left AND n.tree_right
    AND n.component_id = 1 
GROUP BY o.tree_left
HAVING level = 1
ORDER BY o.tree_left
 

user's navigation tree filtered by group permissions using sf_guard plugin:

SELECT
mc.*
FROM
(
    md_components AS mc
    Left Join md_components_has_sf_guard_permission AS cp ON cp.md_components_component_id = mc.component_id
    Left Join sf_guard_permission AS pm ON pm.id = cp.sf_guard_permission_id
    Inner Join sf_guard_group_permission AS pg ON pm.id = pg.permission_id
    Left Join sf_guard_user_group AS ug ON pg.group_id = ug.group_id 
), md_components AS cm
 
WHERE
    mc.tree_left BETWEEN cm.tree_left AND cm.tree_left
AND
    ug.user_id =  1
GROUP BY mc.tree_left
      ORDER BY mc.tree_left;
 

user's navigation tree filtered by group permissions and additional user permissions using sf_guard plugin:

SELECT
mc.*
FROM
(
    md_components AS mc
    Left Join md_components_has_sf_guard_permission AS cp ON cp.md_components_component_id = mc.component_id
    Left Join sf_guard_permission AS pm ON pm.id = cp.sf_guard_permission_id
    Inner Join sf_guard_group_permission AS pg ON pm.id = pg.permission_id
    Left Join sf_guard_user_group AS ug ON pg.group_id = ug.group_id 
    Left Join sf_guard_user_permission AS um ON pm.id = um.permission_id
), md_components AS cm
 
WHERE
    mc.tree_left BETWEEN cm.tree_left AND cm.tree_left
AND
    (ug.user_id =  1 AND um.user_id=1)
GROUP BY mc.tree_left
      ORDER BY mc.tree_left;