Rails Find Record with Zero Has_Many Records Associated

Rails find record with zero has_many records associated

Bah, found it here: https://stackoverflow.com/a/5570221/417872

City.includes(:photos).where(photos: { city_id: nil })

Want to find records with no associated records in Rails

This is still pretty close to SQL, but it should get everyone with no friends in the first case:

Person.where('id NOT IN (SELECT DISTINCT(person_id) FROM friends)')

Rails - only find records where has_many associated records are present

Rails 4

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

or

Parent.joins(:child).distinct

Rails 3

Parent.joins(:child).distinct

Find records with no has_many :through record matching criteria

I've found an answer (with squeel) based on the following nested query:

SELECT "drafts"."id" FROM "drafts"
    WHERE "drafts"."id" NOT IN 
        (SELECT "drafters"."draft_id" FROM "drafters" 
            WHERE (("drafters"."draft_id" = "drafters"."id" 
                    AND "drafters"."user_id" = 2)))

That's convertible to the Rails+squeel query:

Draft.where{ id.not_in(Drafter.select(:draft_id).where{
        (draft_id == drafts.id) & (user_id == omit_user_id)})}

Find all records which have a count of an association of zero and none-zero

The reason is that in SQL a count of zero happens when there are no rows. So if there are no rows, even group, there is no result.

What you want is

Image.left_joins(:associated_images).where(associated_images: {id: nil}).group('images.id')

When SQL does a left join, for an image which does not have an associated image, it fills in NULL for all the columns in the associated_images table. So the ones where the associated_images.id is nil, are the ones we want.

Find all records which have a count of an association greater than zero

joins uses an inner join by default so using Project.joins(:vacancies) will in effect only return projects that have an associated vacancy.

UPDATE:

As pointed out by @mackskatz in the comment, without a group clause, the code above will return duplicate projects for projects with more than one vacancies. To remove the duplicates, use

Project.joins(:vacancies).group('projects.id')

UPDATE:

As pointed out by @Tolsee, you can also use distinct.

Project.joins(:vacancies).distinct

As an example

[10] pry(main)> Comment.distinct.pluck :article_id
=> [43, 34, 45, 55, 17, 19, 1, 3, 4, 18, 44, 5, 13, 22, 16, 6, 53]
[11] pry(main)> _.size
=> 17
[12] pry(main)> Article.joins(:comments).size
=> 45
[13] pry(main)> Article.joins(:comments).distinct.size
=> 17
[14] pry(main)> Article.joins(:comments).distinct.to_sql
=> "SELECT DISTINCT \"articles\".* FROM \"articles\" INNER JOIN \"comments\" ON \"comments\".\"article_id\" = \"articles\".\"id\""

Rails: get parent records having ZERO has_many associations

Maybe something like: User.includes(:tickets).where(tickets: {user_id: nil})

in a scope it would be like :without_tickets, -> { includes(:tickets).where(tickets: {user_id: nil}) }

Finding all records that has at least one association from associated models (tables)

I'd use such query:

Tree.left_joins(:fruits, :flowers).where('fruits.id IS NOT NULL OR flowers.id IS NOT NULL').distinct

it will produce this SQL:

SELECT DISTINCT "trees".* FROM "trees" LEFT OUTER JOIN "fruits" ON "fruits"."tree_id" = "trees"."id" LEFT OUTER JOIN "flowers" ON "flowers"."tree_id" = "trees"."id" WHERE (fruits.id IS NOT NULL OR flowers.id IS NOT NULL)

ActiveRecord - find records that its association count is 0

You're using joins, which is INNER JOIN, whereas what you need is an OUTER JOIN -
includes:

SlideGroup.includes(:surveys).group("slide_groups.id, surveys.id").having("count(surveys.id) = ?",0)

A bit cleaner query:

SlideGroup.includes(:surveys).where(surveys: { id: nil })

Rails - Find records with only one specific associated has_many or has_and_belongs_to_many record

User.includes(:roles).where(roles: {name: 'guest'}).having("COUNT(roles.id)=1")

I also would recommend to use joinsinstead of includes if you don't need to eager load the query.

---

Related Topics