Thread

Thanks drwbeee, it really helped; not only my question was answered, but also I got a better concept about AR. Now, do you think we should suggest this feature in the feature request section?

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.

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.

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.

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.

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.

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.

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');
    }
}

Here you go:

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

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

$query = $this->db->get_where('users', array('surname IS NOT' => NULL));