Strange filter behavior in flask-sqlalchemy

Question

I have a query in flask-sqlalchemy and filter is behaving strange:

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

It works fine, but:

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

Doesn't work correctly! What seems to be the problem?
UPD
My models:
Reconciled transaction model:

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)

Transaction Model:

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)

And here is my query:

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

'Doesn't work correctly' means that in second case(when I place !=, and wanna take transactions only, which are not in ReconciledTransaction table) filter gets ignored, but when filter contains ==, all works correctly(I have only matched transactions).

First of all you're not providing a [mcve]. What's `q` exactly? What about your models? What does "doesn't work correctly" even mean? Are you getting unexpected results? Include some sample data, then, so that others may explore. — Ilja Everilä, Sep 27 '17 at 06:18

Sergey Shubin · Accepted Answer · 2017-09-27T10:21:37.253

When you use query like this:

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

it transforms into SQL query:

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

which means you will get all Transaction rows with all ReconciledTransaction rows except those with matching ids.

If you need to get all Transaction objects which are not in ReconciledTransaction table you can first get all ReconciledTransaction ids:

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

and then use NOT IN filter in your Transaction query:

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

Or your can use subquery:

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

Edit: as Ilja Everilä stated NOT EXISTS operator performance might be better than NOT IN. SQLAlchemy query will look like this:

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

Another option would be to use NOT EXISTS(...), which is almost identical to NOT IN (...) except when it comes to NULLs, and [might perform better](https://stackoverflow.com/questions/173041/not-in-vs-not-exists), since `Transaction.transaction_id` is nullable. — Ilja Everilä, Sep 27 '17 at 09:26

Strange filter behavior in flask-sqlalchemy

1 Answers1