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