CODEWITHSUNDEEP
sqlruby-on-railsrubypostgresqlactiverecord
Nathan Lauer
Nathan Lauer

Reputation: 391

Rails Postgres: Query to find corresponding models in a "triangle of models"

I have models User, League, and Team. A user has_many leagues and has_many teams, a league has_many teams and has_many users, and a team belongs_to a league and belongs_to a user.

Given the current user, what I would like to do is find all leagues and teams belonging to that user, but pair them such that each team is paired with the league it belongs to. Ideally, I'd like to do this in as few queries as possible - the pairing is also important, since I'm going to display each pair as a row in a sidebar.

Here's my current attempt (which works):

def get_user_leagues_and_teams(user)
    @leagues_teams = Hash.new
    if (!user.teams.nil?)
        @teams = user.teams.all
    end
    #Creates hash of leagues and teams (assuming one to one)
    @teams.each do |team|
        league = team.league
        @leagues_teams[league] = team
    end
    @leagues_teams
end

The above works. However, I don't think it's very efficient. I look up all user teams, then one-by-one find each team's league, and store each pair in a hash. For a user with many leagues/teams, this would be far too many database calls. Further, this doesn't scale well if I want to associate other models with a user that need to be matched with a league/team (such as games, trades, etc.)

Is there some better query for this?

Thanks!

Upvotes: 1

Views: 97

Answers (1)

John Hayes-Reed
John Hayes-Reed

Reputation: 1428

Two points:
1. What you are trying to achieve is to just group the teams by their league, you don't need to reinvent the wheel, there is already a #group_by method which you can use to achieve this.
2. To stop looking up every league one-by-one, you can eager load them into memory with a single query using #includes.

so your resulting code would look something like this:

@leagues_teams = user.teams.includes(:leagues).group_by(&:league)

Much simpler and more efficient!

Upvotes: 3

Related Questions