python - 从 SQLALCHEMY 中的多个连接表中使用 OR 和 AND 过滤属性会产生额外的实体

Question

如文档中所述，sqlalchemy“filter”和“filter_by”仅过滤“作为调用 Query.join() 目标的最后一个实体”

我想要一个像这样的查询：

SELECT sale.*
FROM sale JOIN sale_transactions ON sale.id = sale_transactions.sale_id 
JOIN customer ON customer.id = sale.customer_id 
JOIN product ON product.id = sale_transactions.product_id
WHERE (sale.description LIKE '%something%' OR product.name LIKE '%something%' OR customer.name LIKE '%something%')
AND sale.date >= 'someDate' AND sale.date <= 'someOtherDate'

从我拥有的模式中：

class Product(db.Model, ImageFunctions):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String())
    sale_transaction = db.relationship('SaleTransactions', backref='product', lazy='dynamic')

class Customer(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(), index=True)    
    sale = db.relationship('Sale', backref='customer', lazy='dynamic')

class Sale(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    date = db.Column(db.DateTime)
    customer_id = db.Column(db.Integer, db.ForeignKey('customer.id'))

    transactions = db.relationship('SaleTransactions', backref='sale', lazy='dynamic')

class SaleTransactions(db.Model):    
    sale_id = db.Column(db.Integer, db.ForeignKey('sale.id'), primary_key=True)
    product_id = db.Column(db.Integer, db.ForeignKey('product.id'), primary_key=True)
    product_qty = db.Column(db.Integer)

为此，我编写了以下代码：

query = Sale.query.join(Customer)
query = query.join(SaleTransactions, Product)
query = query.filter(Sale.description.like('%something%') | Product.name.like('%something%') | Customer.name.like('%something%'))
query = query.filter(Sale.date >= datetime.today(), Sale.date <= datetime.today())

但它会生成一个带有额外客户实体的查询，但没有任何连接，但 Product 并非如此，因为这是“连接”语句中的最后一个：

SELECT sale.*
FROM customer, sale JOIN sale_transactions ON sale.id = sale_transactions.sale_id 
JOIN customer ON customer.id = sale.customer_id 
JOIN product ON product.id = sale_transactions.product_id
WHERE (sale.description LIKE '%something%' OR product.name LIKE '%something%' OR customer.name LIKE '%something%')
AND sale.date >= 'someDate' AND sale.date <= 'someOtherDate'

有没有办法解决这个问题并且没有任何额外的实体？

标签： pythonjoinfiltersqlalchemyflask-sqlalchemy