Find WHERE in Rails


#1

I’d like to find all records that have a certain integer as their
“level” field in the database. I know how to find all the records:

allquestions = Question.find(:all)

…and I know how to find the one record that has a certain id:

allquestions = Question.find(params[:id])

…but how do you find all the records that share a certain value in one
of their fields? I tried

allquestions = Question.find(params[:level => 3])

…but it just gave me an error.


#2

sean colquhoun wrote:

I’d like to find all records that have a certain integer as their
“level” field in the database. I know how to find all the records:

allquestions = Question.find(:all)

…and I know how to find the one record that has a certain id:

allquestions = Question.find(params[:id])

…but how do you find all the records that share a certain value in one
of their fields? I tried

allquestions = Question.find(params[:level => 3])

…but it just gave me an error.

You want:

allquestions = Question.find(:all, :conditions => "level = ‘3’ ")

Keep in mind that params[:whatever] calls data specifically from the
params hash. (The contents of which you can see by forcing an error
before you load a page. There’s probably a better way, but I haven’t
read the Testing section of AWDWR. :P)

-Adam


#3

sean colquhoun wrote:

I’d like to find all records that have a certain integer as their
“level” field in the database. I know how to find all the records:

allquestions = Question.find(:all)

…and I know how to find the one record that has a certain id:

allquestions = Question.find(params[:id])

…but how do you find all the records that share a certain value in one
of their fields? I tried

allquestions = Question.find(params[:level => 3])

…but it just gave me an error.

Question.find(:all, :conditions => ‘level = 3’)

should work.

/ H


#4

On Apr 6, 2006, at 11:56 PM, sean colquhoun wrote:

in one
of their fields? I tried

allquestions = Question.find(params[:level => 3])

…but it just gave me an error.

allquestions = Question.find(:all, :conditions => ‘level = 3’)


– Tom M.


#5

Thank you so much everybody. Workin’ like a charm now.


#6

Adam B. wrote:

Keep in mind that params[:whatever] calls data specifically from the
params hash. (The contents of which you can see by forcing an error
before you load a page. There’s probably a better way, but I haven’t
read the Testing section of AWDWR. :P)

You can use the “debug” helper:

debug params

With any object as an argument, debug will return information about it
and its contents.

Jeff


#7

Wait. Spoke too soon. I can’t put a variable into where the ‘3’ is in
the above example. Is there a syntax that will let me set the condition
as a variable or does it have to be hard-coded?


#8

Brian V. Hughes wrote:

allquestions = Question.find(:all,
:conditions => [“level = ?”, params[:level]])

Each occurrence of “?” in the first item is replaced, in series, by the
2nd -
nth items in the array.

-Brian

This is generally the preferred form, since although it might not make a
difference in this particular case, in general it prevents SQL
injection. See pg 440 of Agile Web Dev w/Rails.

Keith


#9

sean colquhoun wrote:

Wait. Spoke too soon. I can’t put a variable into where the ‘3’ is in
the above example. Is there a syntax that will let me set the condition
as a variable or does it have to be hard-coded?

There are two ways to accomplish what you’re trying to do. What I think
is the
better way is simply:

allquestions = Question.find_all_by_level(params[:level])

However, if you want to use the :conditions option, with variable
vallues, you
can. You simply need to pass the conditions as an Array, like this:

allquestions = Question.find(:all,
:conditions => [“level = ?”, params[:level]])

Each occurrence of “?” in the first item is replaced, in series, by the
2nd -
nth items in the array.

-Brian


#10

On Apr 7, 2006, at 8:49 AM, Keith L. wrote:

-Brian

This is generally the preferred form, since although it might not
make a
difference in this particular case, in general it prevents SQL
injection. See pg 440 of Agile Web Dev w/Rails.

I’m quite sure Question.find_all_by_levels quotes the content as well,
thereby avoiding SQL injection.


– Tom M.


#11

