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
Project.includes(:tasks).where(...)
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
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("tasks.id IS NOT NULL")
It works because it includes
uses LEFT OUTER JOIN
.
On the other hand, if you don't want to load tasks, you can use joins
as it uses INNER JOIN.
Project.joins(:tasks).where(...)
source to share
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.
source to share
If the question is as simple as the title suggests, not sure why this won't do the trick:
Project.joins(:tasks)
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?
source to share