Using :select in a find method

[Bil]

I'm trying to use the :select option in the find method, but I guess it's not doing what I think it should be doing.

	
find_options = {   	  
  :select => 'CONCAT(last_name, " ", ssn) AS last_name_ssn',        
  :conditions => [ "LOWER(last_name) LIKE ?", '%text%' ],         
  :order => "last_name_ssn ASC",        
  :limit => 10 }

@items = Person.find(find_options)

This gives me the error:

Couldn't find Person without an ID AND LOWER(last_name) LIKE '%text%'

Any ideas where I'm going wrong?

Perhaps I should just use find_by_sql?

[Luke Redpath]

As far as I can tell, the :select option lets you specify what goes between "SELECT" and "FROM" in the SQL statement. Its '*' by default. In your code above, you are changing it to "SELECT CONCAT(last_name, " ", ssn) AS last_name_ssn FROM..." which means you are leaving all of the other columns including the id column out (which is why Rails is complaining). You need to either leave it as the default '*' or put ALL of the columns you want in the :select option.

[Bil]

Thanks Luke, that makes sense. I ended up using find_by_sql, but I did want to try to get it working with :select. I tried these and still got errors, any ideas why?

:select => 'CONCAT(last_name, " ", ssn) AS last_name_ssn, *'
:select => 'CONCAT(last_name, " ", ssn) AS last_name_ssn, id'
:select => 'CONCAT(last_name, " ", ssn) AS last_name_ssn, id, last_name, ssn'

[Luke Redpath]

What error did you get?

Take a look in your development.log and see what SQL query its trying to execute if its made it that far.

[pergesu]

Thanks Luke, that makes sense. I ended up using find_by_sql, but I did want to try to get it working with :select. I tried these and still got errors, any ideas why?

:select => 'CONCAT(last_name, " ", ssn) AS last_name_ssn, *'
:select => 'CONCAT(last_name, " ", ssn) AS last_name_ssn, id'
:select => 'CONCAT(last_name, " ", ssn) AS last_name_ssn, id, last_name, ssn'

I think you would need
:select => '*, CONCAT(last_name, " ", ssn) AS last_name_ssn'

Not really sure though, so don't get mad if it doesn't work

Have you considered doing this operation in the model (which is where I think it belongs, after a glance at what you're doing).? In your User.rb file, add

def last_name_ssn
  lastname + " " + ssn
end

Then to get that info, you just invoke that method on a user object. my_user.last_name_ssn