Find all records which have a count of an association greater than zero
1) To get Projects with at least 1 vacancy:
Project.joins(:vacancies).group('projects.id')
2) To get Projects with more than 1 vacancy:
Project.joins(:vacancies).group('projects.id').having('count(project_id) > 1')
3) Or, if Vacancy
model sets counter cache:
belongs_to :project, counter_cache: true
then this will work, too:
Project.where('vacancies_count > ?', 1)
Inflection rule for vacancy
may need to be specified manually?
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\""
Yeah, vacancies
is not a field in the join. I believe you want:
Project.joins(:vacancies).group("projects.id").having("count(vacancies.id)>0")