'outerjoin which returns all joined rows in right table if at least one matches a condition

I have two mysql tables,

class Item(Base):
    id = Column(BigInteger, primary_key=True)
    updated_at = Column(DateTime, nullable=False)

class ItemVariant(Base):
    id = Column(BigInteger, primary_key=True)    
    item_id = Column(BigInteger(), nullable=False, index=True)
    updated_at = Column(DateTime, nullable=False)

    @declared_attr
    def item(self):
        return relationship(
            'Item',
            lazy='select',
            uselist=False,
            primaryjoin=lambda: foreign(self.item_id) == remote(Item.id),
            backref=backref('variants', uselist=True)
        )

ItemVariants do not necessarily need to exist for every Item. I intentionally and explicitly do not want to add foreign key constraints.

I'd like to select (item, [list of item_variants with item_variant.item_id == item.id]), with either item.updated_at > some_date or any one of item_variant.update_at > some_date.

A query like

session.query(
  *my_columns_with_aliases
).select_from(
  Item
).filter(
  or_(
    Item.updated_at > some_date,
    ItemVariant.updated_at > some_date
  )
).outerjoin(
  ItemVariant, ItemVariant.item_id == Item.id
)

doesn't work, because if I have item_1 with var_1, var_2, and only var_1.updated_at matches the condition, var_2 won't be included in the query results.

I've also tried doing a second aliased outerjoin for the filtering (using the non-aliased table for the selects), but this returns too many results.

Is there a standard solution for "getting all variants if at least one matches the condition"?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source