Seth wrote:
>
> Michael,
>
> I'm really baffled by your response. Are we reading two different
> threads here? Or, perhaps you missed the main message in this thread
> outlining the issues with the suggestions you've given me so far?
hi Seth -
I was responding specifically to your example:
users = DBSession.query(Post.id.label('post_id'),
Post.user_id.label('user_id')).from_statement(Discussion.__table__.select().with_only_columns(['post_id',
'user_id']).union(DiscussionComment.__table__.select().with_only_columns(['post_id',
'user_id'])))
which to my eyes contains exactly one call to "union()", and exactly two
"select()" constructs. It is using the `from_statement()` method that I
recommended, which is described here:
http://www.sqlalchemy.org/docs/05/reference/orm/query.html#sqlalchemy.orm.query.Query.from_statement
and an example here:
http://www.sqlalchemy.org/docs/05/ormtutorial.html#using-literal-sql
note that the purpose of `from_statement()` is to bypass anything the
`Query` would generate normally, thereby granting the user full control
over the SQL generated using a completely hand-constructed SQL expression.
No SQL is generated outside of what was requested inside of
`from_statement()`.
You then asked why it produced the following SQL, which you claimed did
not include the "third" select:
SELECT post_id, user_id
FROM discussions UNION SELECT post_id, user_id
FROM discussion_comments
which again has exactly one "UNION" keyword, and two "SELECT" keywords,
and exactly matches what you asked for in your example code. This may be
the source of the confusion regarding "where did the third select go?"
To my eyes the statement produced exactly what you asked for. To yours,
you were using methods I suggested without fully understanding how they
are used (which may be anyone's fault here). I apologize that I do not
re-read an entire thread as soon as I see a single example where what the
author posts does not make sense to me, and as I answer easily 6-12
threads per day while working at my day job I do not memorize what the
original request was in most cases. It is most helpful if each
individual poster can continue to perform their own independent research
into the API documentation using responses from myself and others only as
clues of where to look next, rather than fully constructed solutions to
their exact issue.
> Let me reiterate: I am trying to perform a SELECT on t1, and then
> *two* UNION SELECTS (one on t2 and one on t3). The SQL should look
> like this:
>
> SELECT post_id, user_id FROM t1
> UNION SELECT post_id, user_id FROM t2
> UNION SELECT post_id, user_id FROM t3
if you would like three SELECT statements unioned together, you will note
that the CompoundSelect produced by `union()` does not itself have a
`union()` method with which to generate a set of three unions, since
things get confusing at that point as to who should be nesting what. So
you use the `union()` function instead. To expand my previous example:
from sqlalchemy import *
from sqlalchemy.orm import *
m = MetaData()
t = Table('t1', m,
Column('id', Integer, primary_key=True),
Column('data', String)
)
class C(object):
pass
mapper(C, t)
s = create_session()
q = s.query(C.id, C.data).from_statement(union(t.select(), t.select(),
t.select()))
print q
produces:
SELECT t1.id, t1.data
FROM t1 UNION SELECT t1.id, t1.data
FROM t1 UNION SELECT t1.id, t1.data
FROM t1
again I apologize for the misunderstanding and I hope this helps with your
issue.
- mike
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
