Forum: Ruby on Rails Find WHERE in Rails

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Sean C. (Guest)
on 2006-04-07 10:56
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.
Adam B. (Guest)
on 2006-04-07 11:31
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
Henrik N (Guest)
on 2006-04-07 11:35
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
Tom M. (Guest)
on 2006-04-07 11:36
(Received via mailing list)
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.
Jeff C. (Guest)
on 2006-04-07 11:37
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
Sean C. (Guest)
on 2006-04-07 12:22
Thank you so much everybody. Workin' like a charm now.
Sean C. (Guest)
on 2006-04-07 12:54
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?
Brian V. Hughes (Guest)
on 2006-04-07 17:47
(Received via mailing list)
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
Keith L. (Guest)
on 2006-04-07 19:49
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
Tom M. (Guest)
on 2006-04-07 21:58
(Received via mailing list)
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.
Sean C. (Guest)
on 2006-04-10 07:43
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.
Tom M. (Guest)
on 2006-04-10 07:47
(Received via mailing list)
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.
Sean C. (Guest)
on 2006-04-10 08:06
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!
Brian V Hughes (Guest)
on 2006-04-10 08:27
(Received via mailing list)
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. ;)

> 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. :) 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
Sean C. (Guest)
on 2006-04-10 08:56
>> 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. :) 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.
Sean C. (Guest)
on 2006-04-10 09:03
Ok.

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

is giving me

SyntaxError in <controller not set>#<action not set>
./script/../config/../app/controllers/test_controller.rb:26: parse
error, unexpected '=', expecting kEND
    :level = 2
            ^

Why can't I set :level in the controller?
Jeff C. (Guest)
on 2006-04-10 10:00
sean colquhoun wrote:
> Ok.
>
> :level = 2
> allquestions = Question.find_all_by_level(params[:level])
>
> is giving me
>
> SyntaxError in <controller not set>#<action not set>
> ./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
Sean C. (Guest)
on 2006-04-10 10:16
> 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.
Jeff C. (Guest)
on 2006-04-10 11:05
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
Sean C. (Guest)
on 2006-04-10 12:38
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'.

:-)

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.
Jeff C. (Guest)
on 2006-04-10 12:46
sean colquhoun wrote:
> 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'.
>
> :-)
>
> 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.

It's worth every penny. :)

Jeff
Calle D. (Guest)
on 2006-04-10 13:15
(Received via mailing list)
>>>>> "sean" == sean colquhoun <removed_email_address@domain.invalid> writes:

> Wow. So basically, symbols are not variables and also not strings.

Exactly. They are symbols. You can think of them as free-floating
names, in a way.

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

No, you have misunderstood here. Parameters from a form come to your
controller in the form of a hash. When you write params[:id], you're
just using the :id symbol as a key into that hash, and you get the
value 5 back. :id has no value other than :id, but the params hash has
a value keyed to the name :id. Do you see the difference?

> Holy jeez this is confusing. I think I'll be investing in that
> Pickaxe Book soon.

That would probably be an excellent idea, yes. Knowing Ruby makes it a
wee bit easier to program in Ruby, to put it mildly.

But if you really want to get into the concept and use of symbols in
programming languages, you should study Lisp :-)
--
		     Calle D. <removed_email_address@domain.invalid>
		 http://www.livejournal.com/users/cdybedahl/
     "Last week was a nightmare, never to be repeated - until this week"
				-- Tom, a.s.r
Sean C. (Guest)
on 2006-04-10 13:21
> But if you really want to get into the concept and use of symbols in
> programming languages, you should study Lisp :-)


Thanks. You cleared that up for  me. Unfortunately, however, I graduated
with a degree in graphic arts and the most challenging thing I've done
so far was program a mini-CMS for myself (ha ha ha-one page only-but it
does the trick) in PHP. OOP is something of a leap for me. i'm going to
have to take it one tongue at a time for the time being.

;-)
This topic is locked and can not be replied to.