On Oct 9, 2008, at 10:20 AM, Wayne Witzel wrote:
> > I have the following code: > > tags = [u"tag1", u"tag2"] > tag_count = len(tags) > > inner_q = select([shiptag_table.c.shipid]) > inner_w = inner_q.where( > and_(shiptag_table.c.tagid == Tag.id,Tag.name.in_(tags)) > ).group_by > (shiptag_table.c.shipid).having(func.count(shiptag_table.c.shipid) > == tag_count) > > outer_q = select([Tag.id, Tag.name, > func.count(shiptag_table.c.shipid)]) > outer_w = outer_q.where( > and_(shiptag_table.c.shipid.in_(inner_w), > not_(Tag.name.in_(tags)), > Tag.id == shiptag_table.c.tagid) > ).group_by(shiptag_table.c.tagid) > > related_tags = meta.Session.execute(outer_w).fetchall() > return related_tags > > If I try to execute as is, I get the following error: > InvalidRequestError: Select statement 'SELECT shiptag.shipid > FROM shiptag, tag > WHERE shiptag.tagid = tag.id AND tag.name IN (:name_1) GROUP BY > shiptag.shipid > HAVING count(shiptag.shipid) = :count_1' returned no FROM clauses due > to auto-correlation; specify c > orrelate(<tables>) to control correlation manually. >>>> > > The inner_w select is claiming to not have any FROM clauses, though > they are clearly listed in the debug? the debug prints out the inner select statement non-nested in the outer statement, so you get the full list of from clauses. the func.count(shiptag_table.c.shipid) on the outer query sticks shiptag_table in the outer FROM, thus removing it from the inner query due to auto-correlation. you probably mean to say func.count(inner_q.c.shipid) instead so that the outer is selecting from the subquery, not the shiptag_table by itself (but then I see a lot of other references to shiptag_table there, so its not really clear what statement you're going for...). > > If I run the inner_w separate, not as a subselect, then feed the > results in to the in_ of outer_w it works. > I figure I have to be missing something obvious? > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---
