Welcome Guest, Not a member yet? Register   Sign In
Query Builder is not null
#1

I'm in the process of upgrading my project from 3.0.0 to 3.1.8 (in one go).

After going over all the required changes listed in the docs, I started testing my project, and realized something is different in the way CI is treating IS NOT NULL statements in the Query Builder.

In my 3.0.0 project, I used syntax like this:

PHP Code:
$this->db->where(array('book_code'=>$book_code,'poi_num is not' => null)); 


to produce
WHERE book_code=[code value] AND poi_num IS NOT NULL

but now, in 3.1.8, the same code produces:
WHERE book_code=[code value] AND poi_num IS NOT

So I changed my Query Builder syntax to:


PHP Code:
$this->db->where(array('book_code'=>$book_code,'poi_num is not NULL' => null)); 

And now it works fine.

But:
1. Is this the correct syntax to use in such cases?

2. Was this a known issue in one of the upgrades between 3.0.0 and 3.1.8? If so, where was it documented? I'm worried that if I missed this, I might have missed other important changes.
Reply
#2

It's regarding the bug below, regarding columns ending with is.

Personally I don't have a query with IS NOT NULL in CI, but I would likely type it out in a complete sentence.

Bug fixes for 3.1.2
Fixed a bug (#4884) - Query Builder didn’t properly parse field names ending in ‘is’ when used inside WHERE and HAVING statements.
Reply
#3

(03-31-2018, 04:18 PM)jreklund Wrote: It's regarding the bug below, regarding columns ending with is.

Personally I don't have a query with IS NOT NULL in CI, but I would likely type it out in a complete sentence.

Bug fixes for 3.1.2
Fixed a bug (#4884) - Query Builder didn’t properly parse field names ending in ‘is’ when used inside WHERE and HAVING statements.

So fixing that bug caused Query Builder to not accept 'filed_name is not' => null syntax as before?

And what's really worrying is how many more similar bugs might there be in my project. I'm really hesitant to go forward with the upgrade now Confused .
Reply
#4

(This post was last modified: 04-01-2018, 04:33 AM by jreklund.)

So you would rather have that bug still in there? Next time it may be you that have a word that ends with is. It weren't indented to be used that way.

IS NOT NULL
PHP Code:
$this->db->where(array('book_code'=>$book_code,'poi_num IS NOT NULL' => NULL));
$this->db->where(array('book_code'=>$book_code,'poi_num <>' => NULL));
$this->db->where(array('book_code'=>$book_code,'poi_num !=' => NULL)); 

IS NULL
PHP Code:
$this->db->where(array('book_code'=>$book_code,'poi_num IS NULL' => NULL));
$this->db->where(array('book_code'=>$book_code,'poi_num' => NULL));
$this->db->where(array('book_code'=>$book_code,'poi_num =' => NULL)); 

NULL aren't a value and therefor ignored.

But hey, you do you. Don't upgrade and have program that won't run on new environments with bugs that will make you scratch your head over.
Reply
#5

(04-01-2018, 04:23 AM)jreklund Wrote: So you would rather have that bug still in there? Next time it may be you that have a word that ends with is. It weren't indented to be used that way.

Of course not! But if fixing one bug caused something that was working before to stop working in new versions, it should have been mentioned in the upgrading instructions.

If my old syntax was wrong, than I would also be happy to stand corrected. This is why, in my original post, I asked if the syntax was correct.

You posted 3 example for the use of IS NULL/IS NOT NULL. Are all the 3 options valid syntax? Was my old syntax not valid?
Reply
#6

(This post was last modified: 04-01-2018, 06:10 AM by jreklund.)

Kinda came off as harsh there. Not my intention. And for that I'm sorry.

I haven't found a similar question on Github or on this forum regarding this. So I'm guessing you are the first one having this problem. Haven't tested this in CI 2.x and Google have discussed this earlier how to properly type it.

As of version 3.0 those are the correct way of writing them. But they aren't listed in the user manual (only change log).
Code:
Methods where(), or_where(), having() and or_having() now convert trailing = and <>, != SQL operators to IS NULL and IS NOT NULL respectively when the supplied comparison value is NULL.

https://github.com/bcit-ci/CodeIgniter/c...3124549130

I can't say if it's a invalid syntax or not. That can only be answered by the CI council or Narf specificly.
Reply
#7
Thumbs Up 

Quote:Kinda came off as harsh there. Not my intention. And for that I'm sorry.
Appreciate the apology. (-:

After fixing all the IS NOT NULL instances, I took a leap of faith and went ahead with the upgrade. So far so good.

Thanks for your help.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB