Sqlachemy: Filter By Relationship (like Django Orm)?

In django ORM you can directly filter by relationship attributes. Eg, given the tables class Product(models.Model): product_id = models.IntegerField(primary_key=True) color

Solution 1:

You can use any() and has() to filter based on non-scalar and scalar relationships. They produce EXISTS subquery expressions:

session.query(Product).filter(Product.sales.any(Sale.timestamp > datetime.now()))

and

session.query(Sale).filter(Sale.product.has(Product.color.in_(['red', 'blue'])))

Unfortunately on some DBMS the EXISTS subquery expressions may perform poorly, compared to using explicit joins.