DB query filtered by comparison of two fields?

Say I have an account model with several fields, two of them are ordered and used, both integers. I need to show a list of accounts that have unused orders. Is there a way to filter by comparing the two fields such as
⠀⠀⠀⠀⠀⠀DB.account.where(“ordered > ?”, used)

That example doesn’t work as it expects used to be a JS variable, not one of the fields. Any suggestions?

Hi @jwyrick

Unfortunately you cannot do that kind of comparison in a JourneyApps DB query.

Your options are

  • Store and update the calculated value in the DB and then query on the calculated value, e.g. DB.account.where('unused > ?', 0)
  • Pull in all the objects into an Array and then do a custom array filter in JS.
    (Please note, if your comparison logic needs to read data from associated objects you will need to first pull those objects into memory, see this post for an example but with a sort function. However, if your comparison logic is just going to use fields directly on the objects then you will be fine)

In essence the latter is what you would have wanted the query to do, as the DB would need to compare each record with itself, as opposed to comparing it to some static query value.

I hope this helps.