3

Requirements:

I am building a task list application and wanted tasks to be able to have sub-tasks.
I also wanted tasks to be able to exist in multiple places in the tree at once, for example, if I had the 2 tasks:

  1. Build dog kennel
  2. Put up new fence

If I planned on building the dog kennel out of the same material as the fence, both of these tasks would have a subtask of "Buy fence palings".

My problematic implementation (feedback welcome):

I have 2 models:

  • Node (has_ancestry and belongs_to :task)
  • Task (has_many :nodes)

This means the tree (that allows me to have subtasks) does not store the task it self, just a reference to a task object.

Here is an example using the rails console:

t1 = Task.create :name => "Build dog kennel"
n1 = Node.create :task => t1

t2 = Task.create :name => "Put up new fence"
n2 = Node.create :task => t2

t3 = Task.create :name => "Buy fence palings"
n11 = Node.create :task => t3, :parent => n1
n21 = Node.create :task => t3, :parent => n2

t4 = Task.create :name => "Construct the fence"
n22 = Node.create :task => t4, :parent => n2

n2.children.each { |c| puts c.task.name }

This last line gives the following output, indicating a select n+1:

Node Load (0.2ms)  SELECT "nodes".* FROM "nodes" WHERE "nodes"."ancestry" = '12'
Task Load (0.2ms)  SELECT "tasks".* FROM "tasks" WHERE "tasks"."id" = 11 LIMIT 1
Buy fence palings
Task Load (0.2ms)  SELECT "tasks".* FROM "tasks" WHERE "tasks"."id" = 10 LIMIT 1
Put up new fence

Help?

I'm quite new to Ruby on Rails and ActiveRecord, but I would think all I need to do is join the nodes table with the tasks table based on the nodes.task_id foreign key, but I have looked through the Ancestry documentation and cant find anything useful.

In the future I plan on fetching more information from the task object via foreign keys too, such as author, related comments, etc. and with this implementation, one page load could trigger quite a lot of select queries :(

Can anyone offer me suggestions on how to accomplish this?
Is there a way to force eager loading? (Would that help?)
I'm open to feedback if you have a better idea how to accomplish this.

Thanks in advance!

Steve Occhipinti
  • 478
  • 5
  • 12

1 Answers1

9

So after playing around for a while, I finally found a way to do this.

Instead of this line:

n2.children.each { |c| puts c.task.name }

which results in this:

Node Load (0.2ms)  SELECT "nodes".* FROM "nodes" WHERE "nodes"."ancestry" = '27'
Task Load (0.2ms)  SELECT "tasks".* FROM "tasks" WHERE "tasks"."id" = 23 LIMIT 1
Buy fence palings
Task Load (0.2ms)  SELECT "tasks".* FROM "tasks" WHERE "tasks"."id" = 24 LIMIT 1
Construct the fence

I used this line:

n2.children.find(:all, :include => :task).each { |c| puts c.task.name }

Which resulted in this:

Node Load (0.2ms)  SELECT "nodes".* FROM "nodes" WHERE "nodes"."ancestry" = '27'
Task Load (0.2ms)  SELECT "tasks".* FROM "tasks" WHERE "tasks"."id" IN (23, 24)
Buy fence palings
Construct the fence

This should only ever execute 2 queries, regardless of the size and the resulting set will include the tasks!
I know this is probably basic stuff, but it may be a little confusing for new-comers like myself as the section of the rails guides that refers to eager loading only shows the class method includes()

Steve Occhipinti
  • 478
  • 5
  • 12