Another SQL problem

[HenriIV]

Hi,

I'm fighting to find how to translate alias column name into Rails:

SELECT * FROM properties as p 

the 'as p' alias doesn't seem to work like that:

   find(:all, 
      :select => 'properties.* as p', 
      :other conditions here 

Any idea to solve this?

[xmitchx]

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.*

[Luke Redpath]

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:

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)

[r937]

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

[HenriIV]

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

select p.id, p.name, p.ref, p.xxx 

is easier than

select properties.id, properties.name, properties.ref, properties.xxx 

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

:joins =>  'LEFT JOIN properties as p ON p.city_id = cities.id', 

not in the :select tag

[Mandibal]

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

: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

select p.* from properties as p 

Then you said you put this as your :select

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

[HenriIV]

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

[Mandibal]

I didn't realize there was a :from either. That should come in real handy.