Sorry guys - would it be possible to explain in a little more depth? I’m
not sure how to implement your recommendations.

For example, if I use
allquestions = Question.find_all_by_level(params[:level]),

Is “find_all_by_level” some kind of dynamic method that I would need to
change depending on the column name I use? i.e. if I want to pull
records out by “chapter”, do I have to change the method call to
“find_all_by_chapter”? The Rails API didn’t have anything on this. And,
I’m still shaky as to the difference between types of variables, for
example ‘@variables’ vs. ‘:variables’ â?? do I have to set :level within
the controller or is that a value that I can pull out of my model?

…and then if I use

allquestions = Question.find(:all,
:conditions => [“level = ?”, params[:level]])

Well, first of all, do I have to use this method with an array? All I
want to do is set a level in my model and then have the controller find
all the records that are of that level. I can’t think of any reason to
pass this thing more than one value, but I’m a total beginner too, so
there might be one that I can’t think of.

I guess also, since I don’t want to keep bothering people on this board
with simple stuff, if anybody knows of something online that will help
me understand things like how to know which variables are available to
which methods (variable scope?) and which ones aren’t, what exactly the
params[] array is and how to use it (also what it’s scope is), that sort
of thing. I would be forever in your debt.


#12

On Apr 9, 2006, at 8:43 PM, sean colquhoun wrote:

I guess also, since I don’t want to keep bothering people on this
board
with simple stuff, if anybody knows of something online that will help
me understand things like how to know which variables are available to
which methods (variable scope?) and which ones aren’t, what exactly
the
params[] array is and how to use it (also what it’s scope is), that
sort
of thing. I would be forever in your debt.

http://tinyurl.com/55oyg


– Tom M.


#13

On Apr 9, 2006, at 11:43 PM, sean colquhoun wrote:

Sorry guys - would it be possible to explain in a little more
depth? I’m
not sure how to implement your recommendations.

I’ll give it a try. Hopefully others will chime in if I miss
something. :wink:

For example, if I use
allquestions = Question.find_all_by_level(params[:level]),

Is “find_all_by_level” some kind of dynamic method that I would
need to
change depending on the column name I use? i.e. if I want to pull
records out by “chapter”, do I have to change the method call to
“find_all_by_chapter”?

Yes, it’s a dynamic finder that allows you to specify one (or two)
columns in a Model, upon which you want to perform a find. It’s very
useful when the columns that you will be finding on don’t change,
since as you pointed out, if you wanted to find on chapters, you
would need to use a different dynamic finder.

The Rails API didn’t have anything on this. And,
I’m still shaky as to the difference between types of variables, for
example ‘@variables’ vs. ‘:variables’ ? do I have to set :level within
the controller or is that a value that I can pull out of my model?

Actually, I’m certain the Rails API, in the ActiveRecord::Base
section covers dynamic finders quite thoroughly. I know because I’ve
read through that section, more than once. :slight_smile: But I learned about
dynamic finders from the AWDwR book.

…and then if I use

allquestions = Question.find(:all,
:conditions => [“level = ?”, params[:level]])

Well, first of all, do I have to use this method with an array?

Do you have to? No. But if you are taking a item from the params
object, which is actually set by the browser request, and funneling
that directly into a find method, you become subject to a form of
security exploit known as SQL Injection. Using the above format to
build your find protects you from this exploit because it allows
Rails to properly escape/quote the value that you want to use in
your :conditions option.

All I want to do is set a level in my model and then have the
controller find all the records that are of that level. I can’t
think of any reason to pass this thing more than one value, but
I’m a total beginner too, so there might be one that I can’t think
of.

Technically, you’re not passing it more than one value, but using the
array form for :conditions is what you are supposed to do when you
are dealing with a value that you don’t have a way to implicitly
trust before you hand it off to Find for turning into an executable
SQL statement.

-Brian


#14

Tom M. wrote:

http://tinyurl.com/55oyg


– Tom M.

