CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
rorykoehler
rorykoehler

Reputation: 1702

How to select parents based on all associated children passing certain criteria in Postgresql

I have a parent class that has many children. The children hold important time information in a date_time column. How do I select a parent record only if the children all pass the required criteria which I am defining as min(children.start_time) > ? AND min(children.start_time) < ?, Time.now, Time.now.end_of_month + 2.month

I have tried a number of solutions which have all given me back incorrect data. An example of what I have tried:

Parent.where(is_active: true).joins("LEFT JOIN children ON parents.id = children.parent_id").group("parents.id").group("children.start_time").having("min(children.start_time) > ? AND min(children.end_time) < ?", Time.now, Time.now.end_of_month + 2.month)

This query gives me back parents where the children has start dates before Time.now which is not what I am after.

Any pointers would be greatly appreciated.

Upvotes: 0

Views: 243

Answers (2)

davidwessman
davidwessman

Reputation: 1228

This is a great time to use a merge method.

To start with - create a scope in your children class:

scope(:near_start, -> { where('min(start_time) > ? AND min(start_time) < ?, Time.zone.now, 2.months.from_now) })

(Should it really be min on both criterions?

Parent.joins(:children).merge(Child.near_start)

I do not have any good example to test on right now, so I am not 100% sure what you need to assure that it checks all children, I guess that it can be done with group and/or having.

I hope that it can guide you a bit.

Upvotes: 0

rorykoehler
rorykoehler

Reputation: 1702

The answer is:

Parent.where(is_active: true).joins(:children).group("parents.id").having("min(children.start_time) > ? AND min(children.end_time) < ?", Time.now, Time.now.end_of_month + 2.month)

Upvotes: 1

Related Questions