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 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.
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)