Rails query hstore where key is NOT something or doesn't exist
I have a table with hstore and sometimes I use it to store information for a checkbox. We use "1" for checked ones and "0" for unverified ones. There is no default for hstore, so the user can be in one of four states:
customer_a = Customer.new({properties: {checkbox: "1"}}) # checked
customer_b = Customer.new({properties: nil}) # unchecked(object doesn't exist)
customer_c = Customer.new({properties: {checkbox: "0"}}) # unchecked(unchecked)
customer_d = Customer.new({properties: {taco: "chicken"}}) # unchecked(key doesn't exist)
Situation: How can I find all the "unchecked" lines?
Customer.where(" customer.properties -> 'checkbox' NOT LIKE '1' ")
^^^ does not work ^^^ ignores clients with empty "checkbox" key and where properties are empty.
Customer.where("customers.properties -> 'checkbox' LIKE '%0%' OR customers.properties IS NULL")
^^^ doesn't work ^^^ also ignores where key is missing
Is there a better way to do this in a single request?
the request should return [customer_b, customer_c, customer_d]
current solutions: - checked: Customer.where(" customer.properties -> 'checkbox' LIKE '1' ")
- unchecked:Customer.all - Customer.where(" customer.properties -> 'checkbox' LIKE '1' ")
does sql query exist returning rows where hstore key does not exist?
source to share
If you want to find all records where hstore is empty use
Customer.where(properties: ['',nil])
OR
Customer.where("properties='' OR properties IS NULL")
EDIT
Assuming you want to find everything minus 1 of your empty line comments, you can try this,
Customer.where("properties -> 'checkbox' NOT LIKE '1' OR NOT(properties ? 'checkbox') OR properties = '' OR properties is null")
To check that hstore contains a key
properties ? 'checkbox'
checks for existence and I do not on it to find the ones with no checkbox and then empty properties. Let me know if this works.
source to share