How to filter columns in two tables with many-to-many relation?

Question

Hello,

I have this table:

channel_items = Table(
        "channel_items",
        metadata,
        Column("channel_id", Integer, ForeignKey("channels.id")),
        Column("media_item_id", Integer, ForeignKey("media_items.id"))
    )

class Channel(rdb.Model):
    """Set up channels table in the database"""
    rdb.metadata(metadata)
    rdb.tablename("channels")

    id = Column("id", Integer, primary_key=True)
    title = Column("title", String(100))

    items = relationship("MediaItem", secondary=channel_items, order_by="MediaItem.titleView", backref="channels")

class MediaItem(rdb.Model):
    """Set up items table in the database"""
    rdb.metadata(metadata)
    rdb.tablename("media_items")

    id = Column("id", Integer, primary_key=True)
    title = Column("title", String(100))
    type = Column("type", String(50))

I'd like to make a query, but filtering the second table. Something like:

channels = session.query(Channel).options(eagerload("item")).filter(MediaItem == "jpg").all()

Thanks in advance!

Answer 1

You just reference it through relation name. Also, its 'items' in your scheme, not 'item'.

channels = session.query(Channel).options(eagerload("items")).filter(Channel.items.type == "jpg").all()