'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 |
|---|
