convert complex SQL to ActiveRecord and Arel

rubyonrails-talk
1

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?

2

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@AgileConsultingLLC.com http://AgileConsultingLLC.com/ rab@GaslightSoftware.com http://GaslightSoftware.com/

3

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

4

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 Rob@AgileConsultingLLC.com http://AgileConsultingLLC.com/ rab@GaslightSoftware.com http://GaslightSoftware.com/