SQL in AR searching if an element is not within a column array

Question

date = Date.today

Subscription.joins(:cart).where("'#{date.beginning_of_week(:sunday).to_s}' != ANY (carts.skipped_weeks)")

The column skipped_weeks on Cart is an array of dates like ["2017-04-10", "2017-04-17"]

I am running the above query and still receiving and records that have a date included on the cart.skipped_weeks column array, I only want records that do not include that date in the skipped_weeks array.

mu is too short · Accepted Answer · 2017-04-04 18:30:28Z

1

PostgreSQL supports all as well as any:

9.23.4. ALL (array)
expression operator ALL (array expression)
The right-hand side is a parenthesized expression, which must yield an array value. The left-hand expression is evaluated and compared to each element of the array using the given operator, which must yield a Boolean result. The result of ALL is "true" if all comparisons yield true (including the case where the array has zero elements). The result is "false" if any false result is found.

Saying:

where('? != all(carts.skilled_weeks)', date.beginning_of_week(:sunday))

would probably be a more readable match for your intended logic.

answered Apr 4, 2017 at 18:30

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

2 Comments

tnaught Over a year ago

is there any added benefit to using ALL vs ANY?

mu is too short Over a year ago

Not likely, just a question of readability and preference. The query optimizer probably knows De Morgan's Laws well enough to translate.

tnaught · Accepted Answer · 2017-04-04 17:58:36Z

1

Subscription.joins(:cart).where("NOT '#{date.beginning_of_week(:sunday).to_s}' = ANY (carts.skipped_weeks)")

Here is the correct form

answered Apr 4, 2017 at 17:58

tnaught

4011 gold badge5 silver badges14 bronze badges

Collectives™ on Stack Overflow

SQL in AR searching if an element is not within a column array

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related