IS NOT NULL using database helper / Forums / Community / EllisLab

Forum Logo

2 of 2

IS NOT NULL using database helper

Posted: 12 June 2009 04:40 AM

[ Ignore ] [ # 16 ] [ Rating: 0 ]

Joined: 2008-07-31
260 posts

drewbee - 11 June 2009 11:06 PM
Query syntax can get very, very ugly if there are a lot of conditionals, and I always prefer coldfusion over php when it comes to writing queries, but that was before CI & AR.

I don’t intend to start to flame, but I don’t quite agree. I wouldn’t say the queries built with AR look much better than a nicely formated, hand-written sql-query.
And often I end up handwriting them because AR simply produces something which won’t work for me, if there is a certain complexity.
The only big advantage of AR over hand-written I can see so far are the automatically escaped parameters.

Signature

RostElyn - Russian Language in Switzerland
TableEditor: No more tirying CRUD-code

drewbee

Posted: 12 June 2009 10:10 AM

[ Ignore ] [ # 17 ] [ Rating: 0 ]

Joined: 2008-07-28
511 posts

The only reason I would say against it being a feature is the fact that there is no other conditions for the IS/IS NOT operators. IE NULL is the only value, making it rather moot to have this setup this way. The only other reasoning would be is to maintain the natural flow of AR.

So, for me, I am content with how it is… however it may be something you want to bring to the attention of the developers. Once again though, I would see this being far more useful if the database had actual values to compare against the IS/IS NOT operators, and not just the standard ‘NULL’ value.

Signature

~ 4 All the Right Reasons ~

Phil Sturgeon

Posted: 16 June 2009 06:02 AM

[ Ignore ] [ # 18 ] [ Rating: 0 ]

Joined: 2007-06-11
2985 posts

drewbee - 12 June 2009 02:10 PM
So, for me, I am content with how it is… however it may be something you want to bring to the attention of the developers. Once again though, I would see this being far more useful if the database had actual values to compare against the IS/IS NOT operators, and not just the standard ‘NULL’ value.

A PHP null is as standard as NULL comes but using IS/IS NOT NULL is not at all. Not all forms of SQL will share this syntax so there is no point.

The other option would be to add extra db functions for it, but what would be the point. You are simply saying something = NULL or something != NULL the same as any other value. Your suggestion seems to be that you want to see:


$this->db->is_null('field');

That has as much point as any of these:

$this->db->is_false('field');
$this->db->is_true('field');
$this->db->is_47('field'); 

Just use:

$this->db->where('field', NULL);
$this->db->where('field !=', NULL); 

One useful idea would be to add a where_not() to the active record which would stop us needing to add in operators ourself, but that would lead down a long road of where_greater_than(), where_less_than() and it would just end up getting silly.

Signature

————————
Blog | Twitter | GitHub | BitBucket
————————-
PyroCMS - open source modular CMS built with CodeIgniter
PancakeApp - Simple, hosted invoicing/w project management

NullUserException

Posted: 09 September 2011 05:20 PM

[ Ignore ] [ # 19 ] [ Rating: 0 ]

Joined: 2011-09-09
3 posts

A PHP null is as standard as NULL comes but using IS/IS NOT NULL is not at all. Not all forms of SQL will share this syntax so there is no point.

It is a standard. It’s called ANSI-92. Oracle, SQL Server, IBM DB2, MySQL, Postgres, Informix, Firebird, SQLite all support it. I actually don’t know a database that doesn’t support this syntax.

Phil Sturgeon - 16 June 2009 10:02 AM
The other option would be to add extra db functions for it, but what would be the point. You are simply saying something = NULL or something != NULL the same as any other value.

Except it isn’t the same thing.


SELECT * FROM table WHERE field != NULL

Is wrong. NULL is a special SQL value that cannot be compared to. RDBMSs will consider that condition to be falsy (similar to 1<>1) and return no rows.

The only RDBMS I know that works correctly with that is SQL Server with ANSI_NULLS turned off.

You have to use


SELECT * FROM table WHERE field IS NOT NULL

instead, and it doesn’t seem like there’s a way to do that through AR short of using


where('field IS NOT NULL', null)

Which is rather counter-intuitive IMO.

Phil Sturgeon

Posted: 10 September 2011 08:42 AM

[ Ignore ] [ # 20 ] [ Rating: 0 ]

Joined: 2007-06-11
2985 posts

My last post was written in June 2009. Whether things were different then or I was just wrong who knows, but the main point still stands, who care?

Just do:


$this->db->where('field !=', null);

If that is broken, send a pull request.

Signature

NullUserException

Posted: 10 September 2011 10:51 AM

[ Ignore ] [ # 21 ] [ Rating: 0 ]

Joined: 2011-09-09
3 posts

Phil Sturgeon - 10 September 2011 12:42 PM
My last post was written in June 2009. Whether things were different then or I was just wrong who knows, but the main point still stands, who care?

Just do:

$this->db->where('field !=', null);

If that is broken, send a pull request.

It is broken. How do I send a pull request?

This could be changed to behave as you described by replacing:

if (is_null($v) && ! $this->_has_operator($k))
{
    // value appears not to have been set, assign the test to IS NULL
    $k .= ' IS NULL';
} 

(system/database/DB_active_rec.php, lines 415-419)

With something like (no PHP here, not tested):

if (is_null($v))
{
    // value appears not to have been set
    if (! $this->_has_operator($k))
    {
        // has no operator, assign the test to IS NULL
        $k .= ' IS NULL';
    }
    elseif (preg_match("/(!=|<>)\s*$/", $k))
    {
        // has <> or != operator, assign test to IS NOT NULL
        $k = preg_replace("/(!=|<>)\s*$/", ' IS NOT NULL');
    }
} 

Phil Sturgeon

Posted: 10 September 2011 11:42 AM

[ Ignore ] [ # 22 ] [ Rating: 0 ]

Joined: 2007-06-11
2985 posts

Here you go:

http://codeigniter.com/news/contribution_guide/

Signature

bientek

Posted: 05 February 2013 08:17 AM

[ Ignore ] [ # 23 ] [ Rating: 0 ]

Joined: 2010-07-11
34 posts

Drewbee’s suggestion is working for me in CI 2.1.3:

drewbee - 10 June 2009 04:49 PM
$query = $this->db->get_where('users', array('surname IS NOT' => NULL));

Signature

My Michigan computer repair services business, BienTek.

2 of 2