CODEWITHSUNDEEP
sqlruby-on-railspostgresqlactiverecord
stevo999999
stevo999999

Reputation: 608

Find parent record with no child with certain param certain parameters

So I know how to find all parent records that don't have a child record. 

Parent.joins(:child).where(child: {id: nil})

However how do I find all parent records with no children created in the last 30 days. I tried the following and it didn't work

Parent.joins(:child).where(child: {id: nil, created_at: 30.days.ago...Time.current})
Parent.joins(:child).where(child: {created_at: 30.days.ago...Time.current).where(child: {id: nil})

Neither of them worked. Any ideas?

Upvotes: 1

Views: 147

Answers (2)

Haytham.Breaka
Haytham.Breaka

Reputation: 155

You will need inner query to do what you want. One way to do it:

Parent.where.not(id: Parent.joins(:child).where(child: { created_at: 30.days.ago...Time.current }).pluck(:id).uniq)

This will select all parents which don't have any child within 30 days. Hope this help.

EDIT:

it can be broken into two simple queries:

unwanted_parents_ids = Parent.joins(:child).where(child: { created_at: 30.days.ago...Time.current }).pluck(:id).uniq
wanted_parents = Parent.where.not(id: unwanted_parents_ids)

Upvotes: 2

SRack
SRack

Reputation: 12203

You should be able to use where.not for this:

Update: to get all records even if there are no children, use left_outer_joins:

# from: 
# Parent.joins(:child).where.not(child: { created_at: 30.days.ago...Time.current } )
# to:
Parent.left_outer_joins(:child).where.not(child: { created_at: 30.days.ago...Time.current } )

It's pretty self explanatory, drawing all records that dont't match the criteria.

To explain the difference between joins and left_outer_joins, I'll use a quote from another question as their explanation is perfect:

INNER JOIN: returns rows when there is a match in both tables.

LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table.

Hence you want the latter in order to include parent records with no children.

Hope it helps - let me know how you get on or if you have any questions :)

Upvotes: 2

Related Questions