On 5/27/15 2:43 AM, Nathan wrote:
Hi,
I'm trying to convert the following query to sqlalchemy:
|
SELECT cg.id,cg.namecg.status,history.action,history.timestampFROM
customergame cgJOIN history ON history.id=(SELECT idFROM history h
WHERE cg.id=h.customergame_idORDER BY timestamp DESC LIMIT 1)ORDER BY
gamename ASC
|
so the first thing to note, the subquery here is used in an ON
expression against the "=" operator. This means we would like it to act
like a column, not a set of rows, and is hence a "scalar subquery" (see
http://docs.sqlalchemy.org/en/rel_1_0/glossary.html#term-subquery)
Essentially to select the customergame entry and the last entry in the
history table for that customer game.
I've a number of ways to try and archive this, however, nothing so
seems to work. For example:
|
subq
=db.session.query(History.id).filter(Customergame.id==History.customergame_id).order_by(History.timestamp).limit(1).subquery()
|
which means, we don't want to use subquery(). We want to use
as_scalar() which has the subquery act like a column. A Core example
is here
http://docs.sqlalchemy.org/en/rel_1_0/core/tutorial.html#scalar-selects
but same idea for ORM.
Another thing to note, the correlation in the FROM clause is a little
non-standard (seems to be accepted by current PG/MySQL versions though),
but SQLAlchemy doesn't expect this to happen normally so we also have to
tell it to correlate that explicitly. Because I could see this part
causing problems I had to verify with a test script (which means you get
a working version for free!). It is attached.
|
db.session.query(Customergame).join(History,History.id==subq.c.id)
|
Which produces:
|
SELECT customergame.,
FROM customergame JOIN history ON history.id = anon_1.id
|
Which in turn leads to a exception when it executes:
|
ProgrammingError: (ProgrammingError) missing FROM-clause entry for
table "anon_1"
|
Has anybody got any ideas of how to achieve this type of query?
Many thanks in advance!
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class History(Base):
__tablename__ = 'history'
id = Column(Integer, primary_key=True)
cg_id = Column(ForeignKey('cg.id'))
data = Column(String(10))
class CG(Base):
__tablename__ = 'cg'
id = Column(Integer, primary_key=True)
data = Column(String(10))
e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.create_all(e)
s = Session(e)
subq = s.query(History.id).filter(CG.id == History.cg_id).\
order_by(History.data.desc()).limit(1).correlate(CG).as_scalar()
q = s.query(CG).join(History, History.id == subq).order_by(CG.data)
print q
q.all()
"""
SELECT cg.id, cg.name cg.status, history.action, history.timestamp
FROM customergame cg
JOIN history ON history.id =
(SELECT id FROM history h WHERE cg.id = h.customergame_id ORDER BY timestamp DESC LIMIT 1)
ORDER BY gamename ASC
SELECT cg.id AS cg_id, cg.data AS cg_data
FROM cg JOIN history ON history.id = (SELECT history.id
FROM history
WHERE cg.id = history.cg_id ORDER BY history.data
LIMIT %(param_1)s) ORDER BY cg.data
"""
