#202 Active Record Queries in Rails 3

Just can't wait for the day when rails3 is released and all gems and plugins are working with rails3.

And of course I'm waiting for the next Railscast ^^

I was waiting for this one to come with the new Rails 3 casts you are pumping out. Arel rocks :)

Thanks Ryan.

Nice introduction.. Thanks for covering the Rails3 changes; it is a lot more enjoyable for me than just reading the blogs. :)

with this new way of doing things, how would you go doing an "or" condition?

In the "Advanced Rails Recipes" book from Mike Clark, recipe 31 illustrates how to prevent train wrecks. According to Clark, train wrecks are long method chains to access attributes. Somehow the new syntax with queries like User.where(:name => "nonsense").order('id').limit(10) looks like a train wreck.

@someone: you can do a "OR query" like this:

    cars = Car.where(["color = ? OR color = ?", "black", "red"])

great screencast, thanks Ryan

Wow, Arel certainly cleans things up a lot especially with scopes. Awesome!

@EppO: I believe you can leave out the square brackets:

cars = Car.where("color = ? OR color = ?", "black", "red")

@0x4a6f4672, I think the train wreck analogy mostly applies when you are jumping through many different types of objects and through methods which can return different data. For example:

Article.first.comments.first.user.name

That line is fairly fragile because "first" may return nil. Also if there is a problem it can be more difficult to debug.

These find method calls are very consistent and always return a relation or scope object which behaves the same. I don't see a problem with it.

I'm sold on the new active record queries after watching this screencast. Thanks again for another great, easy-to-digest, screencast.

Does this increase the number of SQL queries?

Great timing with this Railscast!!

2 Q's

1. Would there be an easy way to chain where with OR instead/with AND

2. any ideas why this is happening?

e.g. THIS FAILS

models/post.rb

  scope :visible, where("is_public =?", true)

controllers/posts_controller.rb

@posts = Post.where("user_id=?",current_user)
@posts = Post.visible if params[:view_all_posts]

this fails complaining about nil array..

But..THIS WORKS

controllers/posts_controller.rb

@posts = Post.where("user_id=?",current_user)
@posts = Post.where("is_public =?", true) if params[:view_all_posts]

this works...

Ty all,

Thanks Ryan for your great screencast. Looking forward to more Rails 3 screencasts! :)

Have a nice day.

Interesting changes, very clean. I like this a lot more that that bundler business, that's for sure. Unfortunately, even though we're all being encouraged to switch to Rails 3 asap, there are a lot of plugins and gems that are still incompatible. So its going to be a while for me, anyway.

@Greg, no, in the long run it should decrease the number of SQL queries. Since it waits until the last moment before the query happens, there are times the query may not be necessary.

@BrettD, I don't think there is any additional support for OR queries here. You'll still need to use an SQL string in the where clause, but I wouldn't be surprised if a plugin comes out which does add OR capabilities. I have some ideas on how it could be done.

As for your second question, I have no idea why that is failing without seeing the full error message. Feel free to email me: ryan at railscasts dot com.

Hi!

I would love to see your ideas in form of a plugin, ryan. :)

Data Mapper of Merb does provide such a syntax, you can do queries like :age.gt(18) or :name.like("%rails%"). I believe, "or" conditions where possible aswell, but I don't recall the syntax...

iGEL

Thanks for the great screencast Ryan! I'm really looking forward to Rails 3, and the lazy loading features you mentioned should really help performance in database intensive/high-traffic applications. Looks like some REALLY sweet stuff!

Thanks ! love your Work.

I was pretty much unexcited about Rails 3, for the things I've seen so far, but this is really clear and cool!
Consider casting your scraps about controller ?

Ryan, thank you very much for (again) a splendid video.

If you would charge for accessing this site I would gladly pay you.

Thanks!

Ryan,

Thanks for another fantastic episode, keep it up - you're a one man training company !

thanks

Ryan,
Loving the tour of Rails 3 so far. This is amazing content :)

-John

Isn't this exactly like Sequel? Anybody know the of any differences (besides filter => where and those stuff).

Allways like sequel anyways.

More importantly. Is AR now threadsafe (sequal is)

Thanks!

where("hidden != ?", true)

shouldn't that be

where("not hidden") ?

While I see this is different, and possibly better, I am worried about two things.

First, I don't see why this change must occur. It seems that all it is doing is building a SQL query in the background and delaying its execution. Why can't Foo.all delay in exactly the same way, without syntax change?

Second, I used hashes a lot, such as conditions = [ "age < :age", { :age => age }] -- is that syntax actually gone?

I just don't see this as a step forward, but just an incompatible change.

