How to search through array of strings using BETWEEN in Active Record POSTGRES,

Question

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

s1mpl3 · Accepted Answer · 2017-04-07 20:51:10Z

5

Subscription.where(canceled_at:(date.beginning_of_week(:sunday)..date.end_of_week(:sunday)))

Edit: missing closing parentheses

edited Apr 7, 2017 at 20:51

answered Apr 7, 2017 at 15:31

s1mpl3

1,4741 gold badge10 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

tnaught Over a year ago

This receives an error, as canceled_at is an array of Dates.

s1mpl3 Over a year ago

Isn't canceled_at a column in your subscriptions table?. The code was missing a last parentheses.

tnaught Over a year 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.

s1mpl3 Over a year 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.

tnaught Over a year 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?

|

Collectives™ on Stack Overflow

How to search through array of strings using BETWEEN in Active Record POSTGRES,

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related