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

sql ruby-on-rails ruby-on-rails-3 activerecord
jphorta picture jphorta · Nov 25, 2013 · Viewed 63.8k times · Source

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

Arta picture Arta · Jan 5, 2015

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?

Related questions

ActiveRecord: List columns in table from console

I know that you can ask ActiveRecord to list tables in console using: ActiveRecord::Base.connection.tables Is there a command that would list the columns in a given table?

Read More
Rails: How to find_by a field containing a certain string

I have a model named Topic, that has a name as a field. So say I have a term I'm searching for, apple. If I do a Topic.find_by_name("apple") I get a record back with the name …

Read More
Rails joins through association

In Ruby on Rails, I want to find employers in the city. Lets say the models are set up this way: City has_many :suburbs has_many :households, :through => suburbs has_many :people, :through => suburbs Suburb has_many :…

Read More