CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
whoaaallamapajama
whoaaallamapajama

Reputation: 101

ActiveRecord Query of nil field

This is my test query and result:

>Pack.first.pages
  Pack Load (0.3ms)  SELECT  "packs".* FROM "packs"   ORDER BY "packs"."id" ASC LIMIT 1
  Page Load (0.1ms)  SELECT "pages".* FROM "pages"  WHERE "pages"."pack_id" = $1  [["pack_id", 1]]
=> #<ActiveRecord::Associations::CollectionProxy [#<Page id: 53, created_at: "2015-02-27 21:59:12", updated_at: "2015-03-23 16:41:05", pack_id: 1, name: "test - pack12?", client_name: "", thumbnail: nil, printable_page: nil, preview: nil, vuforia_archive: "dinosaur.zip", unity_bundle: "girl.unity3d", vuforia_identifier: nil, vuforia_archive_updated_at: "2015-02-27 21:59:12", unity_bundle_updated_at: "2015-03-23 16:41:05">]>

The fields I am concerned with are nil, so why doesn't this work...

> Pack.first.pages.where('thumbnail=? OR printable_page=? OR preview=?',nil,nil,nil)
  Pack Load (0.3ms)  SELECT  "packs".* FROM "packs"   ORDER BY "packs"."id" ASC LIMIT 1
  Page Load (0.1ms)  SELECT "pages".* FROM "pages"  WHERE "pages"."pack_id" = $1 AND (thumbnail=NULL OR printable_page=NULL OR preview=NULL)  [["pack_id", 1]]
=> #<ActiveRecord::AssociationRelation []>

Upvotes: 0

Views: 598

Answers (2)

David Aldridge
David Aldridge

Reputation: 52336

Databases implement three-value logic, in which null is neither true nor false. Null cannot be stated to be equal to, not equal to, greater then, or less than any other value, including null, so comparisons with null will always be null, and therefore not true. There is a separate test for nullness: "thumbnail is null".

As a side note, remember that a predicate such as "name = 'jim'" in an RDBMS is a statement of truth which is tested against rows, which are included or not based on whether that statement is true.

As another side note, this means that "age in (1,null)" might be true is age equals 1, but will not be true is age has a value of null. Similarly, "age not in (1, null)" is false for age = 2 because "2 = null" is false.

Active record is generally pretty good at handling this as it can respond to a condition such as where(:age => my_age) by writing different predicates based on whether my_age is null or not. It will even handle my_age being an array of [1, 2, nil] correctly by writing a predicate such as "where age in (1,2) or age is null".

Long story short, use:

Pack.first.pages.where('thumbnail is null OR printable_page is null OR preview is null')

Upvotes: 1

Chuck Callebs
Chuck Callebs

Reputation: 16441

Depending on your version of SQL, you'll need to format the query like so:

Pack.first.pages.where("thumbnail is null or printable_page is null or preview is null")

field = null doesn't work.

Upvotes: 2

Related Questions