IS NOT NULL using database helper

[eluser]drewbee[/eluser]
[quote author="Phil Sturgeon" date="1244689193"][quote author="Huji" date="1244678382"]About debuging the SQL statement, I don't think it has a place here. The SQL statement created by the second solution is wrong, and this means that CodeIgniter doesn't support that idea. Should I repeat that it leads me to the idea of asking for this new feature to be supported?[/quote]

How do we know its wrong or how to fix it if you dont show us the query it produces? ;-)[/quote]

Yup. This one is chalked up to user error.

[eluser]Huji[/eluser]
[quote author="drewbee" date="1244681375"]

Code:

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

[/quote]

This doesn't work either. The SQL statement it generates is like:

Code:

SELECT * FROM `users` WHERE `surname` IS NOT

The "NULL" part is missing.

[eluser]drewbee[/eluser]
That is what I get for assuming. Huji is correct in this one gentlemen.

You can see the check in the function _where in the db_active_record.php file.

Code:

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

So... this works:

Code:

$where = array('forum_id' => null);
        $this->db->where($where, FALSE);        
        $this->db->get('forum');

Outputs : SELECT * FROM (`forum`) WHERE `forum_id` IS NULL

We can do the positive check, however we cannot do the negative

None of the following work:

Code:

$where = array('forum_id IS NOT' => null);
$where = array('forum_id !=' => null);

After digging through active record, and finding the following function _has_operator in DB_driver.php the function called above uses this:

Code:

function _has_operator($str)
    {
        $str = trim($str);
        if ( ! preg_match("/(\s|<|>|!|=|is null|is not null)/i", $str))
        {
            return FALSE;
        }

        return TRUE;
    }

To properly check against not null, we do the following, and can also do the following for IS NULL as well:

Code:

$where = array('forum_id IS NULL' => null,
               'forum_id IS NOT NULL' => null);
$this->db->get('forum');

Outputs: SELECT * FROM (`forum`) WHERE `forum_id` IS NULL AND `forum_id` IS NOT NULL


Granted, I would expect it to more work like this:

Code:

$where = array('forum_id IS' => null,
               'forum_id IS NOT' => null);

You would simply have to change the code in _has_operator and _where accordingly.

[eluser]CtheB[/eluser]
lol nice one drewbee...

I know you are helping that guy out because he wants to use the AR helper...

But wouldn't it be easier to just use:

Code:

$query = $this->db->query("SELECT ...");

AR helper only makes the code less readable, more complicated and slower...

[eluser]drewbee[/eluser]
I would disagree that it makes it slower. It is merely a query builder, and does very little analysis on it. I personally think the AR helper is a godsend to PHP querying. 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 think it makes it far superior in terms of readability and am willing to take whatever micro hit on speed that it does any day.

[eluser]Huji[/eluser]
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?

[eluser]sl3dg3hamm3r[/eluser]
[quote author="drewbee" date="1244775975"]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.[/quote]

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.

[eluser]drewbee[/eluser]
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.

[eluser]Phil Sturgeon[/eluser]
[quote author="drewbee" date="1244830211"]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.[/quote]

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:

Code:

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

That has as much point as any of these:

Code:

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

Just use:

Code:

$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.

[eluser]bientek[/eluser]
Drewbee's suggestion is working for me in CI 2.1.3:
[quote author="drewbee" date="1244666975"]

Code:

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

[/quote]