EllisLab text mark
Advanced Search
2 of 2
2
   
IS NOT NULL using database helper
Posted: 12 June 2009 04:40 AM   [ # 16 ]   [ Rating: 0 ]
Avatar
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

 
Posted: 12 June 2009 10:10 AM   [ # 17 ]   [ Rating: 0 ]
Avatar
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 ~

 
Posted: 16 June 2009 06:02 AM   [ # 18 ]   [ Rating: 0 ]
Avatar
Joined: 2007-06-11
2987 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

 
Posted: 09 September 2011 05:20 PM   [ # 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.

 
Posted: 10 September 2011 08:42 AM   [ # 20 ]   [ Rating: 0 ]
Avatar
Joined: 2007-06-11
2987 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 

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

 
Posted: 10 September 2011 10:51 AM   [ # 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');
    
}
 
Posted: 10 September 2011 11:42 AM   [ # 22 ]   [ Rating: 0 ]
Avatar
Joined: 2007-06-11
2987 posts

Here you go:

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

 Signature 

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

 
Posted: 05 February 2013 07:17 AM   [ # 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
2