I have two tables bound by a M2M relationship. Books and Writers, writers can have many books and books can have many writers.
I want to have a count
property on both books and writers so I could sort them by, for example, the writer who wrote the most books.
# many to many association table
book_writer_association_table = Table('book_writer_association',Base.metadata,
Column('book_id',ForeignKey('book.id'), primary_key=True),
Column('Writer',ForeignKey('writer.id'), primary_key=True)
)
class Book(Base):
__tablename__ = 'base'
id = Column(Integer, primary_key=True)
name = Column(String)
writers = relationship(Writer,secondary=book_writer_association_table,back_populates="books")
class Writer(Base):
__tablename__ = 'writer'
id = Column(Integer, primary_key=True)
name = Column(String)
books = relationship(Book,secondery=book_writer_association_table,back_populates="writers")
@hybrid_property
def book_count(self):
return len(self.books)
@book_count.expression
def book_count(cls):
#what goes here?
I tried various approaches like detailed here:
class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
bar_id = Column(Integer, ForeignKey('bar.id'))
bar = relationship('Bar')
class Bar(Base):
__tablename__ = 'bar'
id = Column(Integer, primary_key=True)
@hybrid_property
def foo_count(self):
return object_session(self).query(Foo).filter(Foo.bar==self).count()
@foo_count.expression
def foo_count(cls):
return select([func.count(Foo.id)]).where(Foo.bar_id == cls.id).label('foo_count')
However, in this example, there are only two tables and I'm unsure how to achieve a more complicated join here. Another user suggested using column_property
but I run into exactly the same problem there. I'm unsure how to further add tables to the join.
from How to set a M2M hybrid count property in SQLAlchemy?
No comments:
Post a Comment