CODEWITHSUNDEEP
ruby-on-railsrubyactiverecordleft-joininner-join
Benjamin
Benjamin

Reputation: 3950

Rails Active Record join query with sorting and limit

I’m trying to put together a reasonably complex Active Record query and I’m fairly new to Rails.

Relationships

Here are the relevant model relationships for the query:

Schema

Here are the relevant portions of schema.rb:

create_table "teams", force: :cascade do |t|
  t.integer "admin_id"
end

create_table "users", force: :cascade do |t|
  t.string "email", default: "", null: false
  t.integer "team_id"
end

add_foreign_key "teams", "users", column: "admin_id"
add_foreign_key "users", "teams"

Query

Given a domain name (e.g. example.com), I would like to find teams that have admins with an email address with that domain name. If there are multiple matches, I would like only the team with the most users. Here’s what I have so far:

team = Team.joins('INNER JOIN users ON admin_id = users.id')
           .where('email LIKE ?', "%example.com")
           .where(domain_sign_up: true)

Problem

The problem with this query is that it returns all matches as an array. I would like to order it by team.users.count, then return only the first one (with the most users) team as an object, not an array. Is this perhaps a candidate for a LEFT OUTER JOIN?

Thank you!

Upvotes: 2

Views: 1611

Answers (2)

Benjamin
Benjamin

Reputation: 3950

The answer posted by Ursus was quite useful, however I ended up adding a new column to team called domain to simplify things, then used .left_join to order by users. Here’s my final query:

team = Team.left_joins(:users)
           .where(domain: 'example.com', domain_sign_up: true)
           .group('teams.id')
           .order('COUNT(users.id) DESC')
           .first

Upvotes: 0

Ursus
Ursus

Reputation: 30071

Try something like this

team = Team.joins('INNER JOIN users ON admin_id = users.id')
           .where('email LIKE ?', "%example.com")
           .where(domain_sign_up: true)
           .group('teams.id')
           .select('teams.*, COUNT(users.id) AS team_users_count')
           .order('COUNT(users.id) DESC')
           .first

I changed limit(1) to first. It doesn't seem to have much sense limit if the parameter is 1, so you have a Team, not a relation of one team.

Upvotes: 5

Related Questions