title: Nested set categories, visibility
body: <p>I have two tables pages and pages_to_menu, the second one keeps the nested data for the menu, they look like this:</p>
  
  <pre><code> pages : ID | Title | Published
          ----------------------
           1     P1      1
           2     P2      0
           3     P2.1    1
           4     P2.2    1
  
   pages_to_menu:
           ID | lft | rgt | level | page_id | parent_id
           ---------------------------------------------
            1    1     10     0        0         0
            2    2     3      1        1         1
            3    4     9      1        2         1
            4    5     6      2        3         3
            5    7     8      2        4         3
  </code></pre>
  
  <p>so when I execute this query I'm able to get whole tree:</p>
  
  <pre><code>SELECT node.level, node.page_id, page.title
  FROM 
          pages_to_menu AS node
          Left Join pages as page on page.id = node.page_id
          ,
          pages_to_menu AS parent
  WHERE 
          node.lft BETWEEN parent.lft AND parent.rgt  
  
  GROUP BY node.id
  ORDER BY node.lft 
  </code></pre>
  
  <p>Shows this:</p>
  
  <pre><code>  level | page_id | Title
    ------------------------
     0          0       Null
     1          1       P1
     1          2       P2
     2          3       P2.1
     2          4       P2.2
  </code></pre>
  
  <p>The problem is that P2 is published = 0 so P2 and all it's children, P2.1, P2.2 have to be hidden</p>
  
  <p>the right output must be </p>
  
  <pre><code>  level | page_id | Title
    ------------------------
     0          0       Null
     1          1       P1
  </code></pre>
  
  <p>I tried with</p>
  
  <pre><code>SELECT node.level, node.page_id, page.title
  FROM 
          pages_to_menu AS node 
          ,
          pages_to_menu AS parent
          INNER Join pages as page on page.id = parent.page_id  AND page.published = 1
  WHERE 
          node.lft BETWEEN parent.lft AND parent.rgt  
  
  GROUP BY node.id
  ORDER BY node.lft 
  </code></pre>
  
  <p>But this effects only the parent node</p>
  
  <pre><code>  level | page_id | Title
    ------------------------
     0          0       Null
     1          1       P1 
     2          3       P2.1
     2          4       P2.2
  </code></pre>
  
  <p>P2.1 and P2.2 are still there.</p>
  
  <p>Sorry if my English is a bit bad, and thank you in advanced for any help!</p>