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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
