On Aug 12, 2010, at 5:02 PM, jeroen wrote:
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/
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
Well, 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
[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.
