okay,
my select without sub-select looks like this:
f = func.count(TagToProductTable)
result = engine.execute(
select([ProductTable, TagToProductTable, f]).where(
and_(TagToProductTable.c.product_id == ProductTable.c.product_id,
or_(TagToProductTable.c.tag_id==1, TagToProductTable.c.tag_id==2))).\
group_by(ProductTable.c.product_id).\
having(f == 2)
).fetchall()
It does what I need, selects all products that have assigned tag with
id 1 and 2. If I find the sub-select solution I will paste it here.
The question is:
If I'm building select programatically and I have:
query = select([ProductTable])
...
How can I extend this query by the query shown above?
Thanks
- Petr
On Mon, Jun 28, 2010 at 9:40 AM, Petr Kobalíček
<kobalicek.p...@gmail.com> wrote:
> 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.
