Strange alias behaviour
naktinis
select_clause = union(some_table.select(...).column("'a' as type"),
some_table.select(...).column("'b' as
type"),
some_table.select(...).column("'c' as
type")).order_by('id')
which in SQL should look like:
"SELECT id, 'a' as type FROM some_table WHERE ...
UNION SELECT id, 'b' as type FROM some_table WHERE ...
UNION SELECT id, 'c' as type FROM some_table WHERE ... "
where "..." represents some condition
Then I want to use this query (select_clause) as subquery and group
everything by 'id' so i could skip the duplicate rows [(id: 1, type:
'a') is the same for me as (id: 1, type: 'b')]. So I do it like this:
new_select_clause = select_clause.alias('quux').select()
Which gives SQL like this:
"SELECT quux.id, quux.'a' as type FROM (SELECT id, 'a' as type FROM
table WHERE ...
UNION SELECT id, 'b' as type FROM table WHERE ...
UNION SELECT id, 'c' as type FROM table WHERE ... ) AS quux"
And it gives an SQL error, most probably because of the "quux.'a' as
type" part, which is not right syntax.
What I needed was:
"SELECT quux.id FROM (SELECT id, 'a' as type FROM some_table WHERE ...
UNION SELECT id, 'b' as type FROM some_table WHERE ...
UNION SELECT id, 'c' as type FROM some_table WHERE ... ) AS quux"
Which I assume would require some way to explicitly describe the
columns I want to select (and I need only ones that are in the
some_table)
Is there a way to get around this strange behaviour?
Michael Bayer
On Feb 26, 2008, at 3:56 PM, naktinis wrote:
>
> I have this select clause:
>
> select_clause = union(some_table.select(...).column("'a' as type"),
> some_table.select(...).column("'b' as
> type"),
> some_table.select(...).column("'c' as
> type")).order_by('id')
>
> which in SQL should look like:
> "SELECT id, 'a' as type FROM some_table WHERE ...
> UNION SELECT id, 'b' as type FROM some_table WHERE ...
> UNION SELECT id, 'c' as type FROM some_table WHERE ... "
>
> where "..." represents some condition
>
> Then I want to use this query (select_clause) as subquery and group
> everything by 'id' so i could skip the duplicate rows [(id: 1, type:
> 'a') is the same for me as (id: 1, type: 'b')]. So I do it like this:
>
> new_select_clause = select_clause.alias('quux').select()
>
> Which gives SQL like this:
> "SELECT quux.id, quux.'a' as type FROM (SELECT id, 'a' as type FROM
> table WHERE ...
> UNION SELECT id, 'b' as type FROM table WHERE ...
> UNION SELECT id, 'c' as type FROM table WHERE ... ) AS quux"
>
> And it gives an SQL error, most probably because of the "quux.'a' as
> type" part, which is not right syntax.
the reason "quux.'a' as type" is coming out there is because of the
way you defined that clause - as straight text which SA can't make
intelligent decisions about. You'll get better results if you say:
literal_column('a').label('type')
> What I needed was:
> "SELECT quux.id FROM (SELECT id, 'a' as type FROM some_table WHERE ...
> UNION SELECT id, 'b' as type FROM some_table WHERE ...
> UNION SELECT id, 'c' as type FROM some_table WHERE ... ) AS quux"
>
> Which I assume would require some way to explicitly describe the
> columns I want to select (and I need only ones that are in the
> some_table)
the next part here, is that someselectable.select() always spells out
all the columns of the given selectable. To take someselectable and
pull just some columns out, put the select() around it, i.e.
select([someselectable.c.id])
So with these two ideas, youd have:
from sqlalchemy import *
from sqlalchemy.sql import table, column
some_table = table('some_table', column('id'))
select_clause =
union(some_table.select().column(literal_column("'a'").label("type")),
some_table.select().column(literal_column("'b'").label("type")),
some_table
.select
().column
(literal_column("'c'").label("type"))).order_by('id').alias('quux')
print select([select_clause.c.id])
SELECT quux.id
FROM (SELECT some_table.id AS id, 'a' AS type
FROM some_table UNION SELECT some_table.id AS id, 'b' AS type
FROM some_table UNION SELECT some_table.id AS id, 'c' AS type
FROM some_table ORDER BY id) AS quux