Abd00s Abd00s - 1 month ago 8
SQL Question

using array_length inside a WHERE statement in ActiveRecord

I am more comfortable with activerecord as opposed to vanilla SQL so i'm having a bit of trouble.

I have a Relationships table with a property called sequence which is of type array. The array is either empty or contains a series of numbers.

What I am trying to achieve is to write an AR scope the allows me to return only records whose size of their sequence array is equal to

num
. I imagine it looking something like this:

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


If it were available in active record, I would imagine it looking like:

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


EDIT:
Here's what the migration looks like:

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


Sample data:

[
[ 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"
}
]


I'd want
Relationship.all
to return both records,
Relationship.of_order(0)
to return the 1st record,
Relationship.of_order(3)
to return the 2nd record,
and
Relationship.of_order(2)
to return none.

Answer

I think the root of your problem is that array_length is null when the array is empty:

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

(1 row)

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

This isn't exactly clearly documented so don't feel bad if you missed it.

So given:

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

Relationship.of_order(6) will work just fine but Relationship.of_order(0) will end up trying to do null = 0 inside the database and that is never true so you don't find your empty arrays.

Two easy solutions come to mind:

  1. You can handle the of_order(0) case explicitly in your scope:

    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. Throw a coalesce call into the query to convert NULLs to zeros and let the database worry about it:

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