ActiveRecord find

Rails
1

Hey guys,

The find below works just fine:
@companies = Company.find(:all, :conditions =>
[‘name = :input’, {:input => input}], :include =>
[:application])

I was wondering, is there a way in the above code to have the condition
below:
name.downcase.include? input.downcase
Basically checking if the name column includes input regardless of case.
That way a search for ‘ruby rails’ will pick ‘Ruby R.’, ‘Ruby on
Rails’, ‘ruby on RAILS’ etc

Thanks for your help.

2

you’d have to do that at the db level as part of your condition, AFAIK.

in mysql, for example

find(:all, :conditions > [“UPPER(name) = ?”, input.upcase], …)

3

As far as I’m aware (certainly on MySQL and MSSQL), database queries
are case-insensitive. As such, so will your AR queries:

find(:all, :conditions => [“name = ?”, “STEVE”]

is the same as:

find(:all, :conditions => [“name = ?”, “steve”]

I think what Richard is asking is if you can make the include?()
method case insensitive. For example:
“STEVE”.include? “steve” -> false

what he needs is:

“STEVE”.include? “steve” -> true

I’m not exactly sure of how to achieve this neatly - perhaps someone
else has an idea?

Steve

4

Stephen B. wrote:

As far as I’m aware (certainly on MySQL and MSSQL), database queries
are case-insensitive. As such, so will your AR queries:

find(:all, :conditions => [“name = ?”, “STEVE”]

is the same as:

find(:all, :conditions => [“name = ?”, “steve”]

I think what Richard is asking is if you can make the include?()
method case insensitive. For example:
“STEVE”.include? “steve” -> false

what he needs is:

“STEVE”.include? “steve” -> true

I’m not exactly sure of how to achieve this neatly - perhaps someone
else has an idea?

Steve

Thanks Chris and Steve. Chris your answer gets me half there. Forgetting
about case sensitivity for a moment, my real question is there a way of
putting name.include? input in an active record find? From what I am
currently aware of you can only have =,>,< etc in the conditions. So
something like this:

find(:all, :conditions => [“name.include?”, input])

5

On 29/03/07, Steve B. [email protected] wrote:

As far as I’m aware (certainly on MySQL and MSSQL), database queries
are case-insensitive. As such, so will your AR queries:

For SQL Server, this depends how your schema is setup. By default
it’s case insensitive, but it can be made case sensitive if required.

Tom

6

Thanks Chris and Steve. Chris your answer gets me half there. Forgetting
about case sensitivity for a moment, my real question is there a way of
putting name.include? input in an active record find? From what I am
currently aware of you can only have =,>,< etc in the conditions. So
something like this:

find(:all, :conditions => [“name.include?”, input])

Not sure if it’s exactly what you’re looking for but you can do pattern
matching like so (at least with mysql):

find(:all, :conditions => [“name LIKE ?”, input])

7

I think what Richard is asking is if you can make the include?()
method case insensitive. For example:
“STEVE”.include? “steve” → false

Only valid SQL is allowed. Use a LIKE statement in your where clause:

[‘name LIKE ?’, ‘steve’]

You can use % as a wildcard on one or both sides of the query.

[‘name LIKE ?’, ‘%steve%’]

However at this point you’re at the mercy of the database. All
ActiveRecord does is builds the query and sends it to the connection
adapter.

–
Rick O.
http://lighthouseapp.com
http://weblog.techno-weenie.net
http://mephistoblog.com

8

what do you mean by ‘you’re at the mercy of the database’?
do you mean that this method would go thru connection
adapter, while the first way, ActiveRecord will do something
magic for you?

On 3/29/07, Rick O. [email protected] wrote:

http://mephistoblog.com

–
Best Regards
XRuby http://xruby.com
femto 如流，新一代智能工作平台

9

On 3/29/07, femto gary [email protected] wrote:

what do you mean by ‘you’re at the mercy of the database’?
do you mean that this method would go thru connection
adapter, while the first way, ActiveRecord will do something
magic for you?

ActiveRecord uses that to generate an SQL query. That that does,
however, is up to the database. You may find that mysql or mssql are
both case insensitive, but postgresql will require ILIKE to work the
same.

–
Rick O.
http://lighthouseapp.com
http://weblog.techno-weenie.net
http://mephistoblog.com

10

Thanks a lot Dave and Rick, that was what I was looking for.

One other thing, assume we have these two class:

class Application < ActiveRecord::Base
has_one

class Company < ActiveRecord::Base
belongs_to :application
attr_accessible :name
end

11

i think what he wants is match case-insensitive strings

ie, Ruby On Rails == ruby on rails

or any variation in case.

mysql comparisons are by default case-insensitive, so you don’t have
to worry. what you do have to worry about is, if your project is
intended to support multiple databases and will be public, is to deal
with case-sensitive comparisons where the database is case-sensitive
by default or the user has made it case-sensitive.

I believe UPPER (or LOWER) is an ANSI sql function and should be
supported by all databases. so in my opinion, i would go with

find(:all, :conditions => [“UPPER(col_name) = ?”, input.upcase])

and that should do it.

keep in mind that the the :conditions param is either a sql string
fragment

“col_name = ‘#{input}’”

an array

[“col_name = ?”, input] or [“col_name = :input”, { :input => input }]

or a hash

{ :col_name => input }

all of these generate

“col_name = ‘xyz’”

there is no shorthand method of accomplishing what you want.

12

Thanks a lot Dave and Rick, that was what I was looking for.

One other thing, assume we have these two class:

class Application < ActiveRecord::Base
has_one :company
attr_accessible :new <-------------------- this is a boolean

class Company < ActiveRecord::Base
belongs_to :application
attr_accessible :name <------------------- this is varchar
end

I have a name and I want to find all companies with that name and whose
applications are new. Something like this?

Company.find(:all, :include => [:application], :conditions => [‘name
LIKE ? and application.new’, input])

What is the right way to do that in with find if there is one? Or do I
have to craft an sql query?

13

Chris H. wrote:

i think what he wants is match case-insensitive strings

ie, Ruby On Rails == ruby on rails

or any variation in case.

mysql comparisons are by default case-insensitive, so you don’t have
to worry. what you do have to worry about is, if your project is
intended to support multiple databases and will be public, is to deal
with case-sensitive comparisons where the database is case-sensitive
by default or the user has made it case-sensitive.

I believe UPPER (or LOWER) is an ANSI sql function and should be
supported by all databases. so in my opinion, i would go with

find(:all, :conditions => [“UPPER(col_name) = ?”, input.upcase])

and that should do it.

keep in mind that the the :conditions param is either a sql string
fragment

“col_name = ‘#{input}’”

Thanks Chris. The UPPER/LOWER and the LIKE/ILIKE give me what I was
looking for. I have just got myself a postgre reference book that I am
digging into, my db knowledge needs to improve a bit.

Sorry for the double post.