Somewhat complex union_all() question

[Seth]

I have three different types of post tables with all of the same
columns except that the first table has an extra column named
"type" (placed in-between the 'user_id' and 'title' columns of the
other tables). I want to do a UNION ALL that will combine the data
from all these tables into a single list (with a blank value for the
tables missing the "type" column), and then I want to sort the posts
by creation date.

However, try as I might, I cannot seem to get this to work without
throwing me an ArgumentError of "All selectables passed to
CompoundSelect must have identical numbers of columns; select #1 has 7
columns, select #2 has 6". I suspect this has something to do with
SQLAlchemy auto-magically reading the DeclarativeBase table classes.


The SQL version of this query would look something like:

SELECT 'cat1' as category, * FROM posts_1 UNION ALL SELECT 'cat2', id,
user_id, 'NONE', title, body, created, updated FROM posts_2 UNION ALL
SELECT 'cat3', id, user_id, 'NONE', title, body, created, updated
FROM posts3 ORDER BY created DESC LIMIT 10

How can I get this to translate into SQLAlchemy with the extra 'NONE'
for the tables that don't have a "type" column without giving me the
ArgumentError?

Thanks,
Seth

[Mike Conley]

Did you try something like this?

q1=session.query(P1.userid, P1.extra, P1.title, P1.body)
q2=session.query(P2.userid, "'X'", P2.title, P2.body)
q3=session.query(P3.userid, "'X'", P3.title, P3.body)

q=q1.union_all(q2).union_all(q3)


--
Mike Conley

[Mike Conley]

or should be a little better

q=q1.union_all(q2,q3)

--
Mike Conley

[Seth]

Mike,

Very helpful! Seems to work great.

Wanna give me a tip on how to make the user's table join to this query
via the user_id so that I can access the user's information at
something like q.user.email?

Seth

On Aug 26, 9:22 pm, Mike Conley <mconl...@gmail.com> wrote:
> or should be a little better
>
> q=q1.union_all(q2,q3)
>
> --
> Mike Conley
>

> On Thu, Aug 27, 2009 at 12:14 AM, Mike Conley <mconl...@gmail.com> wrote:
> > Did you try something like this?
>
> > q1=session.query(P1.userid, P1.extra, P1.title, P1.body)
> > q2=session.query(P2.userid, "'X'", P2.title, P2.body)
> > q3=session.query(P3.userid, "'X'", P3.title, P3.body)
>
> > q=q1.union_all(q2).union_all(q3)
>
> > --
> > Mike Conley
>

[Mike Conley]

Assuming a declarative based class USER exists, then you can join each of the queries q1, q2, q3 to USER like this:

q1 = session.query(P1.userid,P1.extra,P1.title,P1.body,USER.email)
q1 = q1.join((USER,USER.userid==P1.userid))
q2 = session.query(P2.userid,"'X'",P2.title,P2.body,USER.email)
q2 = q2.join((USER,USER.userid==P2.userid))
q3 = session.query(P3.userid,"'X'",P3.title,P3.body,USER.email)
q3 = q3.join((USER,USER.userid==P3.userid))

q=q1.union_all(q2,q3)

Not a very elegant solution, and probably leads to an inefficient query plan in many databases.

Can anyone tell us how to join the result of union_all with another table? Probably a subquery()?

Effectively:
-  create q1, q2, q3 as selects from P1, P2, P# as in original solution
-  combine q1, q2, q3 with a union_all()
-  add column USER.email to the query
-  join resulting query to USER based on userid column in the union_all statement

SQL would look something like this:

SELECT qry.a, qry.b, qry.c, user.x
FROM (SELECT a,b,c FROM p1
UNION ALL SELECT a,b,c FROM p2
UNION ALL SELECT a,b,c FROM p3) as qry
JOIN USER on qry.a = USER.a

but I can't seem to get this result in SQLAlchemy


--
Mike Conley

[Mike Conley]

OK, I can mostly answer my own question

q1=session.query(P1.userid,P1.extra,P1.title,P1.body)
q2=session.query(P2.userid,"'X'",P2.title,P2.body)
q3=session.query(P3.userid,"'X'",P3.title,P3.body)
subq=q1.union_all(q2,q3).subquery()
q = session.query(USER.email, subq).join((subq, USER.userid==subq.c.userid))

gives the desired SQL



