Thanks for the pointers. I've used some of the info in your specs to eke out a solution. I'm including it here for interests sake. I've then run into the problem of actually getting ActiveRecords from the Arel object and now just doing Record.find_by_sql(arel_object.to_sql) which quite frankly is horrible.
w = Winery.arel_table v = Visit.arel_table visit_count = v.group(v[:winery_id]).project(v[:winery_id], v[:winery_id].count.*as(:visit_count)*) q = w.join(visit_count).on(w[:id].eq(visit_count[:winery_id])).order(*"visit_count desc"*).take(5) Winery.find_by_sql(q.to_sql) I've emboldened the bits that seem to be the shortcomings of Arel as it stands right now. Having to force some aliases that it suggests should just work and then not being able to dynamically access them. Cheers, Michael On Tue, Sep 7, 2010 at 12:40 PM, Chris Darroch <[email protected]> wrote: > You'll also notice that your order by isn't being applied either. > I've used ARel a little on its own as well, and was equally perplexed by > some of its behaviors when composing queries such as this one. > I think I forced the query to return sensible things by explicitly > projecting the columns I cared about on the second query. You probably > already had an inkling you'd have to do this, judging by your bonus points > question. > ARel makes a lot of sense when it's under the hood of ActiveRecord, but it > is still very much a fledgling DSL. Using it manually like this promises > power, but sometimes it ends up feeling ickier than just writing a > straight-up SQL string. The true benefits of using ARel this way are only > seen when you can construct a base query that you'll re-use a lot, and add > specific filters to it elsewhere depending on some business logic > conditions. > That's my two cents, anyway. > P.S. I had trouble with ARel and projecting values from a query with > multiple joins. Might be fixed in 1.0, but just be aware of > it: http://github.com/chrisdarroch/arel_joins > On Tue, Sep 7, 2010 at 2:15 AM, Michael Gall <[email protected]> wrote: >> >> Hi Guys, >> >> I've been fighting with a couple of not really that complex queries in >> Rails 3 and it seems like Arel is the tool for the job, but having >> pulled most of my hair out I don't believe it. So hopefully I can get >> some inspiration from here. >> >> I'm basically trying to reproduce the type of query mentioned at the >> end of the Arel Readme. http://github.com/rails/arel >> >> w = Winery.arel_table >> v = Visit.arel_table >> >> visit_count = group(v[:winery_id]).project(v[:winery_id], >> v[:winery_id].count) >> >> w.join(visit_count).on(w[:id].eq(visit_count[:winery_id])).order(visit_count[:count]).take(5) >> >> and the resultant sql is: >> => "SELECT `wineries`.`id`, `wineries`.`name`, `wineries`.`slug`, >> `wineries`.`openinghours`, `wineries`.`website`, `wineries`.`address`, >> `wineries`.`lat`, `wineries`.`lng`, `wineries`.`phone`, >> `wineries`.`email`, `wineries`.`cellardoor`, `wineries`.`restaurant`, >> `wineries`.`accommodation`, `wineries`.`created_at`, >> `wineries`.`updated_at`, `wineries`.`region_id`, `wineries`.`flickr`, >> `wineries`.`twitter`, `wineries`.`blog`, `wineries`.`subregion_id`, >> `wineries`.`about`, `wineries`.`twitter_rss`, `wineries`.`town`, >> `wineries`.`tags`, `wineries`.`version`, `wineries`.`logo_file_name`, >> `wineries`.`logo_content_type`, `wineries`.`logo_file_size`, >> `wineries`.`logo_updated_at`, `wineries_external`.`winery_id`, >> `wineries_external`.`` FROM `wineries` INNER JOIN (SELECT >> `wineries`.`winery_id`, COUNT(`wineries`.`winery_id`) AS count_id FROM >> `wineries` GROUP BY `wineries`.`winery_id`) >> `wineries_external` ON `wineries`.`id` = >> `wineries_external`.`winery_id` LIMIT 5" >> >> It generally looks ok, except that you'll note that the last select >> clause is `wineries_external`.`` This is where the count select should >> be. >> >> Anyone care to shed any light on the matter? Documentation seems to be >> ridiculously skint around Arel giving me the feeling that while it's >> hyped to be something amazing, it might be too complex for it's own >> good. >> >> For bonus points, how do I do a project("table.*")? >> >> >> Cheers, >> >> Michael >> >> -- >> 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]<rails-oceania%[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]<rails-oceania%[email protected]> . > For more options, visit this group at > http://groups.google.com/group/rails-oceania?hl=en. > -- Checkout my new website: http://myachinghead.net http://wakeless.net -- 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.
