抱歉,如果不是“inverse”不是首选术语,那可能会妨碍我的搜索。无论如何,我要处理两个sqlalchemy声明性类,这是一个多对多关系。第一个是Account,第二个是Collection。用户“购买”了集合,但是我想显示用户尚未购买的前10个集合。
from sqlalchemy import *
from sqlalchemy.orm import scoped_session, sessionmaker, relation
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
account_to_collection_map = Table('account_to_collection_map', Base.metadata,
Column('account_id', Integer, ForeignKey('account.id')),
Column('collection_id', Integer, ForeignKey('collection.id')))
class Account(Base):
__tablename__ = 'account'
id = Column(Integer, primary_key=True)
email = Column(String)
collections = relation("Collection", secondary=account_to_collection_map)
# use only for querying?
dyn_coll = relation("Collection", secondary=account_to_collection_map, lazy='dynamic')
def __init__(self, email):
self.email = email
def __repr__(self):
return "<Acc(id=%s email=%s)>" % (self.id, self.email)
class Collection(Base):
__tablename__ = 'collection'
id = Column(Integer, primary_key=True)
slug = Column(String)
def __init__(self, slug):
self.slug = slug
def __repr__(self):
return "<Coll(id=%s slug=%s)>" % (self.id, self.slug)
因此,使用account.collections可以获得所有集合,并且使用dyn_coll.limit(1).all()可以将查询应用于集合列表...但是我该如何做逆运算呢?我想获取该帐户而不是映射的前10个收藏集。
任何帮助都令人感激。谢谢!
最佳答案
我不会为此目的使用该关系,因为从技术上讲,它不是您正在建立的关系(因此,在两侧保持同步等所有技巧都将失效)。
IMO,最干净的方法是定义一个简单的查询,该查询将返回您要查找的对象:
class Account(Base):
...
# please note added *backref*, which is needed to build the
#query in Account.get_other_collections(...)
collections = relation("Collection", secondary=account_to_collection_map, backref="accounts")
def get_other_collections(self, maxrows=None):
""" Returns the collections this Account does not have yet. """
q = Session.object_session(self).query(Collection)
q = q.filter(~Collection.accounts.any(id=self.id))
# note: you might also want to order the results
return q[:maxrows] if maxrows else q.all()
...
关于python - sqlalchemy多对多,但是相反吗?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/3983593/