ruby-on-rails - AR 中的 SQL 搜索元素是否不在列数组中

Question

date = Date.today

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

购物车上的列 skipped_weeks 是一个日期数组，如 ["2017-04-10", "2017-04-17"]

我正在运行上面的查询并且仍然接收到 cart.skipped_weeks 列数组中包含日期的记录，我只想要 skipped_weeks 数组中不包含该日期的记录。

Answer 1

PostgreSQL 支持 all以及任何:

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.

说:

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

可能会更符合您的预期逻辑。