CODEWITHSUNDEEP
ruby-on-railsactiverecord
Astro Lee
Astro Lee

Reputation: 421

rails joins query over has_many objects independently

I have parent model Project & children model ToDo.

A project has many ToDos with column starts_at & ends_at.

I would like to search for projects that have 'any' todos in the time range.

Here I wrote some codes, however, it is not as expected.

class Project
   has_many :todos

   scope :active, -> {joins(:todos).where("todos.starts_at < '#{Time.now}' AND todos.ends_at > '#{Time.now}'").distinct}
   scope :waiting, -> {joins(:todos).where.not("todos.starts_at < '#{Time.now}' AND todos.ends_at > '#{Time.now}'").distinct}
   scope :done, -> {where("project_due > ?", Time.now)}
end

Active one seems right, but waiting scope also contains the projects that have more than one todos.

I wonder if there any solution to compare starts_at & ends_at per each todo. Not like above.

Thanks ahead.

*# Update *

This is what I wanna achieve. but in one query.

scope :waiting, -> { joins(:todos).where.not(id: active.ids).where('finishing > ?', Time.now).distinct }

Upvotes: 1

Views: 64

Answers (2)

fool-dev
fool-dev

Reputation: 7777

Try to the following

Update

For waiting you mean starts_at greater than NOW right? then it will be

scope :waiting, -> {joins(:todos).where("todos.starts_at >= ?", Time.now).distinct}

If the match with the first condition then you don't need to match the second condition, you can write with the second condition like this 

scope :waiting, -> {joins(:todos).where("todos.starts_at > '#{Time.now}' AND todos.ends_at > '#{Time.now}'").distinct}

but don't need.

Update 2

Remove the not from where, here not means active

scope :waiting, -> {joins(:todos).where("todos.starts_at >= ?", Time.now).distinct}

Update 3 after Update 2 worked

scope :waiting, -> {joins(:todos).where("todos.starts_at >= ?", Time.now).distinct}
scope :finished, -> {where("finishing > ?", Time.now).distinct}
scope :waiting_n_progress, -> {where.not(id: active.ids).finished.waiting}

the waiting_n_progress scope, I think you will get your goal, remember that's not tested.

Hope it should work.

Upvotes: 1

minh
minh

Reputation: 181

Your query for waiting basically translate from !(start < now && end > now) to this start >= now || end <= now which would most likely return a lot more projects than you wanted. See if that is the logic that you wanted.

Also, for best Rails practices, you should write query like the user fool-dev suggested in the other answer using question mark. This is to prevent SQL injection as explained in more details here

EDIT So I think you mean waiting means projects that does not have any Todo or those that have awaiting Todo (start_at > now). I think this should work:

scope(:waiting) { includes(:todos).where('todos.project_id IS NULL OR todos.start_at > ?', Time.now).distinct }

The first part is to select projects that doesn't have any Todo and the second part is self explanatory.

Upvotes: 1

Related Questions