Can I use a condition with counting the has_many relationship?

I have a list of projects and only want to display projects that have tasks. Is it possible to use a counting condition on the has_many relation?

# get my project list

class Project < ActiveRecord::Base
  has_many :tasks

class Task < ActiveRecord::Base
  belongs_to :project


I'm currently doing this through a loop, but I don't think this is the right way.


source to share

3 answers

Since you are already loading tasks for a project, you can use the following statement to get projects with tasks.

# get my project list
Project.includes(:tasks).where(" IS NOT NULL")


It works because it includes



On the other hand, if you don't want to load tasks, you can use joins

as it uses INNER JOIN.





The directive includes

often points to simply loading those associations rather than JOIN

theirs across the database, so you can't make conditions here without additional work.

One way that scales well is using a function counter_cache

to associate, so you always have a numerical count of the number of tasks. You can even add an index to improve the performance of your query.

An alternative is to try to get back to work from the table tasks

, perhaps for example:

Project.where('id IN (SELECT DISTINCT project_id FROM tasks)')


Presumably you have an index on project_id

your table tasks

to make it fairly inexpensive.



If the question is as simple as the title suggests, not sure why this won't do the trick:



Unless otherwise noted, the join will be an internal join and thus exclude any results whose projects have no tasks, so maybe all you need ... if you want to display all projects with tasks.

If you have some condition (for example, projects whose status is active), you can also specify a condition like

Project.joins(:tasks).where("status = 'active')


Or am I missing something?



All Articles