Find All Records Which Have a Count of an Association Greater Than Zero

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\""

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.

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 - Active Record: Find all records which have a count on has_many association with certain attributes

One solution is to use rails nested queries

User.joins(:identities).where(id: Identity.select(:user_id).unconfirmed).group("users.id").having( 'count(user_id) = 1')

And here's the SQL generated by the query

SELECT "users".* FROM "users"
INNER JOIN "identities" ON "identities"."user_id" = "users"."id"
WHERE "users"."id" IN (SELECT "identities"."user_id" FROM "identities"  WHERE "identities"."confirmed" = 'f')
GROUP BY users.id HAVING count(user_id) = 1

I still don't think this is the most efficient way. While I'm able to generate only one SQL query (meaning only one network call to the db), I'm still have to do two scans: one scan on the USERS table and one scan on the IDENTITIES table. This can be optimized by indexing the identities.confirmed column but this still doesn't solve the two full scans problem.

For those who understand the query plan here it is:

     QUERY PLAN
-------------------------------------------------------------------------------------------
 HashAggregate  (cost=32.96..33.09 rows=10 width=3149)
   Filter: (count(identities.user_id) = 1)
   ->  Hash Semi Join  (cost=21.59..32.91 rows=10 width=3149)
         Hash Cond: (identities.user_id = identities_1.user_id)
         ->  Hash Join  (cost=10.45..21.61 rows=20 width=3149)
               Hash Cond: (identities.user_id = users.id)
               ->  Seq Scan on identities  (cost=0.00..10.70 rows=70 width=4)
               ->  Hash  (cost=10.20..10.20 rows=20 width=3145)
                     ->  Seq Scan on users  (cost=0.00..10.20 rows=20 width=3145)
         ->  Hash  (cost=10.70..10.70 rows=35 width=4)
               ->  Seq Scan on identities identities_1  (cost=0.00..10.70 rows=35 width=4)
                     Filter: (NOT confirmed)
(12 rows)

Rails 4 - query on association count is greater than an attribute

Use counter_cache!

http://guides.rubyonrails.org/association_basics.html 4.1.2.3 :counter_cache

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

Use Rails .where to return objects with an association count greater than 0

Somethling like:

Order.joins(:newspaper_placements).where(client_companies_media_company_id: all_cc_mc)

rails 3 getting the count of the records that have more than one associate records (has_many)

active record supports 'having' as a method.
So you could do your query this way:

Account.joins(:users).select('accounts.id').group('accounts.id').having('count(users.id) > 1')

Active Record Query to Get Count on Relationships

This should do the trick:

Order
  .joins(:toys)
  .group('orders.id')
  .having('count(toys.*) > ?', 10)

---

Related Topics