Return a query with nested results with Rails ActiveRecord

Question

I'm trying to grab a list of active tasks for a workflow tool I'm making, with the data structured like this:

User
- has_many Projects
  - has_many Subprojects
    - has_many Tasks
      - has_many TimeLogs

Active tasks are defined as any task with a TimeLog that does not have a 'completed' timestamp.

I'm trying to make the main page display this full structure, but only show parent structures that have an active task at some level. Any Users/Projects/Subprojects that don't have an active task beneath them should not be returned by the query.

So far I've tried:

A join on all four tables, which produces duplicate rows
A WHERE EXISTS statement, which returns only the relevant users, but doesn't maintain the WHERE clause when I try to access its children

Is there a way to achieve this without manually culling the data in Ruby?

You can add .uniq onto the end of your join query. ActiveRecord will then query without the duplicates. — j-dexx
– j-dexx, Commented Oct 28, 2014 at 14:00
That works for the top-level Users, but when I grab the child Projects of a User, it just grabs all projects. Carrying down the where clause through the children seems messy, so I'm looking for a way to just grab it all at once, maybe convert the ActiveRecord query to a nested Hash? — Steve Pletcher
– Steve Pletcher, Commented Oct 28, 2014 at 14:26

blelump · Accepted Answer · 2014-10-28 14:55:19Z

1

Due to the nested level of problem it might be quite complicated. As I understand, you want to omit users|projects|subprojects, that do not have any active tasks. There's no simple sql that will let you achieve that by:

users.each do |user|
  user.active_projects.each do |project|
    ...
  end
end

Instead, I would query for tasks first, i.e. @tasks = Task.includes(:subproject => [:project => :user]).where("status NOT IN ('completed')"). Then you have incompleted tasks and now all you need to do is to reorder fetched data, I mean:

@tasks = @tasks
.group_by {|t| t.subproject.project.user }
.reduce({}) do |sum, (user, tasks)|
  sum[user] ||= {}
  tasks.each do |task|
    sum[user][task.subproject.project] ||= {}
    sum[user][task.subproject.project][task.subproject] ||= []
    sum[user][task.subproject.project][task.subproject] << task 
  end
  sum
end

I haven't tested this, but that's the idea.

edited Oct 28, 2014 at 14:55

answered Oct 28, 2014 at 14:42

blelump

3,2431 gold badge18 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Steve Pletcher Over a year ago

Bleh, so the best way to do this is by constructing the tree manually. I was worried that was going to be the case. Thanks for the code samples.

nikkon226 · Accepted Answer · 2014-10-28 14:48:27Z

0

You could try this based on the activerecord queries documents:

user.projects.joins(subprojects: { tasks: [{ time_logs: { timestamp: 'completed'} }] })

I can't test it, but give it a shot.

answered Oct 28, 2014 at 14:48

nikkon226

1,0087 silver badges11 bronze badges

1 Comment

Steve Pletcher Over a year ago

Unfortunately, that was my first idea, but joins returns duplicate rows if a User has more than one active task, and as I mentioned in the comments, .uniq doesn't apply the where clause to child relations.

Collectives™ on Stack Overflow

Return a query with nested results with Rails ActiveRecord

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related