How to get the descendants of all objects in an array? (Rails + ancestry gem)

Question

I am using this gem https://github.com/stefankroes/ancestry for my People modle. The table looks like this:

+----+----------+
| id | ancestry |
+----+----------+
|  1 | NULL     |
|  2 | 1        |
|  3 | 1        |
|  4 | 1/2      |
|  5 | 1/3      |
|  6 | NULL     |
|  7 | 1/2      |
|  8 | 1/2      |
|  9 | 1/3      |
| 10 | NULL     |
| 11 | 10       |
| 12 | 10       |
| 13 | NULL     |
| 14 | NULL     |
| 15 | 6        |
| 16 | 6        |
| 17 | 6/16     |
| 18 | 6        |
| 19 | 6/18     |
| 20 | 14       |
+----+----------+

What I want to query is:

1. given two people with id 1 and 6

2. get all descendants of these two people

3. in one query instead of querying one by one since I need to put this into an Arel.or method.

I knew that using:

People.select("id").where(People.arel_table[:ancestry].matches("6/%"))

generates LIKE sql statement and returns all grandchildren of the people with id 6. I also knew:

People.select("id").where(People.arel_table[:ancestry].matches(["1/%","6/%"]))

doesn't work because it generates invalid LIKE statement:

SELECT id FROM `people` WHERE (`people`.`ancestry` LIKE '1/%', '6/%')

On the other hand, I knew:

People.select("id").where(People.arel_table[:ancestry].in(["1", "6"]))

generates IN sql statement and returns all children (but not grandchildren) of both 1 and 6. I also knew:

People.select("id").where(People.arel_table[:ancestry].in(["1/%", "6/%"]))

returns nothing because IN statement works as precisely matching.

My question then is: how can I put them all into a (probably chaining) query to get all descendants of both 1 and 6? In this example, I would expect the result is: [ 2, 3, 4, 5, 7, 9, 15, 16, 17, 18, 19 ].

Thank you very much for the suggestions

Answer 1

People.select("id").where(People.arel_table[:ancestry].matches("1/%").or(People.arel_table[:ancestry].matches("6/%"))