Rails 3: Select all submodels where submodel has a certain condition

Question

My Rails db scheme has projects and task. I want to show projects which have at least one open task. This is my code:

class Project
    scope :open_tasks, lambda {
        where(:tasks => {:finished => false}).includes(:tasks)
    }
    ...
end

This code correctly returns the projects with one open task, but only with the one open tasks and not all. e.g. a project has 5 tasks total and 2 open task, the code from above would return only the project with 2 tasks. I know that I could simply force to reload the project, but this is very hackish and has performance problems. How can I get the project with all tasks?

Answer 1

The where condition is always going to limit which associated tasks are returned.

It sounds like you want to return the projects and all of their associated tasks regardless of status if at least one of those tasks is not finished?

You could try this (I don't think you need the lambda since you're not using any variables/times/etc. in your scope). It assumes you have a has_many :tasks and belongs_to :project. You'd need to tweak it if you're using a has_many :through, etc.

scope :open_tasks, where("(select count(*) from tasks where tasks.project_id=projects.id) > 0").includes(:tasks)