SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Location
    Beautiful Rhode Island, USA
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Using :select in a find method

    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.

    Code:
    	
    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?

  2. #2
    SitePoint Guru silver trophy Luke Redpath's Avatar
    Join Date
    Mar 2003
    Location
    London
    Posts
    794
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Location
    Beautiful Rhode Island, USA
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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'

  4. #4
    SitePoint Guru silver trophy Luke Redpath's Avatar
    Join Date
    Mar 2003
    Location
    London
    Posts
    794
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    Grand Junction, CO
    Posts
    292
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 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'
    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

    Code:
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •