How to return results filtered on relations count to a view in RAILS?

Question

Basically, I defined a property on my model which returns true or false depending on values in another table.

What I want is to have my Index action in the controller to return only those results that meet this condition.

I tried this:

#What I've tried on my Controller:
def index
   @projects = Project.where(:is_available?)
end

#What I've on my Controller:
def index
   @projects = Project.all
end

#What I've on my Model:
def is_available?
   workers.count<2 ? true : false
end

Thanks.

Answer 1

Why your code doesn't work?

Project.where(:is_available?)

Here in the where method, you have to pass a hash of arguments OR a string of (SQL) conditions. What you are trying to do here is to select all projects where the method is_available? returns true. The problem is that the method is_available? is a Ruby method (defined in your model). Since it is a Ruby function, you can't call it inside SQL. The where method is expecting SQL conditions, not ruby code.

(Thanks to @benzado for the comment)

---

To fix your problem:

This is what you are looking for, computed only at the db level:

Project.joins(:workers)
       .select('projects.*')
       .group('projects.id')
       .having('COUNT(workers.*) > 2')

This should returns all project having at least 2 workers associated with.

---

How to improve this?

You can make a scope of this query, to use it everywhere easily:

#in your model
class Project < ActiveRecord::Base
  scope :having_more_than_x_workers, lambda do |workers_count|
    joins(:workers).select('projects.*').group('projects.id').having("COUNT(workers.*) > #{workers_count || 0}")
  end

end

To use it, in your controller for example:

#in your controller
def index
   @projects = Project.having_more_than_x_workers(2)
end

Answer 2

The Rails query methods (like where) work by creating a database query; in other words, you can't use an attribute in where unless it actually exists on your data model. Otherwise, the database doesn't know about it and can't perform the filtering for you.

In your case, you should define a method on the Project class which performs the "is available?" query, so you can use your method in place of where. You can do it like this:

class Project < ActiveRecord::Base
  def self.available_projects
     where('workers_count > 2')
  end
end

See MrYoshiji's answer for specifics on how to write the query or how to define it as a named scope.