up vote 1 down vote favorite 
Subscriptions
    canceled_at: ["2017-04-07T09:52:17-05:00", "2017-04-01T09:52:17-05:00"]

Canceled_at is an array of strings. Each element is a DateTime converted to a string.

Above is my subscriptions table. I am trying to query through and find records that have a canceled_at date within a given week. 

date = Date.today

Subscription.where("ANY subscriptions.canceled_at 
    BETWEEN '#{date.beginning_of_week(:sunday)}' 
    AND '#{date.end_of_week(:sunday)}'"

ruby 2.3.1p112

share|improve this question
up vote 3 down vote 
Subscription.where(canceled_at:(date.beginning_of_week(:sunday)..date.end_of_week(:sunday)))

Edit: missing closing parentheses

share|improve this answer
    
This receives an error, as canceled_at is an array of Dates. – tnaught 21 hours ago
    
Isn't canceled_at a column in your subscriptions table?. The code was missing a last parentheses. – s1mpl3 20 hours ago
    
Yes it is a column on the sub table. It however is an array. The above returns an error ActiveRecord::StatementInvalid: PG::InvalidTextRepresentation: ERROR: malformed array literal: "2017-04-09" DETAIL: Array value must start with "{" or dimension information. – tnaught 20 hours ago
    
Oh. Some times is hard to tell. Is it serialized, array saved as string? I would not recommend to do that way, can you cancel a subscription more than once witouth an indications that it has been reactivated? But if you show more detail of the data structure it is still possible to get the data but it will perform very poorly if you have many records. – s1mpl3 20 hours ago
    
The subscription can be canceled and reactivated many times. The reactivation date is not too important to me. I mainly just want to keep track and see if during a given week this subscription was canceled. The canceled_at: column is an array of date times represented as strings. Would you still be able to pull if a given date was in range? – tnaught 19 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.