I am trying to make a query that includes a jsonb column and 2 non jsonb columns.
Multiple attempts to combine them have failed but 1 nearly worked when I only used 1 other non jsonb column. I have a channel model with an 'options' store and several attributes within.
If i separate the queries they work just fine but combined they retrieve an empty array. I have made sure that if the queries did work, there is definitely data for them to return.
Non jsonb columns - works
Channel.where("platform_id = ? AND updated_at < ?",2,7.days.ago)
jsonb column - works
Channel.where("options @> ?", {valid_account: true}.to_json)
combined where operator - returns empty []
Channel.where("platform_id = ? AND updated_at < ?",2,7.days.ago).where("options @> ?", {valid_account: true}.to_json)
1 where operator with combined query - again, returns empty []
Channel.where("options = ? AND platform_id = ? AND updated_at < ?", {"valid_account" => true}.to_json, 2, 7.days.ago)
Am at a loss now and not sure how to get this all into one query... or if it's even possible.
Again... there are definitely channels that should return with the given queries above
TIA
UPDATE
Managed to get the query to work. tried nearly every combination but missed one critical one.
Channel.where("options @> ? AND platform_id = ? AND updated_at > ?", {valid_account: true}.to_json, 2, 7.days.ago)
this worked. For some reason I had used the '>@' in a separate method but not combined it for this. All working now. Thanks for the support
Answer was to include '>@' in the query for the jsonb column
Channel.where("options @> ? AND platform_id = ? AND updated_at > ?", {valid_account: true}.to_json, 2, 7.days.ago