1

How can I get all parents that have no children or in case of children, if its 'state' differs from 'approved'?

Parent.includes(:children).where.not(children: { status: 'approved' })

"SELECT \"parents\".* FROM \"parents\" INNER JOIN \"children\" ON     "children\".\"parent_id\" = \"parents\".\"id\" WHERE \"children\".\"state\" = 'approved'"

It returns only parents with children with status different from approved but ignores parents that has no children

Parent.includes(:children).where(children: { id: nil })

 "SELECT \"parents\".\"id\" AS t0_r0, \"parents\".\"date\" AS t0_r1, ........, \"parents\".\"slug\" AS t0_r33, \"children\".\"id\" AS t1_r0, \"children\".\"parent_id\" AS t1_r1, \"children\".\"request_state\" AS t1_r8 FROM \"parents\" LEFT OUTER JOIN \"children\" ON \"children\".\"edition_id\" = \"parents\".\"id\" WHERE \"children\".\"id\" IS NULL"

It returns only parents with no children

Mauro Dias
  • 1,083
  • 1
  • 11
  • 23

1 Answers1

2

So you need to join the two conditions together.

Since includes only supports hash like condition in where clause, so we need to use references to write the condition in SQL source

Using where like this will only work when you pass it a Hash. For SQL-fragments you need use references to force joined tables

Parent.includes(:children).where("children.status <> 'approved' OR children.id IS NULL").references(:children)

Give this a shot

lusketeer
  • 1,890
  • 1
  • 12
  • 29