so I am working through the cookbook many-to-many ORM example on zzzeek's blog. after executing, Base.metadata.tables looks like this:
{ 'cookbook': Table('cookbook', MetaData(None), Column('id', Integer(), table=<cookbook>, primary_key=True, nullable=False), Column('description', Text(length=None, convert_unicode=False, assert_unicode=None), table=<cookbook>), schema=None), 'cookbook_keyword': Table('cookbook_keyword', MetaData(None), Column('cookbook_id', Integer(), ForeignKey('cookbook.id'), table=<cookbook_keyword>), Column('keyword_id', Integer(), ForeignKey('keyword.id'), table=<cookbook_keyword>), schema=None), 'keyword': Table('keyword', MetaData(None), Column('id', Integer(), table=<keyword>, primary_key=True, nullable=False), Column('name', String(length=255, convert_unicode=False, assert_unicode=None), table=<keyword>), schema=None), 'recipe': Table('recipe', MetaData(None), Column('id', Integer(), table=<recipe>, primary_key=True, nullable=False), Column('description', Text(length=None, convert_unicode=False, assert_unicode=None), table=<recipe>), schema=None), 'recipe_keyword': Table('recipe_keyword', MetaData(None), Column('recipe_id', Integer(), ForeignKey('recipe.id'), table=<recipe_keyword>), Column('keyword_id', Integer(), ForeignKey('keyword.id'), table=<recipe_keyword>), schema=None) } the blog post shows some queries set up, all using just the Class declarations.. I added some data, this way myKwds = [] for nn in keywords: tk = Keyword() tk.description = nn Session.add(tk) Session.commit() myCookbook = Cookbook() myCookbook.description = "timely advice on legumes" Session.add(myCookbook) Session.commit() so now I should be able to use the many-to-many definition in Cookbook thus ? Session.query(Cookbook.keywords).all() but that generates only Session.query(Cookbook.keywords).all() 2009-08-30 16:52:26,398 INFO sqlalchemy.engine.base.Engine.0x...cecc SELECT cookbook.id AS cookbook_id, cookbook.description AS cookbook_description FROM cookbook 2009-08-30 16:52:26,399 INFO sqlalchemy.engine.base.Engine.0x...cecc [] Out[62]: [(1, u'timely advice on legumes')] that doesnt look right to me.. nothing about the many-to-many join ??? how else ought I be able to use this setup? == #!/usr/bin/python from sqlalchemy import * from sqlalchemy.orm import * engine = create_engine('sqlite://', echo=True) Session = scoped_session(sessionmaker(bind=engine)) from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class Keyword(Base): __tablename__ = "keyword" id = Column(Integer, primary_key=True) name = Column(String(255)) Base.metadata.create_all(engine) keywords = ["beans", "lentils", "legumes"] # print Session.query(func.count(Keyword.id)).filter(Keyword.name.in_ (keywords)) # print Session.query(exists().where(Keyword.name.in_(keywords))) recipe_keywords = Table("recipe_keyword", Base.metadata, Column("recipe_id", Integer, ForeignKey ("recipe.id")), Column("keyword_id", Integer, ForeignKey ("keyword.id")) ) cookbook_keywords = Table("cookbook_keyword", Base.metadata, Column("cookbook_id", Integer, ForeignKey ("cookbook.id")), Column("keyword_id", Integer, ForeignKey ("keyword.id")) ) class Recipe(Base): __tablename__ = "recipe" id = Column(Integer, primary_key=True) description = Column(Text) keywords = relation(Keyword, secondary=recipe_keywords) class Cookbook(Base): __tablename__ = "cookbook" id = Column(Integer, primary_key=True) description = Column(Text) keywords = relation(Keyword, secondary=cookbook_keywords) Base.metadata.create_all(engine) ##------------------------------------------------------- ## test ## spell out the join explicitly ##(ret, ), = Session.query( ## exists().where(Recipe.id==recipe_keywords.c.recipe_id). ## where(recipe_keywords.c.keyword_id==Keyword.id). ## where(Keyword.name.in_(keywords)) ##) ## use ORM join() (ret, ), = Session.query( exists().select_from(join(Recipe, Keyword, Recipe.keywords)). where(Keyword.name.in_(keywords)) ) ## use ORM join(), multipe search criteria (ret, ), = Session.query(or_( exists().select_from(join(Recipe, Keyword, Recipe.keywords)). where(Keyword.name.in_(keywords)), exists().select_from(join(Cookbook, Keyword, Cookbook.keywords)). where(Keyword.name.in_(keywords)) )) ## use fancy any().. may have gotchas... ## rows = Session.query(Recipe).filter(Recipe.keywords.any (Keyword.name.in_(keywords))).all() --~--~---------~--~----~------------~-------~--~----~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---