title: Using recursive CTE with Ecto
body: <p>How would I go about using the result of a recursive CTE in a query I plan to run with Ecto? For example let's say I have a table, nodes, structured as so:</p>
  
  <pre><code>-- nodes table example --
  
  id  parent_id
  1   NULL
  2   1
  3   1
  4   1
  5   2
  6   2
  7   3
  8   5
  </code></pre>
  
  <p>and I also have another table nodes_users structured as so:</p>
  
  <pre><code>-- nodes_users table example --
  
  node_id   user_id
  1         1
  2         2
  3         3
  5         4
  </code></pre>
  
  <p>Now, I want to grab all the users with a node at or above a specific node, for the sake of an example let's choose the node w/ the id 8.</p>
  
  <p>I could use the following  recursive <a href="/questions/tagged/postgresql" class="post-tag" title="show questions tagged &#39;postgresql&#39;" rel="tag">postgresql</a> query to do so:</p>
  
  <pre><code>WITH RECURSIVE nodes_tree AS (
      SELECT *
      FROM nodes
      WHERE nodes.id = 8
  UNION ALL
      SELECT n.*
      FROM nodes n
      INNER JOIN nodes_tree nt ON nt.parent_id = n.id
  )
  SELECT u.* FROM users u
  INNER JOIN users_nodes un ON un.user_id = u.id
  INNER JOIN nodes_tree nt ON nt.id = un.node_id
  </code></pre>
  
  <p>This should return users.* for the users w/ id of 1, 2, and 4.</p>
  
  <p>I'm not sure how I could run this same query using ecto, ideally in a manner that would return a chainable output. I understand that I can insert raw SQL into my query using the fragment macro, but I'm not exactly sure where that would go for this use or if that would even be the most appropriate route to take.</p>
  
  <p>Help and/or suggestions would be appreciated!</p>