Friday, 24 December 2021

How to set a M2M hybrid count property in SQLAlchemy?

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