sqlachemy：按关系过滤（如Django orm）？ [英] sqlachemy: filter by relationship (like django orm)?

问题描述

在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 = models.TextField()

class Sale(models.Model):
    sale_id = models.IntegerField(primary_key=True)
    timestamp = models.DateTimeField()
    product = models.ForeignKey(Product, on_delete=models.CASCADE)

您可以

Sale.objects.filter(product__color__in=['red', 'blue'])

或者反之亦然

Product.objects.filter(sale__timestamp__gt=datetime.now())

什么是在sqlalchemy中执行此操作的正确方法，无需显式JOIN s ??

What is the proper way of doing this in sqlalchemy, without explicit JOINs??

推荐答案

您可以使用< a href = https://docs.sqlalchemy.org/zh_CN/latest/orm/tutorial.html#using-exists rel = nofollow noreferrer> any（）和 has（） 来过滤bas关于非标量和标量关系。它们产生EXISTS子查询表达式：

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()))

和

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

不幸的是，与使用显式联接相比，在某些DBMS上，EXISTS子查询表达式的性能可能较差。

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

这篇关于sqlachemy：按关系过滤（如Django orm）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！