title: Finding the last ancestor for each record in a self-join association
body: <p>I have a table Products(id, product_id, (name)...)</p>
  
  <p>Each product is owned by another product (this chain can have multiple links)</p>
  
  <p>The 'name' attribute is only defined on the last ancestor in the product chain, so if I want to display a list of products, i'll have to recursively query for the name of each record.</p>
  
  <p>Because of this, given a list of product ids, I want to return the oldest ancestor for each one of these, so that I may eager load these instead.</p>
  
  <p>Ive have tried using the recursive self join tree in postgres, but have not been able to come up with anything that works</p>
  
  <p>my attempt:</p>
  
  <pre><code>self.where('products.id IN
                    (WITH RECURSIVE tree(id) AS (
                       SELECT product.id
                       WHERE product.id IN (?)
                     UNION ALL
                       SELECT product.id
                       JOIN tree ON product.id = tree.id)
                      SELECT id FROM tree)', product_ids)
  </code></pre>
  
  <p>any help is much appreciated</p>
  
  <p>Thanks</p>