Hi, I have a fairly complex SQL statement I''d like to convert to AREL SELECT count(matches.id), players.* FROM clubs INNER JOIN players ON players.club_id = clubs.id INNER JOIN rankings ON rankings.player_id = players.id INNER JOIN tournaments ON rankings.tournament_id = tournaments.id LEFT OUTER JOIN matches ON (matches.p1_id = players.id OR matches.p2_id = players.id) AND clubs.id = 7 AND tournaments.id = 19 GROUP BY players.id How would I do this? -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Aug 10, 2010, at 6:15 PM, jeroen wrote:> Hi, > > I have a fairly complex SQL statement I''d like to convert to AREL > > SELECT count(matches.id), players.* > FROM clubs > INNER JOIN players ON players.club_id = clubs.id > INNER JOIN rankings ON rankings.player_id = players.id > INNER JOIN tournaments ON rankings.tournament_id = tournaments.id > LEFT OUTER JOIN matches ON (matches.p1_id = players.id OR > matches.p2_id = players.id) > AND clubs.id = 7 > AND tournaments.id = 19 > GROUP BY players.id > > How would I do this?One step at a time ... Even though you have the query Club-centric, you''re asking for players.* so I''d start with the Player model: Player.select(''count(matches.id) as match_count, players.*'') Then you have inner joins: .joins([:club, { :rankings => :tournament }]) And then an outer join (this is a tricky one and depends on how you''ve defined the associations): .includes(:matches) Looks like you already know the club and the tournament: .where([''clubs.id = ? AND tournaments.id = ?'', 7, 19]) And you want the count() function to behave: .group(''players.id'') Then ask for all of ''em: .all That may not actually work, but it certainly ought to give you some hints. (And there''s probably other ways to get the same information, but you need to ask a better question to get a better answer.) -Rob Rob Biedenharn Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org http://AgileConsultingLLC.com/ rab-/VpnD74mH8+00s0LW7PaslaTQe2KTcn/@public.gmane.org http://GaslightSoftware.com/ -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Hi Rob, I really appreciate your feedback. One of the things I;m struggling with is to fetch a player''s matches with Arel: irb) Player.joins(:matches) ActiveRecord::ConfigurationError: Association named ''matches'' was not found; perhaps you misspelled it? A match does not have a player_id it does have p1_id and p2_id. Hence the "JOIN matches ON (matches.p1_id = players.id OR matches.p2_id = players.id)" in SQL I''m already struggling to get a Player#has_many :matches going. I can do it the old fashioned way using finder_sql but that doesn''t use Arel so it hard to chain more things to it. I dont know how to specify the join columns in in a Player.joins(...) call. Sorry if my questions aren''t clear, I just don''t have a clue where to start, Arel docs & examples on this topic seem hard to find. Cheers, Jeroen On Aug 11, 1:48 am, Rob Biedenharn <R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org> wrote:> On Aug 10, 2010, at 6:15 PM, jeroen wrote: > > > > > Hi, > > > I have a fairly complex SQL statement I''d like to convert toAREL > > > SELECT count(matches.id), players.* > > FROM clubs > > INNER JOIN players ON players.club_id = clubs.id > > INNER JOIN rankings ON rankings.player_id = players.id > > INNER JOIN tournaments ON rankings.tournament_id = tournaments.id > > LEFT OUTER JOIN matches ON (matches.p1_id = players.id OR > > matches.p2_id = players.id) > > AND clubs.id = 7 > > AND tournaments.id = 19 > > GROUP BY players.id > > > How would I do this? > > One step at a time ... > > Even though you have the query Club-centric, you''re asking for > players.* so I''d start with the Player model: > > Player.select(''count(matches.id) as match_count, players.*'') > > Then you have inner joins: > .joins([:club, { :rankings => :tournament }]) > > And then an outer join (this is a tricky one and depends on how you''ve > defined the associations): > .includes(:matches) > > Looks like you already know the club and the tournament: > .where([''clubs.id = ? AND tournaments.id = ?'', 7, 19]) > > And you want the count() function to behave: > .group(''players.id'') > > Then ask for all of ''em: > .all > > That may not actually work, but it certainly ought to give you some > hints. (And there''s probably other ways to get the same information, > but you need to ask a better question to get a better answer.) > > -Rob > > Rob Biedenharn > R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org http://AgileConsultingLLC.com/ > r...-/VpnD74mH8+00s0LW7PaslaTQe2KTcn/@public.gmane.org http://GaslightSoftware.com/-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Aug 12, 2010, at 5:02 PM, jeroen wrote:> On Aug 11, 1:48 am, Rob Biedenharn <R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org> > wrote: >> On Aug 10, 2010, at 6:15 PM, jeroen wrote: >> >>> Hi, >> >>> I have a fairly complex SQL statement I''d like to convert toAREL >> >>> SELECT count(matches.id), players.* >>> FROM clubs >>> INNER JOIN players ON players.club_id = clubs.id >>> INNER JOIN rankings ON rankings.player_id = players.id >>> INNER JOIN tournaments ON rankings.tournament_id = tournaments.id >>> LEFT OUTER JOIN matches ON (matches.p1_id = players.id OR >>> matches.p2_id = players.id) >>> AND clubs.id = 7 >>> AND tournaments.id = 19 >>> GROUP BY players.id >> >>> How would I do this? >> >> One step at a time ... >> >> Even though you have the query Club-centric, you''re asking for >> players.* so I''d start with the Player model: >> >> Player.select(''count(matches.id) as match_count, players.*'') >> >> Then you have inner joins: >> .joins([:club, { :rankings => :tournament }]) >> >> And then an outer join (this is a tricky one and depends on how >> you''ve >> defined the associations): >> .includes(:matches) >> >> Looks like you already know the club and the tournament: >> .where([''clubs.id = ? AND tournaments.id = ?'', 7, 19]) >> >> And you want the count() function to behave: >> .group(''players.id'') >> >> Then ask for all of ''em: >> .all >> >> That may not actually work, but it certainly ought to give you some >> hints. (And there''s probably other ways to get the same information, >> but you need to ask a better question to get a better answer.) >> >> -Rob >> >> Rob Biedenharn >> R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org http://AgileConsultingLLC.com/ >> r...-/VpnD74mH8+00s0LW7PaslaTQe2KTcn/@public.gmane.org http://GaslightSoftware.com/ > Hi Rob, > > I really appreciate your feedback. > > One of the things I;m struggling with is to fetch a player''s matches > with Arel: > > irb) Player.joins(:matches) > ActiveRecord::ConfigurationError: Association named ''matches'' was not > found; perhaps you misspelled it? > > A match does not have a player_id it does have p1_id and p2_id. > Hence the "JOIN matches ON (matches.p1_id = players.id OR > matches.p2_id = players.id)" in SQL > > I''m already struggling to get a Player#has_many :matches going. I can > do it the old fashioned way using finder_sql but that doesn''t use Arel > so it hard to chain more things to it. > > I dont know how to specify the join columns in in a Player.joins(...) > call. > > Sorry if my questions aren''t clear, I just don''t have a clue where to > start, Arel docs & examples on this topic seem hard to find. > > Cheers, > > JeroenWell, you''re discovering that SQL is hard for relations that aren''t symmetric (i.e., your p1_id and p2_id). If you add a MatchPlayer model, you can probably simplify your SQL and if you keep a ''position'' you can still maintain which is "Player 1" and which is "Player 2" in a match. The bare minimum is probably something like: Club id Player id club_id Tournament id Ranking id player_id tournament_id Match id tournament_id MatchPlayer match_id player_id position And thus in AR: Player belongs_to :club has_many :match_players has_many :matches, :through => :match_players Match belongs_to :tournament Player.select(''count(matches.id) as match_count, players.*'') .includes([:club, { :matches => :tournament }]) .where([''clubs.id = ? AND tournaments.id = ?'', 7, 19]) .group(''players.id'') .all I don''t think that this query needs Ranking at all (although it was probably used to create the Match and MatchPlayer records). -Rob Rob Biedenharn Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org http://AgileConsultingLLC.com/ rab-/VpnD74mH8+00s0LW7PaslaTQe2KTcn/@public.gmane.org http://GaslightSoftware.com/ -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.