Rails iterated query to SQL statement

Question

I'm trying to convert ruby query to sql here is what I got so far:

sql2 = "SELECT teams.* FROM teams, (SELECT sum(goals) FROM players WHERE team_id = team_id);"
connection.execute(sql2)

and this is what I'm trying to write:

  Team Load (0.6ms)  SELECT "teams".* FROM "teams"
   (0.5ms)  SELECT SUM("players"."goals") FROM "players" WHERE "players"."team_id" = $1  [["team_id", 1]]
   (0.5ms)  SELECT SUM("players"."goals") FROM "players" WHERE "players"."team_id" = $1  [["team_id", 2]]

the above was created with : Team.all.map {|t| puts "#{t.name} and #{t.players.sum(:goals)}"}

a simple loop trough players, getting team name and summing team.players.goals

Ideas would be appreciated

SELECT sum(goals) FROM players GROUP BY team_id would do the trick. Or, if you want exactly what stupid AR does: SELECT sum(goals) FROM players WHERE team_id IN (SELECT id FROM teams). — Aleksei Matiushkin
– Aleksei Matiushkin, Commented May 20, 2015 at 16:52
@BSeven The snippet on how AR does it, posted by OP, is a perfect answer on “why not to use AR.” — Aleksei Matiushkin
– Aleksei Matiushkin, Commented May 20, 2015 at 16:53
@mudasobwa - What do you mean by "The snipped"? What exactly is wrong with AR? — B Seven
– B Seven, Commented May 20, 2015 at 16:55
@BSeven typo: snippet. Anyway, AR does “N+1 select” aka “N+1 query.” Google for that. — Aleksei Matiushkin
– Aleksei Matiushkin, Commented May 20, 2015 at 16:57

Aleksei Matiushkin · Accepted Answer · 2015-05-20 18:02:25Z

1

SELECT sum(goals) FROM players GROUP BY team_id

would do the trick. Or, if you want exactly what stupid AR does:

SELECT sum(goals) 
FROM players 
WHERE team_id IN (SELECT id FROM teams)

or, with a name of team to be returned as well:

SELECT teams.name, sum(players.goals) 
FROM players 
  JOIN teams ON (players.team_id = teams.id)
GROUP BY team_id

edited May 20, 2015 at 18:02

answered May 20, 2015 at 17:10

Aleksei Matiushkin

121k12 gold badges109 silver badges173 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Richardlonesteen Over a year ago

the name trows an error, and it would get players name. When I need the teams name, thats why I been selecting all teams. THANK you

Collectives™ on Stack Overflow

Rails iterated query to SQL statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related