asking for help with a database lookup

I want to retrieve objects based on two attributes - pay_period and
user. I have no problem with this if an exact pay_period and user are
selected. However, I want "All" to be an option for the two fields and
I don't know how to get my code to accept "All" as an option and then
act appropriately. Any help would be appreciated.

This is the code I have right now...
controller:
@sessions = Session.find_all_for_user(current_user.id,
params[:pay_period])

model:
  def self.find_all_for_user(user, pay_period)
    find(:all, :conditions => "user_id = #{user} AND pay_period_id =
#{pay_period}")
  end

Wouldn’t a user have many pay periods?

@user.pay_periods

I think you may be misunderstanding. I'm looking up Session objects
that have the attribute "user" and "pay_period"

Ok in that case:

Session.find_all_by_user_id_and_pay_period_id(user_id,pay_period_id) is already built into Rails.

Ryan Bigg wrote:

Ok in that case:

Session.find_all_by_user_id_and_pay_period_id(user_id,pay_period_id) is
already built into Rails.

Ryan,

I read your comment and I thought it was funny at first because I
thought you were playing around and then I realized how amazing Rails is
because I'm guessing the code above probably works.

Indeed it’s part of the “awesomeness” of Rails. It’ll let you do a find_by_field_1_and_field_2, but bad luck to you if one of your fields contains the word “and” or “or”.

But is there a way to pass something like ":all" into one of the
parameters? Or would I just have to use a different method - if
user_id = "all"; Session.find_all_by_pay_period_id(pay_period_id) ?

condition = true?

Session.find_all_by_user_id_and_pay_period_id(true,pay_period_id) ?

note: untested

Guess that doesn't work, you could make a self method which takes the
two parameters and checks for string "all"

If "all" is chosen for a parameter, how about substituting its column
name instead of the value? Something like

   :conditions => "user_id = #{@all_users ? 'user_id' : user_id} --etc

So that if @all_users was true, you'd end up with

    :conditions => "user_id = user_id"

which would give you all users.

Just an idea.

///ark

Mark - that seems to work. Thank you.

However, after Ryan Bigg posted the helper,
Session.find_all_by_user_id_and_pay_period_id(user_id,pay_period_id)
I'm curious as to whether this can be tweaked to accept "All" for one
or both of the parameters.

That method is dynamically generated by Rails, so there's nowhere to
tweak it. Rails has no builtin support for your query.

Just create your own method in the model, named what you like, using
the code I posted or something like it. Any trouble - just ask away.

///ark

Got it, thank you!

I want to retrieve objects based on two attributes - pay_period and
user. I have no problem with this if an exact pay_period and user are
selected. However, I want "All" to be an option for the two fields and
I don't know how to get my code to accept "All" as an option and then
act appropriately. Any help would be appreciated.

I prefer to use the empty string for this purpose rather than an
arbitrary magic value like "All". Then
you don't have to worry about someone having the user id 'all'.

In the view:

<select><option value="">All</option>...</select>

Then:

# Find sessions by user id and pay period.
# +user+: user id, or blank for all users
# +pay_period+: pay period, or blank for all pay periods

define self.find_all_for_user(user, pay_period)
  conditions = [[]]

  unless user.blank?
    conditions.push[user]
    conditions.first << 'user_id = '
  end

  unless pay_period.blank?
    conditions.push[pay_period]
    conditions.first << 'pay_period_id = '
  end
end

conditions.first = conditions.first.join(' AND ')

Session.find(:all, :conditions => conditions)

This is the code I have right now...
controller:
@sessions = Session.find_all_for_user(current_user.id,
params[:pay_period])

model:
  def self.find_all_for_user(user, pay_period)
    find(:all, :conditions => "user_id = #{user} AND pay_period_id =
#{pay_period}")

Be careful. This code is vulnerable to SQL injection attacks. Most
people prefer this:

  find(:all, :conditions => [ 'user_id = ? AND pay_period_id = ?',
user, pay_period_id ])

Ahh great.

However, what makes
find(:all, :conditions => "user_id = #{user} AND pay_period_id =
#{pay_period}")
vulnerable to SQL injection attacks? Especially since it's in the
model.

For details, see AWDWR 2nd Ed page 600 .. 604

Jeremy Weiskotten wrote:

If that code is vulnerable to SQL injection, then that project has
some very major problems! :slight_smile:

///ark

Well, it doesn't seem like there's that much code to change to go from
the insecure to secure method.

There isn't. And I would also use substitution just out of habit, if
nothing else. I was just pointing out that the particular code in
question could not reasonably have permitted an injection attack.

///ark