rails joins query over has_many objects independently

Question

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 }

fool-dev · Accepted Answer · 2018-03-25T08:56:42.270

1

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.

edited Mar 25 '18 at 08:56

answered Mar 25 '18 at 07:01

fool-dev

7,671
9
40
54

Thanks for answer. The problem is scope :waiting. active one works just fine. – Astro Lee Mar 25 '18 at 07:03
@AstroLee see the ***Update*** – fool-dev Mar 25 '18 at 07:10
Thanks. Unfortunately updated query also returns some projects that is active && waiting some todos at the same time. I need to achieve projects that does not have any active todos at the moment And waiting for todos coming up. – Astro Lee Mar 25 '18 at 07:20
@AstroLee try `scope :waiting, -> {joins(:todos).where("todos.starts_at >= ?", Time.now).distinct}` it should work see ***Update 2*** – fool-dev Mar 25 '18 at 07:42
query works awesomely however also returning projects that does have progressing todo as well. I only wanna achieve projects that has 'some todos awaiting' and at the same time 'no todos currently progressing'. Thank you so much – Astro Lee Mar 25 '18 at 08:07
What do you mean by `no todos currently progressing` I mean what's the logic, value or consept – fool-dev Mar 25 '18 at 08:10
Sorry for the confuse. I meant. Project A has [1 todo started yesterday and ends tmrw, 1 todo starts next monday and ends next thursday ]. Project B has only [1 todo starts next monday and ends next thursday]. I am trying to find projects like B but querying returns A also. – Astro Lee Mar 25 '18 at 08:24
I added some #Update. please refer. – Astro Lee Mar 25 '18 at 08:30
Hey @AstroLee, I think you achieved your initial goal, I mean which for you have posted this question e.g `waiting` scope, I have written***Update 3 after Update 2 worked*** see this if it doesn't work then you need to ask a new question for only this otherwise it little be confusing, what actually problem. I think you get it :) – fool-dev Mar 25 '18 at 09:00
It's ok, that's why I love this site so much, everyday learning new things, you are welcome :) – fool-dev Mar 28 '18 at 12:28

minh · Answer 2 · 2018-03-25T08:13:12.853

1

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.

edited Mar 25 '18 at 08:13

answered Mar 25 '18 at 07:11

minh

161
1
5

Thanks a lot. You are right. I am getting a lot more projects than I wanted. I am still confused how I should achieve my goal. I wanna get projects that does not have any ongoing todos which are also not over yet while still have coming up todos. – Astro Lee Mar 25 '18 at 07:19
+ sql injection tip helped a lot. You really save me. I never knew such thing. Thank you so much. – Astro Lee Mar 25 '18 at 07:30
so you just want projects that have todos that are all `end < now` or projects that have no Todos? – minh Mar 25 '18 at 07:52
I wanna get projects, that has 'some todos awaiting' and at the same time 'no todos currently progressing' – Astro Lee Mar 25 '18 at 08:06
then you can do `where(joins(:todos).where("(todos.starts_at >= ? OR todos.ends_at <= ?) AND finishing > ?", Time.now, Time.now, Time.now)`. I believe this is the only way, and unfortunately yes, you have to duplicate the code for `active` scope. – minh Mar 25 '18 at 09:36

rails joins query over has_many objects independently

2 Answers2