Abd00s - 7 months ago 28

SQL Question

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`

`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`

`Relationship.of_order(0)`

`Relationship.of_order(3)`

and

`Relationship.of_order(2)`

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:

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`

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`