JPHorta JPHorta - 2 months ago 5
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


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)


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