JPHorta JPHorta - 3 months ago 7
SQL Question

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

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

Answer

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