炼金术中奇怪的过滤行为

Question

我在flask-sqlalchemy中有一个查询，filter的行为很奇怪：

q.filter(Transaction.transaction_id == ReconciledTransaction.safe_withdraw_id).all()

工作正常，但是：

q.filter(Transaction.transaction_id != ReconciledTransaction.safe_withdraw_id).all()

工作不正常！有什么问题吗？
升级版 我的模型：
对账交易模型：

class ReconciledTransactionModel(db.Model):
    """Reconciled Transaction model"""

    __tablename__ = 'ReconciledTransaction'

    id = db.Column('id', db.Integer, primary_key=True, nullable=False)
    balance_entry_id = db.Column('BalanceEntry_id', db.Integer, db.ForeignKey("BalanceEntry.id"), nullable=False)
    safe_withdraw_id = db.Column('Transaction_id', db.String, nullable=False)
    datetime = db.Column('datetime', db.Date(), nullable=False)
    balance_entry_amount = db.Column('BalanceEntry_amount', db.Float)
    reconciled_amount = db.Column('ReconciledAmount', db.Float)
    currency = db.Column('currency', db.String)
    reconciliation_status = db.Column('reconciliation_status', db.String, nullable=False)
    status_code = db.Column('status_code', db.Integer, nullable=False)

交易模式：

class TransactionModel(db.Model):
    """Transaction SA model."""

    __tablename__ = 'Transaction'

    id = db.Column('id', db.Integer, primary_key=True)
    till_id = db.Column('Till_id', db.Integer, db.ForeignKey("Till.id"),
                        nullable=False)
    till = relationship("Till", foreign_keys=[till_id], backref="transactions", enable_typechecks=False)
    establishment_id = db.Column('Establishment_id', db.Integer,
                                 db.ForeignKey("Establishment.id"),
                                 nullable=False)
    establishment = relationship("Establishment",
                                 foreign_keys=[establishment_id],
                                 backref="transactions",
                                 enable_typechecks=False)
    employee_id = db.Column('Employee_id', db.Integer,
                            db.ForeignKey("Employee.id"),
                            nullable=False)
    employee = relationship("Employee",
                            foreign_keys=[employee_id],
                            backref="transactions",
                            enable_typechecks=False)
    local_time = db.Column('local_time', db.DateTime, nullable=False)
    create_time = db.Column('create_time', db.TIMESTAMP(timezone=True),
                            nullable=False)
    send_time = db.Column('send_time', db.TIMESTAMP(timezone=True),
                          nullable=False)
    receive_time = db.Column('receive_time', db.TIMESTAMP(timezone=True),
                             nullable=False)
    total_value = db.Column('total_value', db.Integer, nullable=False)
    amount = db.Column('amount', db.Float, nullable=False)
    discrepancy = db.Column('discrepancy', db.Float, nullable=False)
    type = db.Column('type', db.Enum('shift',
                                     'payment',
                                     'skimming',
                                     'withdraw',
                                     'refund',
                                     'till',
                                     'till_deposit',
                                     'safe_deposit',
                                     'safe_withdraw',
                                     'till_reset',
                                     name='transaction_type'),
                     nullable=False)
    status = db.Column('status',
                       db.Enum('start', 'end', name='transaction_status'),
                       nullable=False)
    receipt_id = db.Column('receipt_id', db.String(32), server_default=None)
    transaction_id = db.Column('transaction_id', db.String(32),
                               server_default=None)
    parent_transaction = db.Column('parent_transaction', db.String(32),
                                   server_default=None)
    discrepancy_reason = db.Column('discrepancy_reason', db.String(1024))
    resolve_discrepancy_reason = db.Column('resolve_discrepancy_reason',
                                           db.String(1024))
    accounted = db.Column('accounted', db.Boolean, default=False)

我的问题是：

_transactions = db.session.query(Transaction,
                                 status_sq.c.count,
                                 end_transaction_sq.c.discrepancy,
                                 end_transaction_sq.c.discrepancy_reason,
                                 end_transaction_sq.c.resolve_discrepancy_reason,
                                 end_transaction_sq.c.amount,
                                 ). \
    filter(Transaction.establishment_id.in_(store_ids)). \
    filter(Transaction.amount != 0). \
    filter_by(status='start')

transactions = _transactions. \
    filter(Transaction.type.in_(transaction_types)). \
    outerjoin(status_sq,
              Transaction.transaction_id == status_sq.c.transaction_id). \
    outerjoin(end_transaction_sq,
              Transaction.transaction_id == end_transaction_sq.c.transaction_id)

# check possible values for sorting and pages
if sort_field not in allowed_sort_fields:
    sort_field = Transaction.default_sort_field
if sort_dir not in (ASCENDING, DESCENDING):
    sort_dir = Transaction.default_sort_dir
if per_page > 100:  # hard limit
    per_page = Transaction.default_per_page

if sort_dir == ASCENDING:
    order = allowed_sort_fields[sort_field].desc()
else:
    order = allowed_sort_fields[sort_field].desc()

q = transactions.\
    join(Establishment).\
    join(Employee, Transaction.employee_id == Employee.id). \
    outerjoin(Currency). \
    group_by(Transaction,
             status_sq.c.count,
             end_transaction_sq.c.discrepancy,
             end_transaction_sq.c.discrepancy_reason,
             end_transaction_sq.c.resolve_discrepancy_reason,
             end_transaction_sq.c.amount,
             allowed_sort_fields[sort_field]).\
    order_by(order)
items = q.filter(Transaction.transaction_id == ReconciledTransaction.safe_withdraw_id).limit(per_page).offset((page - 1) * per_page).all()

“不能正常工作”意味着在第二种情况下（当我放置!=，并且只想获取事务，这些事务不在协调的事务表中）过滤器被忽略，但是当过滤器包含==时，所有都正常工作（我只有匹配的事务）。你知道吗

Answer 1

使用以下查询时：

q = db.session.query(Transaction). \
    filter(Transaction.transaction_id != ReconciledTransaction.safe_withdraw_id)

转换为SQL查询：

SELECT Transaction.* FROM Transaction, ReconciledTransaction
WHERE Transaction.transaction_id != ReconciledTransaction.safe_withdraw_id

这意味着您将获得所有具有所有已协调事务行的所有事务行，但具有匹配ID的事务行除外。你知道吗

如果需要获取不在协调事务表中的所有事务对象，可以首先获取所有协调事务ID：

r_query = db.session.query(ReconciledTransaction.safe_withdraw_id). \
    group_by(ReconciledTransaction.safe_withdraw_id)
r_ids = [x[0] for x in r_query]

然后在事务查询中使用NOT IN filter：

q = q.filter(Transaction.transaction_id.notin_(r_ids))

也可以使用子查询：

q = q.filter(Transaction.transaction_id.notin_(
    db.session.query(ReconciledTransaction.safe_withdraw_id)
))

编辑：正如Ilja Everilästated NOT EXISTS operator performancemight be betterthan NOT IN。SQLAlchemy查询将如下所示：

q = q.filter(~session.query(ReconciledTransaction). \
    filter(ReconciledTransaction.safe_withdraw_id == Transaction.id).exists())