0

I'm trying to build a scope for my Contradiction model where the evaluation_parameters attribute only contains non NULL values. I have records where this attribute is completely empty {} or where one of the two values is nil {123, NULL} and I would like to not have these in my result.

Here is my migration: 

add_column :contradictions, :evaluation_parameters, :integer, array: true, default: []

I already tried the following query which returns only records with an empty array

Contradiction.all.where.not("NULL = ANY (evaluation_parameters)")

Any suggestions on how I could do this ? Thank you :)

Joel Mercier
  • 53
  • 6

2 Answers2

0

First of all:

evaluation_parameters should probably be :text, not :integer.

The query you're looking for then is:

Contradiction.where("evaluation_parameters = '{}'")

This would fetch all contradictions which have evaluation_parameters equal to empty array.

Yaro
  • 570
  • 3
  • 20
  • Thank you for your input, however doing this only gives me the results with an empty array. What I want is all contradictions where both values in the array are NOT null (e.g. `{123, 123}`). I also don't see why I would have to convert to `:text` since I'm storing ids in this array. – Joel Mercier Nov 27 '17 at 08:07
0

I found an answer here Check if NULL exists in Postgres array My final query looks like this :

Contradiction.where.not(evaluation_parameters: '{}').where.not("-1 = ANY(evaluation_parameters) IS NULL")
Joel Mercier
  • 53
  • 6