I have this case with a bundle that looks something like this:
primate_bundle = Bundle(
'primate',
Primate.name,
Bundle('wooden_tool', *[
WoodenTool.id,
WoodenTool.name,
WoodenToolCategory.name.label('category'),
]),
Bundle('solid_tool', *[
SolidTool.id,
SolidTool.name,
SolidToolCategory.name.label('category'),
])
)
Then I query it like this:
session.query(primate_bundle)
.select_from(Primate)
.join(WoodenTool, Primate.main_tool)
.join(WoodenToolCategory, WoodenTool.category_id == WoodenToolCategory.id)
.join(SolidTool, Primate.secondary_tool)
.join(SolidToolCategory, SolidTool.category_id == SolidToolCategory.id)
.all()
However, since the label for category name is the same within both
sub-bundles it will throw Ambiguous column name (because the compiled SQL
labels will be exactly the same). Adding .with_labels() doesn’t fix it.
Full traceback can be seen by running the included examples. Commenting out
one of the .label() lines in the example makes it runnable. Do you guys
have a clean solution to support this use case? I really like this feature
of creating your own custom made results so it would be a shame to not be
able to do this.
Tested on SQLAlchemy 1.0.0b5 and 0.9.9. Python 3.
Thank you so much for any potential help you can give me on this. I’ve
followed the source code for Bundle but I can’t think of a clean way to
this…
--
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/d/optout.
import sqlalchemy as sa
from sqlalchemy.orm import Session, relationship, aliased, Bundle
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
engine = sa.create_engine('sqlite:///')
session = Session(bind=engine)
class ToolCategory(Base):
__tablename__ = 'toolcategory'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.Text)
class Tool(Base):
__tablename__ = 'tool'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.Text)
category_id = sa.Column(sa.Integer, sa.ForeignKey(ToolCategory.id))
category = relationship(ToolCategory)
class Primate(Base):
__tablename__ = 'primate'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.Text)
main_tool_id = sa.Column(sa.Integer, sa.ForeignKey(Tool.id))
main_tool = relationship(Tool, foreign_keys=[main_tool_id])
secondary_tool_id = sa.Column(sa.Integer, sa.ForeignKey(Tool.id))
secondary_tool = relationship(Tool, foreign_keys=[secondary_tool_id])
category = ToolCategory(name='physical')
stick = Tool(name='Stick', category=category)
stone = Tool(name='Stone', category=category)
chimpanzee = Primate(name='Chimpanzee', main_tool=stick, secondary_tool=stone)
Base.metadata.create_all(engine)
session.add(chimpanzee)
WoodenTool = aliased(Tool)
WoodenToolCategory = aliased(ToolCategory)
SolidTool = aliased(Tool)
SolidToolCategory = aliased(ToolCategory)
primate_bundle = Bundle(
'primate',
Primate.name,
Bundle('wooden_tool', *[
WoodenTool.id,
WoodenTool.name,
# Remove .label('category') to get rid of "Ambiguous column name" error
WoodenToolCategory.name.label('category'),
]),
Bundle('solid_tool', *[
SolidTool.id,
SolidTool.name,
SolidToolCategory.name.label('category'),
])
)
query = (
session.query(primate_bundle)
.select_from(Primate)
.join(WoodenTool, Primate.main_tool)
.join(WoodenToolCategory, WoodenTool.category_id == WoodenToolCategory.id)
.join(SolidTool, Primate.secondary_tool)
.join(SolidToolCategory, SolidTool.category_id == SolidToolCategory.id)
# Applying `with_labels()` doesn't fix it...
# .with_labels()
)
print(query)
results = query.all()
print(results)
