sql - 在 ActiveRecord 的 WHERE 语句中使用 array_length

Question

与 vanilla SQL 相比，我更喜欢 activerecord，所以我遇到了一些麻烦。

我有一个 Relationships 表，它有一个名为 sequence 的属性，它是数组类型。该数组为空或包含一系列数字。

我想要实现的是编写一个 AR 范围，它允许我只返回序列数组大小等于 num 的记录。我想象它看起来像这样:

def self.of_order(num)
   where("array_length(sequence, 1) = ?", num)
end

如果它在事件记录中可用，我会想象它看起来像:

def self.of_order(num)
   where(sequence.size == num)
end

编辑:这是迁移的样子:

class CreateRelationships < ActiveRecord::Migration
  def change
    create_table :relationships do |t|
      t.integer :root_id
      t.integer :destination_id
      t.boolean :first_order?
      t.text :sequence, array: true, default: []

      t.timestamps null: false
    end
  end
end

示例数据:

[
    [ 0] #<Relationship:0x007f8d5a5c82c8> {
                    :id => 73,
               :root_id => 51,
             :target_id => 52,
          :first_order? => true,
              :sequence => [],
            :created_at => Thu, 20 Oct 2016 19:05:22 UTC +00:00,
            :updated_at => Thu, 20 Oct 2016 19:05:22 UTC +00:00,
        :primitive_type => "spouse"
    },
    [ 1] #<Relationship:0x007f8d5a5c8188> {
                    :id => 74,
               :root_id => 52,
             :target_id => 51,
          :first_order? => true,
              :sequence => [22,43,90],
            :created_at => Thu, 20 Oct 2016 19:05:22 UTC +00:00,
            :updated_at => Thu, 20 Oct 2016 19:05:22 UTC +00:00,
        :primitive_type => "spouse"
    }
]

我希望 Relationship.all 返回两条记录，Relationship.of_order(0) 返回第一条记录，Relationship.of_order(3) 返回第二条记录，和 Relationship.of_order(2) 返回 none。

Answer 1

我认为你问题的根源是当数组为空时 array_length 为 null:

=> select array_length(array[]::text[], 1);
 array_length 
--------------

(1 row)

=> select coalesce(array_length(array[]::text[], 1), 0);
 coalesce 
----------
        0
(1 row)

这并没有被清楚地记录下来，所以如果你错过了也不要难过。

因此给定:

def self.of_order(num)
  where("array_length(sequence, 1) = ?", num)
end

Relationship.of_order(6) 可以正常工作，但 Relationship.of_order(0) 最终会尝试执行 null = 0在数据库中，这永远不会是真的，所以你找不到你的空数组。

我想到了两个简单的解决方案:

1. 您可以在您的范围内显式处理 of_order(0) 情况:

   def self.of_order(num)
     if(num == 0)
       where('array_length(sequence, 1) is null')
     else
       where('array_length(sequence, 1) = ?', num)
     end
   end
   

2. 在查询中调用 coalesce 以将 NULL 转换为零，并让数据库负责:

   def self.of_order(num)
     where('coalesce(array_length(sequence, 1), 0) = ?', num)
   end