# convert complex SQL to ActiveRecord and Arel

**URL:** <https://discuss.rubyonrails.org/t/convert-complex-sql-to-activerecord-and-arel/53553>\
**Category:** rubyonrails-talk\
**Created:** [August 10, 2010, 10:15pm UTC](https://discuss.rubyonrails.org/t/convert-complex-sql-to-activerecord-and-arel/53553 "2010-08-10T22:15:05Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![jeroen2](https://avatars.discourse-cdn.com/v4/letter/j/5e9695/32.png) [@jeroen2](https://discuss.rubyonrails.org/u/jeroen2)\
**Post date:** [August 10, 2010, 10:15pm UTC](https://discuss.rubyonrails.org/t/convert-complex-sql-to-activerecord-and-arel/53553/1 "2010-08-10T22:15:05Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![rab](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/rab/32/710_2.png) [@rab](https://discuss.rubyonrails.org/u/rab)\
**Post date:** [August 10, 2010, 11:48pm UTC](https://discuss.rubyonrails.org/t/convert-complex-sql-to-activerecord-and-arel/53553/2 "2010-08-10T23:48:21Z")

</div>

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: &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.joins([:club, { :rankings =\> :tournament }])

And then an outer join (this is a tricky one and depends on how you've defined the associations): &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.includes(:matches)

Looks like you already know the club and the tournament: &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.where(['clubs.id = ? AND tournaments.id = ?', 7, 19])

And you want the count() function to behave: &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.group('players.id')

Then ask for all of 'em: &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.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/](http://AgileConsultingLLC.com/) rab@GaslightSoftware.com [http://GaslightSoftware.com/](http://GaslightSoftware.com/)

---

<div class="post-metadata">

**Author:** ![jeroen2](https://avatars.discourse-cdn.com/v4/letter/j/5e9695/32.png) [@jeroen2](https://discuss.rubyonrails.org/u/jeroen2)\
**Post date:** [August 12, 2010, 9:02pm UTC](https://discuss.rubyonrails.org/t/convert-complex-sql-to-activerecord-and-arel/53553/3 "2010-08-12T21:02:03Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![rab](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/rab/32/710_2.png) [@rab](https://discuss.rubyonrails.org/u/rab)\
**Post date:** [August 16, 2010, 12:55pm UTC](https://discuss.rubyonrails.org/t/convert-complex-sql-to-activerecord-and-arel/53553/4 "2010-08-16T12:55:15Z")

</div>

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 &nbsp;&nbsp;&nbsp;id

Player &nbsp;&nbsp;&nbsp;id &nbsp;&nbsp;&nbsp;club\_id

Tournament &nbsp;&nbsp;&nbsp;id

Ranking &nbsp;&nbsp;&nbsp;id &nbsp;&nbsp;&nbsp;player\_id &nbsp;&nbsp;&nbsp;tournament\_id

Match &nbsp;&nbsp;&nbsp;id &nbsp;&nbsp;&nbsp;tournament\_id

MatchPlayer &nbsp;&nbsp;&nbsp;match\_id &nbsp;&nbsp;&nbsp;player\_id &nbsp;&nbsp;&nbsp;position

And thus in AR: Player &nbsp;&nbsp;&nbsp;belongs\_to :club &nbsp;&nbsp;&nbsp;has\_many :match\_players &nbsp;&nbsp;&nbsp;has\_many :matches, :through =\> :match\_players Match &nbsp;&nbsp;&nbsp;belongs\_to :tournament

Player.select('count(matches.id) as match\_count, players.\*') &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.includes([:club, { :matches =\> :tournament }]) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.where(['clubs.id = ? AND tournaments.id = ?', 7, 19]) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.group('players.id') &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.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/](http://AgileConsultingLLC.com/) rab@GaslightSoftware.com [http://GaslightSoftware.com/](http://GaslightSoftware.com/)
