Which finder syntax would be preferred as far as being the most database agnostic?

rubyonrails-talk
1

Which finder syntax is preferred? Or are they the same?

I'm developing on sqlite and I've posted the result of each query...they kind of look the same but not really. Would this scale to say, Mysql or Postgres? Thanks for any advice.

named_scope :disabled, :order => 'name', :conditions => { :disabled_at => !nil } SELECT * FROM "groups" WHERE ("groups"."disabled_at" = 't') ORDER BY name

or

named_scope :disabled, :order => 'name', :conditions => ['disabled_at <> ?', nil] SELECT * FROM "groups" WHERE (disabled_at <> NULL) ORDER BY name

2

I for one prefer the first syntax, since it eliminates SQL from the ruby code, but that's just my preference.

Cheers, Tim

3

Lee Smith wrote:

Which finder syntax is preferred? Or are they the same?

I'm developing on sqlite and I've posted the result of each query...they kind of look the same but not really. Would this scale to say, Mysql or Postgres? Thanks for any advice.

named_scope :disabled, :order => 'name', :conditions => { :disabled_at => !nil } SELECT * FROM "groups" WHERE ("groups"."disabled_at" = 't') ORDER BY name

or

named_scope :disabled, :order => 'name', :conditions => ['disabled_at <> ?', nil] SELECT * FROM "groups" WHERE (disabled_at <> NULL) ORDER BY name

Use the first syntax -- the second one is incorrect. I don't know if this is true in SQLite, but in the other DBs, NULL = NULL returns NULL, so your <> NULL construct will return TRUE in all cases, and so it is pointless. In the first case, Rails automatically generates the proper syntax.

Best,

4

> named_scope :disabled, :order => 'name', :conditions => ['disabled_at > <> ?', nil] > SELECT * FROM "groups" WHERE (disabled_at <> NULL) ORDER BY name

Use the first syntax -- the second one is incorrect. I don't know if this is true in SQLite, but in the other DBs, NULL = NULL returns NULL, so your <> NULL construct will return TRUE in all cases, and so it is pointless. In the first case, Rails automatically generates the proper syntax.

Actually NULL <> NULL is also NULL. Hooray for 3 way logic. (doesn't mean the OP's would work though). In general I tend to use the hash form of conditions when possible, but that's not always the case.

Fred