CODEWITHSUNDEEP
ruby-on-railsdatabasepostgresqlactiverecordrails-activerecord
dom
dom

Reputation: 509

Active Record: How to find a Parent based on an that there is no association with a given attribute

I have the following Database structure: 

Parent
 -> child 1
 -> child 2
 ->..

Now I want to find all the parents who don't have a child with a specific attribute set to true.

Example: 

Should not be found: 
Parent: 
 -> child 1 (selected: true)
 -> child 2 (selected: false)

Should be found
Parent: 
 -> child 1 (selected: nil)
 -> child 2 (selected: false)

Currently I have the following:

Project.all - Project.includes(:project_images).where(project_images: { selected: true })

This works fine but it seems to be somewhat inefficient. I believe there should be an easier and faster way.

Upvotes: 0

Views: 51

Answers (3)

army_coding
army_coding

Reputation: 154

Project.joins(:project_images).where("project_images.selected NOT IN (TRUE)");

Upvotes: 0

adam
adam

Reputation: 94

You can just use where.not to solve it.

see https://api.rubyonrails.org/classes/ActiveRecord/QueryMethods/WhereChain.html#method-i-not

Project.joins(:project_images).where.not(project_images: { selected: true })

Upvotes: 1

dileep nandanam
dileep nandanam

Reputation: 2885

    parents = Parent.joins(:children).where('children.selected = FALSE or children.selected is NULL')

This query selects rows from parents children inner join table with selected attribute not set to true.

If you want parent objects without even an asociated child you have to left outer join children.

Parent.joins('left join children on parents.id = childrenparent_id')..where('children.selected = FALSE or children.selected is NULL')

Upvotes: 0

Related Questions