Ryan:

Thanks for yet another wonderful episode!

With respect to your "nifty-generators" gem, will it available in 3.0?

Thanks,
Alex

great job as usual, ryan but please, stop doing casts for rails 3.

- not many use it yet
- it's not stable (things break here and there) and we dont want to waste development time on something that's 3/4 baked.
- please continue to support rails 2.3.5+

thank you, ryan! :)

@James think of it this way; you can spend 10 minutes watching the screencast and have a good idea of what is going on with Rails3, without wasting hours of your time messing with it.

I am still using 2.3.5 too, but I still want to know about 3.0 :)

@James

Rails 3.0 works great as it is right now. Not something you would use in production for sure. But playing with it to learn what's new is great. And Bundler handles dependencies great with Git.

Only downside is the lack of ported plugins. But they are getting there. I'm already using MongoMapper and Formtastic together. Paperclip has a Rails3-branch that was forked too, which works properly with Rails 3.0beta. So it's getting there =)!

@Omar, using "not hidden" will work in SQLite3 but is not database agnostic. I prefer to use SQL queries which can be used in other SQL databases if need be.

@Michael, it appears that the hash syntax you mentioned is still supported.

@Alex, I'm working on upgrading nifty generators to Rails 3, yes.

@James, you're right that Rails 3 is not yet stable and should not be used in production. However I highly recommend everyone give it a try now. Start a small project with Rails 3 or try branching an existing project and upgrading to get an idea of what is involved and report anything that breaks.

Right now a lot of the work needs to be done by the community. One of the biggest roadblocks to upgrading is unsupported plugins. If you want the plugins you use to work with Rails 3 the best thing to do is try it now to see what breaks and report any issues to the developers.

@0x4a6f4672, @Ryan: Ruby 1.9 has introduced slip intermediate operations
into the middle of a call chain, so to prevent the train wrecks you can use tap() method, eg:
Article.first.comments.first.tap {|x| puts x}.user.name

fdg dgf dg dg

@Ryan, I believe you will find the "not" operator for booleans will work for all databases.

If that was not the case, queries that use "is not null" would never work, surely?

Great screencast! At first glance, I figured the changes were merely for the purposes of being more DRY, but now I see how powerful the benefit of lazy loading can be!

U'r 2 cool 4 skool Ryan!

scope :recent, lambda { |*args| {where("created_at > ?",args.first) } }

is this correct?

any ideas as will_paginate and rails3 is breaking

Hi Ryan,

great screencast, as usual!

I have a question: how would I lazy-load #all?

In the controller you did:
@articles = Article.order(...)

What if I wanted to lazy-load all articles? I mean something like:

@articles = Article # would need to implement Enumerable

or some other way to turn Article into an empty Relation.

Technically, I could just create a dummy Relation object as in:

@articles = Article.where('1 = 1')

but that doesn't seem to Railish...

I'm a little late to this party, but a couple of weeks ago I put together a post discussing some interesting things about the kinds of values that are accepted by Relation#where.

In particular, you can feed Arel predicates to #where directly, or for that matter, anything that responds to #to_sql. Kind of neat I think. It's at http://bit.ly/dhSjPJ if you're interested. :)

Hi,How to fit the large string into single textbox without breaking the word in active reports.

Hi Ryan,

I hope this comment does not get lost in all the spam that has filled your page :(

I am using pg adapter(postgres) instead of sqlite3.

While playing with the console I found that when i do Article.order("name") i do not get a list ActiveRecord::Relation objects(as shown in the screencast). You spoke about lazy-loading.. this however does not seem to work with the setup that i have. I instead get the entire list of articles (as you would if you did Article.order("name").all).

Has it got anything to do with the postgres adapter?
Is this a bug that needs to be reported?

Thanks,
Shripad

Olek, I had the same question as you: how do you lazy-load #all? I found the answer in Pratik's post (see link in the show notes): use #scoped:

@articles = Article.scoped

Seems to me the Rails 3 generators should start generating #scoped by default.

See also http://ilikestuffblog.com/2010/10/30/how-to-lazily-find-all-records-in-rails-3-with-arel/

great job as usual, ryan but please, stop doing casts for rails 3.

- not many use it yet
- it's not stable (things break here and there) and we dont want to waste development time on something that's 3/4 baked.
- please continue to support rails 2.3.5+

thank you, ryan! :)

thank you ryan!

While playing with the console I found that when i do Article.order("name") i do not get a list ActiveRecord::Relation objects(as shown in the screencast). You spoke about lazy-loading.. starting a business in cyprus

i was waiting for such a post and its information!
Owner of london escorts

Very nice info!