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 <[email protected]> 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 > [email protected] http://AgileConsultingLLC.com/ > [email protected] 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 [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
