#215 Advanced Queries in Rails 3

Ryan,

Thanks for the mention of MetaWhere today! I'm really excited about it and I hope it helps make Rails 3 conditions a lot more powerful and flexible. Continuing on your theme, I've also got a post up at http://metautonomo.us/2010/05/11/activerecord-relation-vs-arel/ that your viewers might be interested in which helps to clarify some of the differences between AR and Arel. It also has a few more nifty tricks you can pull with Arel.

Better late than never!

Thanks Ryan

Rails 3 is very exciting!

I hope to see you at RailsConf 2010, I want to buy you dinner or a least a beer.

Ben

This is perfect! Thanks so much Ryan. I was just exploring the topics you covered today. Can't wait to implement what I learned tomorrow!

How could you list the leaves of a node in an acts_as_nested_set model using arel?

The select statement should be something like this

select * from tree where lft = rgt-1

very nice, thanks for such a great post :)

Thanks for this episode Ryan, awesome work as usual !
I really like the idea behind MetaWhere except the fact that it extends Symbol so I've created a small wrapper around Arel::Attribute (http://github.com/samleb/sexy_scopes).
I hope you guys will find it useful.

Why does .includes not behave like .joins?

Phrase.joins(
  :feed_entry_phrase_correlations).merge(
  FeedEntryPhraseCorrelation.since(
  10.minutes.ago)).all

Generates this sql:

SELECT
  `phrases`.*
FROM
  `phrases`
  INNER JOIN `feed_entry_phrase_correlations` ON `feed_entry_phrase_correlations`.`phrase_id` = `phrases`.`id`
WHERE
  (feed_entry_phrase_correlations.created_at > '2010-06-09 16:23:42')

Whereas using .includes:
Phrase.includes(
  :feed_entry_phrase_correlations).merge(
  FeedEntryPhraseCorrelation.since(
  10.minutes.ago)).all

Generates this sql:
SELECT
  `phrases`.`id` AS t0_r0,
  `phrases`.`phrase` AS t0_r1,
  `phrases`.`created_at` AS t0_r2,
  `phrases`.`updated_at` AS t0_r3,
  `feed_entry_phrase_correlations`.`id` AS t1_r0,
  `feed_entry_phrase_correlations`.`feed_entry_id` AS t1_r1,
  `feed_entry_phrase_correlations`.`phrase_id` AS t1_r2,
  `feed_entry_phrase_correlations`.`count` AS t1_r3,
  `feed_entry_phrase_correlations`.`created_at` AS t1_r4,
  `feed_entry_phrase_correlations`.`updated_at` AS t1_r5

FROM
  `phrases` LEFT OUTER JOIN `feed_entry_phrase_correlations` ON `feed_entry_phrase_correlations`.`phrase_id` = `phrases`.`id` WHERE (feed_entry_phrase_correlations.created_at > '2010-06-09 16:23:42')

Ok, I now understand why it's using LEFT JOINS. (It has to in case the first model does not appear in the second's table).

Here is the real problem as I see it:

When I place a where clause on an included model, AR stops fetching each model in its own query and combines everything into a big LEFT JOIN monstrosity.

While this works, it is very non-performant when several models are included as I get back many many rows that all need to be aggregated by the ORM. This also requires more network IO.

I've created this Gist to demonstrate what's happening:
http://gist.github.com/431943

I'll send a pizza to anyone who can help me with this.

One more note.

The edge documents here:
http://edgeguides.rubyonrails.org/active_record_querying.html

Say:
4.2 Specifying Conditions on Eager Loaded Associations

Even though Active Record lets you specify conditions on the eager loaded associations just like joins, the recommended way is to use joins instead.

---

But they don't say how to eager load associations using joins.

There are a way to concat 2 where clauses with OR operator? Like Post.where("name = 'foo'") | Post.where("name = 'bar'") ?

I have the same question as Pioz: Is there a way to use an OR operator with your WHERE clauses?

Yeay for Rainbows and adorable froggy umbrellas! Have a great weekend!
http://www.arabacar.com/araba

How would I use a custom scope within default_scope?

(if the user is logged in, show inactive stuff if not only the active stuff)

This is what I'm trying to do ...

https://gist.github.com/844917

hope someone can shed some light on this.

Thanks,
Matenia

Why to_sql function works like this:

Comment.root.to_sql
=> "SELECT comments.* FROM comments WHERE comments.parent_id IS NULL ORDER BY created_at desc"

Comment.includes(:user).to_sql
=> "SELECT comments.* FROM comments"

in the second example real sql query should differs from result shown by to_sql function

I don`t want to use 'find_by_sql', but currently it is the only way to get ordered relation for pagination. It would be good to use 'last' and 'first' methods.

I have modelname.where(...).union(modelname.where(...))
how i can get something like modelname.where(...).union(modelname.where(...)).order('something')?

I like the new queries much more than in 2.3, I find the chaining functionality the most useful. - thank you

Irrelevant for 3.2.2, rails bug or API change?

In one of my queries I used {:id => nil} for 'id IS NULL'. How to write a similar query for 'id IS NOT NULL' ?

FYI, the & shortcut syntax for merging has been deprecated. Just use .merge instead.
https://github.com/rails/rails/commit/66003f596452aba927312c4218dfc8d408166d54

For anyone interested in diving deeper into scopes on associations in Rails, I've written up a post at http://ducktypelabs.com/using-scope-with-associations/

Defining scopes on your associations can come in handy for cleaning your code up and ensuring that business logic stays in one place. I was also motivated to write about it because there have been quite a few situations where I wanted to filter a group of records by given values of association records, for which scopes are useful.