sql - How to optimise this ActiveRecord query? -

i'm still learning ruby, rails , activerecord everyday. right i'm learning sql through new small app i'm building problem main view of app ~2000 queries per page refresh, oouuuppps.

so know have required information in db , can display them correctly, time me optimise them don't know start honest.

these models associations

class league < activerecord::base   belongs_to :user   has_many :league_teams   has_many :teams, :through => :league_teams end   class team < activerecord::base   has_many :gameweeks   has_many :league_teams   has_many :leagues, :through => :league_teams end   class gameweek < activerecord::base   belongs_to :team   has_and_belongs_to_many :players   has_and_belongs_to_many :substitutes, class_name: "player", join_table: "gameweeks_substitutes"   belongs_to :captain, class_name: "player"   belongs_to :vice_captain, class_name: "player" end   class player < activerecord::base   serialize :event_explain   serialize :fixtures   serialize :fixture_history   has_many :gameweeks, class_name: "captain"   has_many :gameweeks, class_name: "vice_captain"   has_and_belongs_to_many :gameweeks   has_many :player_fixtures end 

so controller:

@league = league.includes(teams: [{gameweeks: [{players: :player_fixtures} , :captain]}]).find_by(fpl_id:params[:fpl_id]) @teams = @league.teams @defense_widget_leaderboard = @league.position_based_leaderboard_stats(@teams, ['defender', 'goalkeeper']) 

and 1 of method in league model:

  def position_based_leaderboard_stats(teams,positions_array)     leaderboard = []      teams.each |team|       position_points = 0       gameweeks = team.gameweeks        gameweeks.each |gameweek|         defense = gameweek.players.where(type_name:positions_array)          defense.each |player|           player.player_fixtures.where(gw_number: gameweek.number).each |p|             position_points += p.points           end         end       end       leaderboard << [team.team_name,position_points]     end     return leaderboard.sort_by {|team| team[1]}.reverse   end 

i have 4 methods more or less same thing 1 above. each doing between 300 , 600 queries.

as far read only, typical case of n+1 queries. tried reduce includes in @league got me down 2000 1800 queries.

i looked group_by, joins , sum couldn't make work.

the closest thing got working this

players = playerfixture.group("player_id").sum(:points) 

where query doing players[player.id] doesn't give me right results anyway because doesn't take account gameweeks > players > player_fixtures relationship.

how can reduce numbers of queries i'm doing? went on #rubyonrails on freenode , people told me can done in 1 query wouldn't point me in directions or me...

thanks

in position_based_leaderboard_stats n+1 problem appears, too. can preload associations before each cycles:

def position_based_leaderboard_stats(teams,positions_array)   leaderboard = []   team.preload(gameweeks: players).where('players.type_name=?', positions_array )   code  

also, add player_fixtures preload statement, can't understand dependencies of associations, sorry.