I have a dedicated plan and so I often query my datatables directly.
I’m trying to query two tables that are linked. The first bit of the WHERE statement works. I fetch only rows where the master_data._id is in _master_ids. However, many of the linked rows from the requirements table have the transfer_orders column empty. I don’t want to fetch those rows, ie the last bit of the WHERE statement AND requirements.transfer_orders IS NOT NULL should remove those rows. However, when I print the result of the query, it seems that the last bit of the WHERE statement has no effect and I have a result with many NULL transfer orders.
SELECT
master_data.PRODUCT_ID,
master_data.LOCATION,
master_data.PRODUCT_GROUP,
requirements.transfer_orders
FROM master_data
INNER JOIN requirements ON master_data.linked_requirements = requirements._id
WHERE master_data._id IN {_master_ids}
AND requirements.transfer_orders IS NOT NULL
What kind of column type is requirements.transfer_orders ? If it is media, or a simple object try:
SELECT
master_data.PRODUCT_ID,
master_data.LOCATION,
master_data.PRODUCT_GROUP,
requirements.transfer_orders
FROM master_data
INNER JOIN requirements ON master_data.linked_requirements = requirements._id
WHERE master_data._id IN {_master_ids}
AND requirements.transfer_orders IS NOT NULL
AND requirements.transfer_orders != "NULL"
No idea if this will work. I have no experience using dedicated anvil plan features, just SQL in general.
Aha - this is probably it! SimpleObject columns are presented (and stored) in Postgres’s JSONB type, and JSON can represent “null” separately from being an SQL “NULL”. Yes, it’s confusing, but it can actually carry meaning (eg “this key is not present in this object” vs “this key is present but null”). In Anvil’s case we generally want to make sure the key is present in our underlying data store in order to support indexed queries.
Does your query work if you compare the value with 'null'::jsonb (or even both, eg transfer_orders IS NOT NULL AND transfer_orders != 'null::JSOB`, although that shouldn’t be necessary)?
Ah, we were so close, I noticed in the code that simple object NULL is treated differently than a regular sql NULL row entry, but I did not understand correctly how to access it.
Before anvil I did not use postgresql , but I have come to really like it’s ability/tools to search within columns of stored json data. Good stuff.