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 On Wed, Jul 6, 2011 at 9:12 AM, Dmytrii Nagirniak <[email protected]> wrote: > Sometimes it feels much easier and better to just use good old SQL :) > > > On 5 July 2011 19:53, Chris Berkhout <[email protected]> wrote: >> >> 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 >> >> >> On Tue, Jul 5, 2011 at 5:15 PM, Mike Bailey <[email protected]> wrote: >> > 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 >> > >> > -- >> > 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 [email protected]. >> > To unsubscribe from this group, send email to >> > [email protected]. >> > For more options, visit this group at >> > http://groups.google.com/group/rails-oceania?hl=en. >> > >> >> -- >> 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 [email protected]. >> To unsubscribe from this group, send email to >> [email protected]. >> For more options, visit this group at >> http://groups.google.com/group/rails-oceania?hl=en. >> > > -- > 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 [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/rails-oceania?hl=en. > -- 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 [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/rails-oceania?hl=en.
