Search code examples
ruby-on-railspostgresqlactiverecordhstore

Activerecord scope query on hstore attributes, check if attribute is nil?

I have a column in my Subscription model called expiry_date that tracks when a subscription will expire. I also have hstore column called properties which has an attribute called expiry_email_sent that is used as a flag to determine if an email has been sent. 

store_accessor :properties, :expiry_email_sent

I have a scope as below to extract all records that have expiry_date between today and the next 5 days. It also fetches only those records that have properties as nil, or those for which expiry_email_sent does not exist. I want to addd a check to fetch records for which expiry_email_sent is nil if it exists. How would I add that in the scope below?

scope :expiry_soon_email, -> { where("expiry_date >= ? AND expiry_date <= ? AND (properties IS NULL OR EXIST(properties, 'expiry_email_sent') = FALSE", Time.zone.now.to_date, Time.zone.now.to_date + 5.days) }
Solution

  • You may use Postgres function defined(hstore,text) which means

    does hstore contain non-NULL value for key?

    Your scope should looks like 

    scope :expiry_soon_email, -> { where("expiry_date >= ? AND expiry_date <= ? AND (properties IS NULL OR DEFINED(properties, 'expiry_email_sent') = FALSE", Time.zone.now.to_date, Time.zone.now.to_date + 5.days) }

    See details about hstore functions in Postgres documentation.