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]. For more options, visit this group at http://groups.google.com/group/rails-oceania?hl=en.
