CODEWITHSUNDEEP
pythonsqlalchemy
Klymenko Kyrylo
Klymenko Kyrylo

Reputation: 671

Strange filter behavior in flask-sqlalchemy

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

Upvotes: 2

Views: 225

Answers (1)

Sergey Shubin
Sergey Shubin

Reputation: 3257

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

Upvotes: 1

Related Questions