Railscasts - Advanced Queries in Rails 3

#215

May 24, 2010

Advanced Queries in Rails 3

Learn some advanced techniques regarding named scopes and get an introduction to Arel in this episode.

Tags: rails-3.0 active-record

Download (33.9 MB, 9:26)

alternative download for iPod & Apple TV (47 MB, 9:26)

Read it on ASCIIcasts

Resources

# rails console
Product.cheap.to_sql
(Category.joins(:products) & Product.cheap).to_sql
Category.with_cheap_products.to_sql
p = Product.discontinued.build
p.discontinued
t = Product.arel_table
t[:price].eq(2.99)
t[:name].matches("%catan").to_sql
Product.where(t[:price].eq(2.99).or(t[:name].matches("%catan")))

# models/product.rb
scope :discontinued, where(:discontinued => true)

def self.cheaper_than(price)
  where("products.price < ?", price)
end

scope :cheap, cheaper_than(5)

# models/category.rb
scope :with_cheap_products, joins(:products) & Product.cheap

< Previous Episode

8 comments

1. Ernie Miller May 24, 2010 at 08:58

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.

2. Sephi-Chan May 24, 2010 at 09:14

Thanks Ryan, it's a real pleasure to heard you each Monday. :)

Sephi-Chan

3. Nate Bird May 24, 2010 at 11:18

Better late than never!

4. Benjamin Lewis May 24, 2010 at 12:24

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

5. Sam Soffes May 24, 2010 at 17:05

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!

6. chris May 24, 2010 at 17:40

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

7. Tom May 24, 2010 at 23:20

Very cool.

<a href="">Test</a>

8. Michael Hasenstein May 25, 2010 at 01:52

offtopic: Ryan, could you do something against all the spam comments? Not just those right here, they also are all over the place in older articles, and there are many posted by computers, not just the human ones. This takes at least some fun out of reading the comments.

9. Michael Hasenstein May 25, 2010 at 01:57

offtopic: Ryan, could you do something against all the spam comments? Not just those right here, they also are all over the place in older articles, and there are many posted by computers, not just the human ones. This takes at least some fun out of reading the comments.

10. emlak May 28, 2010 at 09:28

Thank you very much!

11. pankaj May 28, 2010 at 11:28

very nice, thanks for such a great post :)

12. Samuel Lebeau May 30, 2010 at 09:26

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.

13. Keith Carter Jun 09, 2010 at 09:43

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')

14. Keith Carter Jun 09, 2010 at 11:41

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.

15. Keith Carter Jun 09, 2010 at 13:14

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.

16. elvankent halı yıkama Jul 22, 2010 at 00:18

Great.I like it!

17. Pioz Jul 23, 2010 at 02:33

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

18. aluminum case Jul 25, 2010 at 19:35

very good post, I like this very much. Thank you.

21. Sony PCGA-BP1N Battery Jul 30, 2010 at 23:51

Thanks Ryan

Rails 3 is very exciting!

21. Sony Vaio Pcg-v505 Series Battery Jul 30, 2010 at 23:54

Great.I like it!

22. UGG Boots on sale Aug 10, 2010 at 18:42

Gooooooooooooooooooood luck ~~!!

23. True Religion jeans Aug 14, 2010 at 03:06

Wow great information, really helpful to me. http://www.cmonc.com Keep posting just like this. Thanks for precious information.

24. Rip Blu-ray for Mac Aug 18, 2010 at 01:34

Thanks,it's so good.
suport!

25. Wholesale baseball hats Aug 20, 2010 at 20:02

Took me awhile to read all the comments, but I really love the article. It proved to be very useful to me and I am sure to all the commenters here! It's always nice when you can not only be informed, but also entertained! I'm sure you had fun writing this article. Comfortably, the article is really the sweetest on this precious topic.

26. converse all star Aug 20, 2010 at 20:52

love converse all star,love yourself.High quality low price.It's fit for you.

27. medyum Aug 22, 2010 at 09:25

The information you provided was very useful. Because of your help, thank you.

28. medyum Aug 22, 2010 at 09:26

Thanks for such a great post and the review, I am totally impressed! Keep stuff like this coming.

29. lily Aug 23, 2010 at 23:32

What youre saying is completely true. I know that everybody must say the same thing, but I just think that you put it
in a way that everyone can understand. I also love the images you put in here. They fit so well with what youre trying to say.
Im sure youll reach so many people with what youve got to say.<b><a href=http://www.tygluegun.com>glue stick</a></b> |<b><a href=http://www.tygluegun.com>glue gun</a></b>
|<b><a href=http://www.wanjia-ylm.cn>booster cable</a></b> |<b><a href=http://www.wanjia-ylm.cn>power cord</a></b>

30. cheap clothes Aug 24, 2010 at 19:16

David Heinemeier Hansson..thanks...<a href="http://www.superflatiron.com">chi hair straighteners</a>

31. louis vuitton shoes Aug 26, 2010 at 23:14

Thanks for sharing your article. I really enjoyed it. I put a link to my site to here so other people can read it. My readers have about the same interets

32. mbt shoes sale Aug 28, 2010 at 09:43

t's always nice when you can not only be informed, but also entertained! I'm sure you had fun writing this article. Comfortably, the article is really the sweetest on this precious topic.

33. cheap mbt shoes Aug 28, 2010 at 10:15

there are many posted by computers, not just the human ones. This takes at least some fun out of reading the comments.

34. cheap ugg boots sale Aug 28, 2010 at 10:16

`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 >

35. herve leger dress Aug 30, 2010 at 19:52

Thanks for sharing your article. I really enjoyed it. I put a link to my site to here so other people can read it.

36. snow boots Aug 30, 2010 at 20:16

I know that everybody must say the same thing, but I just think that you put it .

37. blu ray ripper Sep 01, 2010 at 23:42

It looks nice and I am in need of this.

Add your comment:

navigation:

sponsored by:

tags:

if you want to help:

required:

Give Back to Open Source