There is an application on flask , sqlalchemy used. There is a query in which I insert .filter()

The question is: Why is this code

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

fulfills correctly, but this

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

does not work correctly (the filter is stupidly ignored in this case)?

  • and you can get more details, the filter cannot be ignored, or it will produce a result, or an error that you give it and what you get? - Igor Lavrynenko
  • @IgorSergeevich Roughly speaking, the request retrieves the list of all transactions, shoves client information (which does not contain any transactions) with joins, and I try to filter out the transactions that are present in the ReconciledTransaction table, neither in the first nor in the second case there are errors, the code is not falls, just in the first case, the filter is triggered (only those transactions that are in ReconciledTransaction are issued), and in the second case it does not work (all transactions are issued). - Klimenkomud
  • Are you sure that in the second case you have transactions that are in ReconciledTransaction.safe_withdraw_id ? - Igor Lavrynenko
  • @IgorSergeevich More than that, because the request itself does not change at all, I only change the filter condition - Klimenkomud
  • Add prints and compare with python under the conditions == & is & type() can there be a number crept in - a string? - Igor Lavrynenko

1 answer 1

Helped on enSO
When using a query on the similarity of this:

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

We get the following SQL :

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

Accordingly, in any case, we make a selection from the second table, and in this case, both those and those results fall into the query result, it turns out that the filter is “somehow ignored”.
To prevent this from happening, you need to make a selection from the second table (with which we compare):

 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 our filter (in my case) will look like this:

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

Alternatively, you can do it like this (they say, better performance):

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