Ruby on Rails and Querys (understanding Question)

Question

I have a table called Codes on my project.

irb(main):017:0> Code.new                                      
=> #<Code id: nil, company_id: nil, name: nil, employee_uuid: nil, employee_email: nil, redeemed_at: nil, created_at: nil, updated_at: nil>

On my Database i have a total of 97994 Code entries.

irb(main):018:0> Code.all.count
(127.7ms)  SELECT COUNT(*) FROM "codes"
=> 97994

So, what i don't understand is the following. So far i tested it with the name attribute.

irb(main):019:0> Code.where(name: "").count
(135.0ms)  SELECT COUNT(*) FROM "codes" WHERE "codes"."name" = ?  [["name", ""]]
=> 0
irb(main):020:0> Code.where(name: " ").count
(18.3ms)  SELECT COUNT(*) FROM "codes" WHERE "codes"."name" = ?  [["name", " "]]
=> 0

if the do the oposite Query it should give me the missing objects, on this case my total amount of codes.

irb(main):021:0> Code.where.not(name: "").count 
(116.3ms)  SELECT COUNT(*) FROM "codes" WHERE ("codes"."name" != ?)  [["name", ""]]
=> 94652
irb(main):022:0> Code.where.not(name: " ").count
(19.9ms)  SELECT COUNT(*) FROM "codes" WHERE ("codes"."name" != ?)  [["name", " "]]
=> 94652

But i get as a result 94652 instead of 97994 (from Code.all.count)

That makes me wonder. I did check what are the left codes:

irb(main):023:0> rest = Code.all - Code.where(name: "") - Code.where.not(name: "")
Code Load (1030.6ms)  SELECT "codes".* FROM "codes"
Code Load (16.1ms)  SELECT "codes".* FROM "codes" WHERE "codes"."name" = ?  [["name", ""]]
Code Load (489.1ms)  SELECT "codes".* FROM "codes" WHERE ("codes"."name" != ?)  [["name", ""]]
... a huge array was returned
irb(main):024:0> rest.last
=> #<Code id: 86217, company_id: 307, name: nil, employee_uuid: "XXXXXXXXXXXXXX", employee_email: "[email protected]", redeemed_at: "2018-07-09 12:30:29", created_at: "2018-12-11 13:07:57", updated_at: "2018-12-11 13:07:57">

I was curious about this, so i checked one more thing:

irb(main):027:0> rest.last.name == ""
=> false
irb(main):028:0> rest.last.name != ""
=> true

so the rest.last.name is not "" (of course not, name is nil)

but still i get this

irb(main):025:0> Code.where(name: "").include? rest.last
Code Load (108.1ms)  SELECT "codes".* FROM "codes" WHERE "codes"."name" = ?  [["name", ""]]
=> false
irb(main):026:0> Code.where.not(name: "").include? rest.last
Code Load (365.9ms)  SELECT "codes".* FROM "codes" WHERE ("codes"."name" != ?)  [["name", ""]]
=> false

I always thought where something, and its negative will always make the total amount, meaning when something happen, and when this something does not happen would give us the always answer. A + !A = 1 Why am i seeing this? i cannot follow this.

Thanks in advance for the help.

Update Question to answer comments:

irb(main):002:0> Code.where(name: [nil, '']).count                                                   
(432.2ms)  SELECT COUNT(*) FROM "codes" WHERE ("codes"."name" = '' OR "codes"."name" IS NULL)
=> 3342

and this number is exactly

irb(main):003:0> Code.all.count - Code.where.not(name: "").count
(315.2ms)  SELECT COUNT(*) FROM "codes"
(332.5ms)  SELECT COUNT(*) FROM "codes" WHERE ("codes"."name" != ?)  [["name", ""]]
=> 3342

Ok, so far i understand that SQL cannot compare to Nil or not Nil when i test if a string is empty. That means i need to either do the query with array [nil, ''] or think in an other way..

Could you share the result of the following query? Code.where(name: [nil, '']).count — Zohaib A. Butt
– Zohaib A. Butt, Commented Dec 12, 2018 at 7:56

Zohaib A. Butt · Accepted Answer · 2018-12-17 16:34:30Z

3

In this case, The default value for name being stored in database is NULL. If you did not save name for a code entry in the database, it will be stored as NULL instead of an empty string "".

So, if you run the query Code.where(name: "").count, this will explicitly try to return only those rows that have a name equal to an empty string ("") and ignore all entries having name=NULL or " ". This is 0 in your case

When you run the query Code.where(name: [nil, '']).count, you receive a result of 3342, which is exactly equal to the difference between all rows in the table and the rows that do not have a name equal to an empty string (Code.where.not(name: "").count). Which proves that there are 3342 rows in the table that have name stored as NULL.

So, in order to avoid this situation, you can set default value of "" (empty string) to your name attribute in the migration that is creating the Code table. This way, the results of your query to find entries where name is not present, i.e. (Code.where(name: "").count) consistent.

Update:

Code.where.not(name: '') will ignore records where name = NULL in database. Reason is the query that is formed:

SELECT * FROM Codes WHERE name != "";

This query explicitly tries to find those rows where name is not "", but in order to check if NULL exists or not, we have to write a query like the one below in SQL:

SELECT * FROM Codes WHERE name IS NOT NULL;

The above mentioned query is generated if we write Codes.where.not(name: nil) in ActiveRecord.

To implement both conditions that were mentioned above in the two different queries in a single query, we will use the following in ActiveRecord:

Code.where.not(name: [nil, ""])

This will generate the following query:

SELECT * FROM "codes" WHERE AND (NOT (("codes"."name" = "" OR "codes"."name" IS NULL)))

The above query ensures that we avoid records with both conditions of empty string and NULL-valued name in database.

edited Dec 17, 2018 at 16:34

answered Dec 17, 2018 at 9:36

Zohaib A. Butt

1561 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Filipe Santiago Over a year ago

My question here is more, why Code.where.not(name: "")does not include the codes that have the name = nil value. I guess, when i try to search for a name that is not equal to "" SQL ( and not ruby/rails) think the name is already a NOT NIL value, and ignores it, or am i wrong here?

Zohaib A. Butt Over a year ago

I have updated the answer under the Update heading to try to answer your question. Please have a look and let me know if this information is satisfactory.

Vasilisa · Accepted Answer · 2018-12-12 08:34:43Z

2

It is because sql != and = operators can't compare with NULL. For NULL comparison you need to use IS NULL and IS NOT NULL or in ROR syntax

Code.where(name: [nil, '']).count
Code.where.not(name: [nil, '']).count

answered Dec 12, 2018 at 8:34

Vasilisa

4,6403 gold badges22 silver badges25 bronze badges

2 Comments

Filipe Santiago Over a year ago

you mean that (in SQL) != '' will never include the == nil results ?

Vasilisa Over a year ago

@FilipeSantiago, exactly!

Dave Powers · Accepted Answer · 2019-07-30 16:03:53Z

1

SQL queries do not return records with NULL values when using a where clause.

Some additional resources:

edited Jul 30, 2019 at 16:03

Dave Powers

2,4332 gold badges38 silver badges39 bronze badges

answered Dec 12, 2018 at 8:34

Aarthi

1,53119 silver badges43 bronze badges

Collectives™ on Stack Overflow

Ruby on Rails and Querys (understanding Question)

3 Answers 3

Update:

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Update:

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related