title: Postgres with recursive query with multiple tables
body: <p>I need to find all the child items of a parent item recursively in postgres.</p>
  
  <p>I have a table structure like this:</p>
  
  <pre><code>items
  -----
  id
  
  modifiers
  ---------
  id
  
  item_modifiers
  --------------
  id
  item_id
  modifier_id
  
  modifier_items
  --------------
  id
  item_id
  modifier_id
  </code></pre>
  
  <p>Item has_many <code>child_modifiers</code> through <code>item_modifiers</code>
  Modifier has_many <code>child_items</code> through <code>modifier_items</code>.</p>
  
  <p>So basically the hierarchy goes like this: <code>item -&gt; item_modifiers -&gt; modifier -&gt; modifier_items -&gt; item</code></p>
  
  <p>I have this but not working. Not sure what to change.</p>
  
  <pre><code>    WITH RECURSIVE search_tree(id, path) AS (
        SELECT "items".id, ARRAY["items".id]
        FROM items
        WHERE "items".id ='#{top_level_item.id}'
      UNION ALL
        SELECT #{table_name}.id, path || #{table_name}.id
        FROM search_tree
        INNER JOIN modifier_items
        ON "search_tree".id = "modifier_items".item_id
        WHERE "modifier_items".modifier_id IN (
          SELECT "modifiers".id
          FROM modifiers
          INNER JOIN item_modifiers ON "modifiers".id = "item_modifiers".modifier_id
          WHERE "item_modifiers".item_id = "search_tree".id
        )
        WHERE NOT #{table_name}.id = ANY(path)
    )
    SELECT id FROM search_tree ORDER BY path
  </code></pre>
  
  <p>Any ideas?</p>