count() and group() in a rails 3 query, or just use sql?

What's the Rails 3 chained-method way to do this kind of query?

@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 came up with:

@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(@e开发者_如何转开发mployer.id)

Can I tighten this up even more? Can I clean up the order() call, and should I be using count() somewhere? Should I bother? Thanks.

In the order clause you don't need to specify the table unless the column name is ambiguous. You can probably just do

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

Also, I think you meant to put

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

Other than that I think you things are fine. I don't think .count will help you for this case.