Hi devs, I need to write a query where two tags match one product. I wrote simple test-case I'm using with sqlite:
---------------------------------------- #!/usr/bin/env python from sqlalchemy import * from sqlalchemy.sql import and_, or_ engine = create_engine("sqlite://", echo=True) metadata = MetaData(engine) TagTable = Table( "tag", metadata, Column("tag_id" , Integer , primary_key=True), Column("tag_name" , Unicode(128) , nullable=False) ) ProductTable = Table( "product", metadata, Column("product_id" , Integer , primary_key=True), Column("product_name" , Unicode(128) , nullable=False) ) TagToProductTable = Table( "tag_to_product", metadata, Column("tag_id" , Integer , ForeignKey(ProductTable.c.product_id), nullable=False, primary_key=True), Column("product_id" , Integer , ForeignKey(TagTable.c.tag_id), nullable=False, primary_key=True) ) if __name__ == '__main__': metadata.create_all() engine.execute(TagTable.insert(values={"tag_name": u"Tag 1"})) engine.execute(TagTable.insert(values={"tag_name": u"Tag 2"})) engine.execute(ProductTable.insert(values={"product_name": "Product A"})) engine.execute(ProductTable.insert(values={"product_name": "Product B"})) engine.execute(TagToProductTable.insert(values={"tag_id": 1, "product_id": 1})) engine.execute(TagToProductTable.insert(values={"tag_id": 2, "product_id": 1})) print "----------------------------------" result = engine.execute( select([ProductTable], select([func.count(TagToProductTable.c.tag_id)], or_(TagToProductTable.c.tag_id==1, TagToProductTable.c.tag_id==2) ).correlate(TagToProductTable) == 2 ) ).fetchall() print "----------------------------------" for item in result: print item.product_name metadata.drop_all() Current code will raise OperationalError: no such column: False Problem is that I not understand how to write the sub-select. I know that there are other solutions than sub-select, but I'd like to understand this method first. I'm using latest SqlAlchemy and Python. Any hint will be appreciated;) NOTE: I read the manual and API documentation, but it not helped me, is there some SQLA example where something like this is used? -- Best regards - Petr Kobalicek <http://kobalicek.com> -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.