Find all records that have a counter greater than zero

Question

I'm trying to do something that, in my opinion, would be simple, but it seems that it is not.

I have a project model that has many vacancies.

class Project < ActiveRecord::Base has_many :vacancies, :dependent => :destroy end 

I want to get all projects that have at least 1 vacancy. I tried something like this:

 Project.joins(:vacancies).where('count(vacancies) > 0') 

but he 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) .

Answer 1

joins uses an internal join by default, so using Project.joins(:vacancies) will actually return projects that have a related vacancy.

UPDATE:

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

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

Answer 2

1) To receive projects with at least 1 vacancy:

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

2) To receive projects with more than 1 vacancy:

 Project.joins(:vacancies).group('projects.id').having('count(project_id) > 1') 

3) Or, if the Vacancy model sets a cache counter:

 belongs_to :project, counter_cache: true 

then this will work too:

 Project.where('vacancies_count > ?', 1) 

An exception rule for Vacancy might need to be specified manually ?

Answer 3

Yes, vacancies not a field in a join. I believe you want:

 Project.joins(:vacancies).group("projects.id").having("count(vacancies.id)>0") 

Answer 4

 # None Project.joins(:vacancies).group('projects.id').having('count(vacancies) = 0') # Any Project.joins(:vacancies).group('projects.id').having('count(vacancies) > 0') # One Project.joins(:vacancies).group('projects.id').having('count(vacancies) = 1') # More than 1 Project.joins(:vacancies).group('projects.id').having('count(vacancies) > 1') 

Answer 5

The error tells you that jobs are not a column of projects, basically.

This should work

 Project.joins(:vacancies).where('COUNT(vacancies.project_id) > 0')