Self referential joins using Arel in Rails

[Mike Bailey]

I have a working SQL query I'm trying to convert to Arel. I'm trying to work out the syntax for a self referential join on a table.

I can't work out how to address the table as 's1' and 's2' so my query makes sense.

irb(main):018:0> s1.join(s2).on(s1[:serviceid].eq(s2[:serviceid]), s1[:id].gt(s2[:id])).where(s2[:serviceid].eq(nil)).to_sql
=> "SELECT FROM `service` INNER JOIN `service` ON `service`.`serviceid` = `service`.`serviceid` AND `service`.`id` > `service`.`id` WHERE `service`.`serviceid` IS NULL"

Has anyone done this?

- Mike

[Chris Berkhout]

Hey Mike,

You need to use the alias method and tell it what to call the alias.
So, you'll want something like this:

service_table = Arel::Table.new(:service)
s1 = service_table.alias("s1")
s2 = service_table.alias("s2")

Take a look at the inner query of this (relevant_contents_query):
https://gist.github.com/22966422d5a62b100d96

Cheers,
Chris

> --
> You received this message because you are subscribed to the Google Groups
> "Ruby or Rails Oceania" group.
> To post to this group, send email to rails-...@googlegroups.com.
> To unsubscribe from this group, send email to
> rails-oceani...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/rails-oceania?hl=en.
>

[Dmytrii Nagirniak]

Sometimes it feels much easier and better to just use good old SQL :)

[Chris Berkhout]

If I was rewriting that one again, I'd probably use Arel for the inner
query (where the number of joins is dependent on parameters), and SQL
for the outer query.

I wrote it mostly as an experiment, and found that I couldn't really
use the Arel code to debug the couple of issues that came up. I had to
keep going back to the SQL to get my head around what was going on.

Arel certainly has advantages, but I'd say there are some times when
the costs outweigh them.

Cheers,
Chris

[Mike Bailey]

I'm leaning what way myself. One problem is that Arel uses different terminology when you use it in rails.

• Service.select in rails = #project in 'native' Arel.
• Service.includes doesn't have an equivalent (that I could find) in 'native' Arel.

Part of the promise from Arel is lazy loading and the ability to chain scopes.

irb(main):088:0> b = Service.where(:id => 1).to_sql
=> "SELECT `service`.* FROM `service`  WHERE `service`.`id` = 1"

When we use an Arel::SelectManager object instead of an ActiveRecord::Relation then we need to remember to call Service.find_by_sql(arel_object.to_sql) to actually grab some AR objects.

I think I'll stick to 'rails arel' and attempt the things it doesn't offer using SQL when I don't need to dynamically build queries.

It's been interesting learning more about arel. If I've missed anything obvious please point it out.

- Mike