Question: Can't Figure Out Manager Tree about activerecordextended HOT 6 CLOSED

this SELECT "groups".* FROM "groups" needs to turn into this SELECT "gtree".* FROM "gtree"

You're not calling the CTE you created.

from activerecordextended.

Okay. I'm looking at that now, though after a full day I'm a bit foggy. I think that query is being built in the
statement Group.union_all(group, recursive).to_union_sql

Or is it built here? Group.with.recursive(gtree: Group.union_all(base, recursive).to_union_sql)

I hard-coded the statement

   sql =<<EOS;
WITH RECURSIVE "gtree" AS ((
  (SELECT "groups".* FROM "groups" WHERE "groups"."id" = '7918c477-8843-471a-bfcd-e47982fed0c6')
  UNION ALL (SELECT "groups".* FROM "groups" JOIN gtree ON gtree.id = groups.parent_id)
  )) SELECT "gtree".* FROM "gtree"
EOS
      ActiveRecord::Base.connection.execute(sql)

The result is 7918c477-8843-471a-bfcd-e47982fed0c6 returns itself all its children. This is further than I was. I need to figure out how to get gtree into the ARExtended to get just this to work.

Then to work out how to get the heirarchy. I'm learning. But, this is going to be worth it since my old query was taking about 46ms and this takes 12ms (to get root, then its descendants).

from activerecordextended.

I've spent a couple hours trying to find how to get the following statement:

group = Group.where(id: root_id)
recursive = Group.joins('JOIN gtree ON gtree.id = groups.parent_id')
Group.with.recursive(gtree: Group.union_all(group, recursive).to_union_sql)

To create SELECT "gtree".* FROM "gtree" as the final query instead of SELECT "groups".* FROM "groups"

The closest I've gotten was:

group = Group.where(id: root_id)
recursive = Group.joins('JOIN gtree ON gtree.id = groups.parent_id')
Group.select('gtree.*').with.recursive(gtree: Group.union_all(group, recursive).to_union_sql)

which got me to SELECT "gtree".* FROM "groups"

I've read through your code and your tests. I don't see anywhere it's possible in ARExtended.

Edit: Okay, this is probably because the query should be in a relationship class instead. Going down that path, it doesn't like that there's a missing table. So, I'm heading into view country. But, I have this working as a raw query. Is there a better way?

I got it to work with the following hack:

group = Group.where(id: root_id)
recursive = Group.joins('JOIN gtree ON gtree.id = groups.parent_id')
sql = Group.select('gtree.*').with.recursive(
        gtree: Group.union_all(group, recursive).to_union_sql
      ).to_sql.sub(/FROM "groups"$/, 'FROM "gtree"')

return Group.find_by_sql(sql)

from activerecordextended.

You have 2 options.

Either:

Group.with.recursive(gtree: ...).select("*").from("gtree")

or

Group.with.recursive(gtree: ...).joins("INNER JOIN gtree ON gtree.id = groups.id")

from activerecordextended.

Wait...I have choices?! How dare you give me options. :-)

Here's what I have which appears to be working as advertized.

base = Group.where(id: root_id)
recursive = Group.joins('JOIN gtree ON gtree.id = groups.parent_id')
Group.with.recursive(
   gtree: Group.union_all(base, recursive).to_union_sql
).select("*").from("gtree")

Thank you for your patience. And, for letting me mind-dump in the comments of this question so others might benefit. I am very glad I was able to get ActiveRecord Extended to work. Keep up the good work.

from activerecordextended.

For what it's worth, the original query tracked around 36-42ms, this query was 0.9ms. That's a 4-5x improvement

from activerecordextended.