Rails - Has many, with query get all nested and parent object

Question

I have been working on an scheduling app, and running into an issue with finding a way to efficiently get specific records.

Here are the models in question.

class Task < ApplicationRecord
 belongs_to :user, optional: true
 belongs_to :project, optional: true
 belongs_to :project_schedule, optional: true
 belongs_to :user_schedule, optional: true

class User < ApplicationRecord
 include Filterable
 validates :email, presence: true
 validates :email, uniqueness: true
 has_many :tasks

What the front end expects is a json of all users, with all tasks nested within them. This has become too cumbersome to load that many tasks and I was attempting to specify a date range to the tasks while still getting all users.

So this seems more difficult that I originally expected. I can get all users efficiently, all tasks within a date range, and all users with tasks within a given date range with the tasks included. But getting all users with the tasks nested within a given date range has evaded me.

What I've tried

User.filter(params.slice(:project_ids, :email, :id))
 .includes(:tasks).where('((tasks.end_date BETWEEN ? AND ?)
 OR (tasks.start_date BETWEEN ? AND ?))',
 week_start, week_end, week_start, week_end)
 .references(:tasks).order(:name)

I've also tried merging all users and all users with tasks within the range to no avail.

I feel like key is raw SQL but I couldn't wrap my head around the query yesterday.

Any insight would be helpful and appreciated.

What is the issue with that query? Is your goal "All Users" and just load the tasks in a given range? (Outer Join with condition) If so maybe just execute 2 queries (one for users and one for tasks in the date range) then just unify them in the UI. What rails version are you using as well as rails 5 offers `left_joins` as well as `or` syntax options? — engineersmnky, Nov 21 '18 at 17:57
The issue with the query is I only get the users with tasks within the date range, when my UI expects a list of all users. As for version, I am using rails 5. As for unifying them on the UI that's what I am currently trying. I thought it would prove difficult to line up the tasks with the correct user, when I first considered this route. — Dalemonaco, Nov 21 '18 at 18:36

score 1 · Answer 1 · answered Nov 22 '18 at 11:23

What you need is a left outer join. However, if you have a WHERE clause, this will filter away results for which the join fails, see Left Join With Where Clause.

You need to specify the condition as part of the JOIN clause, so something along these lines:

User.filter(params.slice(:project_ids, :email, :id))
    .joins("LEFT OUTER JOINS tasks ON tasks.user_id = users.id AND 
            tasks.end_date BETWEEN :week_start AND :week_end OR 
            tasks.start_date BETWEEN :week_start AND 
            :week_end", week_start: week_start, week_end: week_end
    .includes(:tasks).order(:name)

Rails - Has many, with query get all nested and parent object

1 Answers1