But what if I have a real requirement to retrieve the email address last in the row?

q = session.query(subq,USER.email).join((USER, USER.userid==subq.c.userid))
and
q = session.query(subq,USER.email).join((subq, USER.userid==subq.c.userid))

both complain
AttributeError: 'NoneType' object has no attribute 'base_mapper'

Probably because the subq is first in the list and is not an entity

--
Mike Conley

[Seth]

Mike,

Thanks again for your posts. What about something like:


q1 = DBSession.query(P1.id, P1.user_id, P1.type, P1.title, P1.body,
P1.created, P1.updated, User.name).filter(P1.user_id==User.id)
q2 = DBSession.query(P2.id, P2.user_id, "'P2'", P2.title, P2.body,
P2.created, P2.updated, User.name).filter(P2.user_id==User.id)
q3 = DBSession.query(P3.id, P3.user_id, "'P3'", P3.title, P3.body,
P3.created, P3.updated, User.name).filter(P3.user_id==User.id)

posts = q1.union_all(q2, q3)

?

Seth

On Aug 27, 2:45 pm, Mike Conley <mconl...@gmail.com> wrote:
> OK, I can mostly answer my own question
>
> q1=session.query(P1.userid,P1.extra,P1.title,P1.body)
> q2=session.query(P2.userid,"'X'",P2.title,P2.body)
> q3=session.query(P3.userid,"'X'",P3.title,P3.body)
> subq=q1.union_all(q2,q3).subquery()
> q = session.query(USER.email, subq).join((subq, USER.userid==subq.c.userid))
>
> gives the desired SQL
>
> But what if I have a real requirement to retrieve the email address last in
> the row?
>
> q = session.query(subq,USER.email).join((USER, USER.userid==subq.c.userid))
> and
> q = session.query(subq,USER.email).join((subq, USER.userid==subq.c.userid))
>
> both complain
> AttributeError: 'NoneType' object has no attribute 'base_mapper'
>
> Probably because the subq is first in the list and is not an entity
>
> --
> Mike Conley
>

[Mike Conley]

It works and will probably be OK. Using this style (query for user multiple times) in a really big database could lead to a performance problem depending on how the underlying database engine constructs a query plan. Try it and see how it goes.

--
Mike Conley

[Seth]

Ok Mike,

Tell me what you think about this:

q1 = DBSession.query(P1.id, P1.user_id, P1.type, P1.title, P1.body,

P1.created, P1.updated)

q2 = DBSession.query(P2.id, P2.user_id, "'P2'", P2.title, P2.body,

P2.created, P2.updated)

q3 = DBSession.query(P3.id, P3.user_id, "'P3'", P3.title, P3.body,

P3.created, P3.updated)

subquery = DBSession.query().from_statement(union_all(q1, q2,
q3)).subquery()

posts = DBSession.query(subquery, User.name).filter
(User.user_id==subquery.c.user_id)


Kind of hackish, but... it seems to work?

Seth

[Mike Conley]

Nothing hackish about it. The SQL is doing exactly what you want; union the posting tables and join the result to users. Simple enough that the database engine should construct a reasonable plan.

--
Mike Conley

[Michael Bayer]

On Sep 9, 2009, at 8:41 PM, Seth wrote:

>
> Ok Mike,
>
> Tell me what you think about this:
>
> q1 = DBSession.query(P1.id, P1.user_id, P1.type, P1.title, P1.body,
> P1.created, P1.updated)
> q2 = DBSession.query(P2.id, P2.user_id, "'P2'", P2.title, P2.body,
> P2.created, P2.updated)
> q3 = DBSession.query(P3.id, P3.user_id, "'P3'", P3.title, P3.body,
> P3.created, P3.updated)
>
> subquery = DBSession.query().from_statement(union_all(q1, q2,
> q3)).subquery()
>
> posts = DBSession.query(subquery, User.name).filter
> (User.user_id==subquery.c.user_id)
>
>
> Kind of hackish, but... it seems to work?

you should be able to call select() directly on the union_all() and
send that as your subquery.

[Mike Conley]

What would that look like? I don't get it the syntax.

This is also really close to answering the question I posted earlier about labeling literals in the first query of a union. I'll reply to that thread again when I have the answer completed.

--
Mike Conley

[Mike Conley]

here is the sample code I am using
   http://pastebin.com/m6cd9c5dd

--
Mike Conley