Find all records which have a count of an association greater than zero
activerecord, ruby-on-rails, ruby-on-rails-3, sql
Solution
`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\""
Problem
I'm trying to do something that I thought it would be simple but it seems not to be. I have a project model that has many vacancies. ``` class Project < ActiveRecord::Base has_many :vacancies, :dependent => :destroy end ``` I want to get all the projects that have at least 1 vacancy. I tried something like this: ``` Project.joins(:vacancies).where('count(vacancies) > 0') ``` but it says `SQLite3::SQLException: no such column: vacancies: SELECT "projects".* FROM "projects" INNER JOIN "vacancies" ON "vacancies"."project_id" = "projects"."id" WHERE ("projects"."deleted_at" IS NULL) AND (count(vacancies) > 0)`.