Path and db views

[brodock]

I've had the need to have some sort of "path enumeration", and figured out a SQL query (that I use as a view) to do that:

      SELECT nodes.id AS node_id, nodes.name,
        ( SELECT group_concat(node.code separator '.') AS path
          FROM nodes as node
          JOIN node_hierarchies as tree
                ON (node.id = tree.ancestor_id)
           WHERE tree.descendant_id = nodes.id
          ) AS path
      from nodes;

The output of this query looks like:
1, "leaf node", 1.2.1.1
1, "leaf node 2", 1.2

This is useful to make for example, a search based on "path" (think auto complete).

I don't know exactly how it could be incorporated by the gem, but think it is useful enough to be considered for.

[mceachen]

Thanks for the suggestion! What's node.code represent here?

[brodock]

node.code is what I use for the "path" (in the example is the "1.2.1.1 and the 1.2")... it has special pourpous on my application, but i think that what you would probably want to use is node.id or a node.permalink in some situation... (which leads to make it configurable)...

here is a more clear example:

      SELECT nodes.id AS node_id, nodes.name,
        ( SELECT group_concat(node.id separator '/') AS path
          FROM nodes as node
          JOIN node_hierarchies as tree
                ON (node.id = tree.ancestor_id)
           WHERE tree.descendant_id = nodes.id
          ) AS path
      from nodes;

this will produce a path that looks like: "1/4/25/76" where 1 is the root and 76 is the leaf