1

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?

stevo999999
  • 588
  • 4
  • 12

2 Answers2

2

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 :)

Community
  • 1
  • 1
SRack
  • 11,495
  • 5
  • 47
  • 60
  • @engineersmnky if there are no child records at all I'd still like to see the parent record. How do I do that? – stevo999999 Sep 25 '19 at 20:46
  • @stevo999999 - updated to do that using `left_outer_joins` :) – SRack Sep 26 '19 at 07:33
  • 1
    @SRack an outer join with a condition on the join table will create an inner join Also if I read the question correctly this is still incorrect as parents with children created outside of the 30 day window will be present in the result – engineersmnky Sep 26 '19 at 20:12
2

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)
Haytham.Breaka
  • 155
  • 12
  • This looks a convoluted (and perhaps inefficient) way of doing something built into Rails. Are there any advantages to this approach @Haytham.Breaka? What is returned if you call `to_sql` on this? Keen to see the performance implications. – SRack Sep 26 '19 at 07:44
  • 1
    @SRack Not that convoluted you can break it into two queries to be easier to understand like: 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) – Haytham.Breaka Sep 26 '19 at 08:14
  • 1
    @SRack Unfortunately, I can't leave you a comment on your answer but actually it's not right even after adding left_outer_joins. Because if a parent have children created before 30 days and other children created within 30 days your query will fetch this parent. And I don't think that's what the author wants. He wants only the parents who don't have any children created within 30 days. The only solution for this query is to do inner queries or break it on multiple queries – Haytham.Breaka Sep 26 '19 at 08:23
  • I disagree - mine selects parents who don't have a child created within the last 30 days. It's that simple. Your first query is doing the same as mine - you're just reserving the `not` for the second query. – SRack Sep 26 '19 at 08:42
  • 2
    @SRack not correct. I don't know how to explain it to you. I will do my best. I will give you an example with an invalid parent which your query will fetch it. imagine a parent who has 2 children. child_1 is created within 30 days and child_2 is not created within the 30 days. when you do the left_join it will not join this parent with the child_1 because child_1 is created within the 30 days. However, it will join the parent with child_2 because child_2. And it will return this parent as a valid parent which is not correct. hope I explain it well. – Haytham.Breaka Sep 26 '19 at 08:51
  • Thanks for the discussion @Haytham.Breaka, I'm enjoying running through this :) That's a nice example, though I still not sure I agree. The SQL generated appears to show this: `SELECT 'parents'.* FROM 'parents' LEFT OUTER JOIN 'children' ON 'children'.'parent_id' = 'parents'.'id' WHERE (NOT ('children'.'created_at' BETWEEN '2019-06-26 08:59:42' AND '2019-09-26'))`. So there needs to NOT be a child created between those dates, which I believe is what the OP is asking. – SRack Sep 26 '19 at 09:04
  • 1
    Please try my example first and come back to me if I am wrong. (That's how JOIN works) – Haytham.Breaka Sep 26 '19 at 09:07
  • 2
    Don't `pluck` the ids `select` them instead. Using `pluck` will create an `Array` where as using `select` will create a subquery e.g. `Parent.joins(:child).where(child: { created_at: 30.days.ago...Time.current }).select(:id).distinct`. Now when you call `Parent.where.not(id: unwanted_parents_ids)` it will generate a where clause of `id NOT IN ( SELECT parents.id FROM --...)` rather than `id NOT IN (1,2,3,4 --...)` – engineersmnky Sep 26 '19 at 20:15