SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot HenriIV's Avatar
    Join Date
    Jun 2004
    Location
    France
    Posts
    129
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Another SQL problem

    Hi,

    I'm fighting to find how to translate alias column name into Rails:
    PHP Code:
    SELECT FROM properties as 
    the 'as p' alias doesn't seem to work like that:
    PHP Code:
       find(:all
          :
    select => 'properties.* as p'
          :
    other conditions here 
    Any idea to solve this?

  2. #2
    SitePoint Evangelist
    Join Date
    Jun 2004
    Location
    California
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I may be wrong but I don't think you can do something.* as p you have to be specific with the column name such as something.name as p. I have never tried aliases with rails so I'm not sure how they work with ActiveRecord, but I have done them in queries in the past with other languages and I don't think you can do something.*
    Happy switcher to OS X running on a MacBook Pro.

    Zend Certified Engineer

  3. #3
    SitePoint Guru silver trophy Luke Redpath's Avatar
    Join Date
    Mar 2003
    Location
    London
    Posts
    794
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Its been a while since I've written raw SQL but I think the problem here is with your SQL, rather than Rails. You are trying to alias a column, so your SQL doesn't make sense. For example, if you wanted to alias a column you would do something like:

    PHP Code:
    SELECT address as a FROM properties 
    The SQL you've posted creates a table alias and I'm not really sure why you'd want to do that in Rails are you are dealing with your model, not the table directly.

    As for column aliases, I'm not sure Rails automatically creates model properites from column aliases - is this what you are trying to do? (but again, I'm not sure I can see why you'd want to)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, you can do something.*

    example: select a.*, b.* from a inner join b ... inner join c ...

    in this example, you want all columns from tables a and b, but none from c

    but something.* as p is wrong, because * cannot be given an alias
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot HenriIV's Avatar
    Join Date
    Jun 2004
    Location
    France
    Posts
    129
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The SQL you've posted creates a table alias and I'm not really sure why you'd want to do that in Rails
    In fact, I'm just pasting the query generated by mysql query browser software, the only benefit is to shorten queries when you have a lot of attributes
    PHP Code:
    select p.idp.namep.refp.xxx 
    is easier than
    PHP Code:
    select properties.idproperties.nameproperties.refproperties.xxx 
    The strange thing is that it seems to work when it's in a join:

    PHP Code:
    :joins =>  'LEFT JOIN properties as p ON p.city_id = cities.id'
    not in the :select tag

  6. #6
    Mal Reynolds Mandibal's Avatar
    Join Date
    Aug 2003
    Location
    Columbus
    Posts
    718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by HenriIV

    The strange thing is that it seems to work when it's in a join:

    PHP Code:
    :joins =>  'LEFT JOIN properties as p ON p.city_id = cities.id'
    not in the :select tag
    If you look back at your original post you'll see where the mistake is. You first posted that you wanted
    PHP Code:
    select p.* from properties as 
    Then you said you put this as your :select
    PHP Code:
    :select=>"select properties.* as p" 
    This is just incorrect sql syntax. The reason it works in the join is because your back to writing "properties as p" or "table_name as alias" instead of "column_list as alias" which is in effect what you put in the :select. Does that make more sense?
    Erh

  7. #7
    SitePoint Zealot HenriIV's Avatar
    Join Date
    Jun 2004
    Location
    France
    Posts
    129
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, I just needed another cup of coffee!
    it's ok with :from => 'properties as p' http://railsmanual.org/class/ActiveRecord::Base API didn't mention :from so it took me a while to understand

  8. #8
    Mal Reynolds Mandibal's Avatar
    Join Date
    Aug 2003
    Location
    Columbus
    Posts
    718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I didn't realize there was a :from either. That should come in real handy.
    Erh


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
  •