Count () and group () in rails 3 query or just use sql?

Question

What is a Rails 3 method with a chain method to execute such a request?

@jobs_by_location = Employer.find_by_sql ['SELECT count(j.id) AS job_count, e.* FROM employers e, jobs j' + ' WHERE e.parent_id = ? AND j.employer_id = e.id' + ' AND j.status = 2' + ' GROUP BY e.id' + ' ORDER BY e.state_id, e.city, e.name ASC', @employer.id] 

I figured it out:

 @jobs_by_location = Employer .select('employers.*, count(jobs.id) as job_count').joins(:jobs) .group('employers.id').order('employers.state_id,employers.city,employers.name ASC') .where(:jobs => {:status => 2}).where(@employer.id) 

Can I tighten this even more? Can I clear the order () call, and should I use count () somewhere? Should I worry? Thanks.

Answer 1

In the order clause, you do not need to specify a table if the column name is not ambiguous. Maybe you can just do

 .order('state_id, city, name ASC') 

Also, I think you wanted to put

  .where(:parent_id => @employer.id) # instead of .where(@employer.id) 

In addition, I think that everything is in order. I do not think .count will help you in this case.