Already have it, but it’s a meal of a book. If everything is in there,
I’ll skip ahead and look for it. Thanks!


#15

The Rails API didn’t have anything on this. And,
I’m still shaky as to the difference between types of variables, for
example ‘@variables’ vs. ‘:variables’ � do I have to set :level within
the controller or is that a value that I can pull out of my model?

Actually, I’m certain the Rails API, in the ActiveRecord::Base
section covers dynamic finders quite thoroughly. I know because I’ve
read through that section, more than once. :slight_smile: But I learned about
dynamic finders from the AWDwR book.

Ha ha ha. Yeah, I forgot to include the disclaimer that I can’t read
APIs, apparently. I was looking in the “methods” list because I thought
it was a method. Whoops.


#16

Ok.

:level = 2
allquestions = Question.find_all_by_level(params[:level])

is giving me

SyntaxError in #
./script/…/config/…/app/controllers/test_controller.rb:26: parse
error, unexpected ‘=’, expecting kEND
:level = 2
^

Why can’t I set :level in the controller?


#17

sean colquhoun wrote:

Ok.

:level = 2
allquestions = Question.find_all_by_level(params[:level])

is giving me

SyntaxError in #
./script/…/config/…/app/controllers/test_controller.rb:26: parse
error, unexpected ‘=’, expecting kEND
:level = 2
^

Why can’t I set :level in the controller?

You might want to look into the Pickaxe Book (“Programming Ruby” by Dave
Thomas), which covers basic Ruby concepts like variables and symbols.

A name with a : before it is a symbol. That’s different from a
variable, which is what you want in the example you’re giving.

level = 2
allquestions = Question.find_all_by_level(level)

You only need to use “params[:level]” if you’re using the parameters
sent from another page, like a form.

If you had a form on a search page, for example, with a dropdown list or
an text field for the level value, then the number the user enters would
be sent in the params hash with the :level key–params[:level].

If you just want to set a variable to search for some value explicitly,
you can use a variable.

If you’re just getting started with Rails, the “Agile Web Dev” book’s
tutorial chapters are a great way to do it. Build the depot app from
the book, and all this stuff gets covered.

Jeff


#18

sean colquhoun wrote:

You might want to look into the Pickaxe Book (“Programming Ruby” by Dave
Thomas), which covers basic Ruby concepts like variables and symbols.

Aha. For some reason I was under the impression that the :symbols were
unique to Rails. That’s probably exactly what I need - I’ll look into
it. Thanks.

There’s a discussion on the subject with some useful links in the Ruby
mailing list (which I access through ruby-forum at
http://www.ruby-forum.com/forum/4 ) in a subject called something like
“What are symbols?”, there should be some good info there too.

Personally, I felt a bit overwhelmed by Rails’ syntax when I first
started learning it, so I stepped back and spent some time with pure
Ruby, writing a series of small applications to get the feel of the
language. Once I was more comfortable with symbols, blocks, iterators,
and Ruby in general, then I felt more confident diving into Rails.

Jeff


#19

Wow. So basically, symbols are not variables and also not strings. They
can’t be set, but logically that seems like an invalid statement to me,
since we can use the :id symbol from a form, and that obviously has been
set, because it can return an integer like ‘5’ and not just ‘id’. If it
could only return ‘id’, it would be useless to any program unless that
program was a program about the two letters ‘i’ and ‘d’.

:slight_smile:

So symbols aren’t set, but they can be, like, ‘born’ (?) somehow or else
they just exist perpetually. But it seems unclear how to control them.
Holy jeez this is confusing. I think I’ll be investing in that Pickaxe
Book soon.


#20

You might want to look into the Pickaxe Book (“Programming Ruby” by Dave
Thomas), which covers basic Ruby concepts like variables and symbols.

Aha. For some reason I was under the impression that the :symbols were
unique to Rails. That’s probably exactly what I need - I’ll look into
it. Thanks.