add parent_sql

[andrelip]

When retrieving the parent resource name for a list of clients I had to rely on N+1 when accessing the data using Rails. With Elixir and I could easily retrieve and query that list by joining a substring:

join: parent in "resource_table",
on: parent.id == fragment("SUBSTRING_INDEX(?, '\/', -1)", e.ancestry),
where: parent.score > 600...

Is there a native functionality for including and querying in this library?

[kbrock]

Hello @andrelip

We currently do not string munge.

If I hear you correctly, you would like something like the following (none of this code works, just trying to understand your use case):

clients = Records.where(:id => [1,2,3,4,5])
result = clients.map(&:parent).map(&:name)

#

Record.parents_of(Records.where(:id => [1,2,3,4,5]).pluck(:name)
# =>
SELECT name
FROM records
WHERE id IN (
  SELECT SUBSTRING_INDEX(ancestry, '/', -1)::INTEGER
  FROM records WHERE id IN (1,2,3,4,5)
)

There problem currently lies in the to_node calls in the conditions. Those cause the node lookups.
If it were smarter and differentiated between an object, an array of nodes, and a scope, we would see a great benefit.

Do you have any suggestions for that block of code?

[kbrock]

this is also related to #308

[kbrock]

actually the problem is we can not currently implement has_many :children, :foreign_key => :parent_id

I am looking into this.
Have belongs_to :parent working but not has_many :children working