Hi,
I am working on a function that extends an existing 'Query' object with an
additional subquery.
This is done via a join of existing query and the additional one.
My problem is that either the result of the combined query returns a list of
values instead of (table) objects
or the query contains unnecessary select-from-statements.
Example code
--------------------------------------------------------------------------------------------------------------------------from
sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer, String
Base = declarative_base()
class TableOne(Base):
__tablename__ = 'table_one'
id = Column(Integer, primary_key=True)
name = Column(String)
class TableTwo(Base):
__tablename__ = 'table_two'
id = Column(Integer, primary_key=True)
f_id = Column(Integer, ForeignKey(TableOne.id))
description = Column(String)
# initialization
engine = create_engine('sqlite://')
Base.metadata.create_all(bind=engine)
Session = sessionmaker(bind=engine)
session = Session()
session.add_all([
TableOne(id=1, name='some name'),
TableTwo(id=1, f_id=1, description='some description')])
# -------------------------------
existing_query = session.query(TableOne).subquery()
additional_query = session.query(TableTwo).subquery()
combined_query = session.query(existing_query).join(additional_query)
print combined_query.all()[0]
# out: (1, u'some name')
# -------------------------------
combined_query = session \
.query(TableOne) \
.select_from(combined_query.subquery().select())
result = combined_query.all()
print combined_query.all()[0]
print combined_query
# out: <__main__.TableOne object at 0x13224d0>
--------------------------------------------------------------------------------------------------------------------------
The 'First Query' looks like this.
SELECT anon_1.id AS anon_1_id, anon_1.name AS anon_1_name
FROM (
SELECT table_one.id AS id, table_one.name AS name
FROM table_one) AS anon_1
JOIN (
SELECT table_two.id AS id, table_two.f_id AS f_id, table_two.description AS
description
FROM table_two) AS anon_2
ON anon_1.id = anon_2.f_id
That is also what I what, but it returns the list of values.
The 'Second Query' looks like this
SELECT anon_1.id AS anon_1_id, anon_1.name AS anon_1_name
FROM (
SELECT anon_2.id AS id, anon_2.name AS name
FROM (
SELECT anon_3.id AS id, anon_3.name AS name
FROM (
SELECT table_one.id AS id, table_one.name AS name
FROM table_one) AS anon_3
JOIN (
SELECT table_two.id AS id, table_two.f_id AS f_id,
table_two.description AS description
FROM table_two) AS anon_4
ON anon_3.id = anon_4.f_id
) AS anon_2)
AS anon_1
As you see it contains some unnecessary statements,
but it returns a list of objects (TableOne) that I need.
What is the correct way to join two subqueries?
Thanks in advance.
Chris
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer, String
Base = declarative_base()
class TableOne(Base):
__tablename__ = 'table_one'
id = Column(Integer, primary_key=True)
name = Column(String)
class TableTwo(Base):
__tablename__ = 'table_two'
id = Column(Integer, primary_key=True)
f_id = Column(Integer, ForeignKey(TableOne.id))
description = Column(String)
# initialization
engine = create_engine('sqlite://')
Base.metadata.create_all(bind=engine)
Session = sessionmaker(bind=engine)
session = Session()
session.add_all([
TableOne(id=1, name='some name'),
TableTwo(id=1, f_id=1, description='some description')])
# -------------------------------
existing_query = session.query(TableOne).subquery()
additional_query = session.query(TableTwo).subquery()
combined_query = session.query(existing_query).join(additional_query)
print combined_query.all()[0]
# out: (1, u'some name')
# -------------------------------
combined_query = session \
.query(TableOne) \
.select_from(combined_query.subquery().select())
result = combined_query.all()
print combined_query.all()[0]
print combined_query
# out: <__main__.TableOne object at 0x13224d0>
