"IS NULL" in Zend_Db_Table select not working
Solution 1
The solution was to be found in Machine's comment on my original post. Doing what he suggested I noticed that Zend created an inner join as I was using the wrong select method, so:
$select = $this->getDbTable()->select(Zend_Db_Table::SELECT_WITH_FROM_PART)
->setIntegrityCheck(false)
->joinLeft('images', 'images.oldFilename =
availablePictures.filename')
->where('images.ref IS NOT NULL');
$resultSet = $this->getDbTable()->fetchAll( $select );
is how it should be.
Solution 2
My thinking is it has to do with the way MySql decides what is NULL and what isn't. Is it possible that the results you are expecting have a default assignment of the empty string ''
or 0
in the images.ref
column? MySql does not treat those as NULLs. Have a look here:
http://dev.mysql.com/doc/refman/4.1/en/working-with-null.html
Peter
Updated on June 07, 2022Comments
-
Peter almost 2 years
I'm trying to do a join on 2 tables in Zend, using the DbTable / model / mapper structure. If, in my mapper, I do this:
$select = $this->getDbTable()->select(Zend_Db_Table::SELECT_WITH_FROM_PART) ->setIntegrityCheck(false) ->join('images', 'images.oldFilename = availablePictures.filename') ->where('images.ref IS NOT NULL'); $resultSet = $this->getDbTable()->fetchAll( $select );
it works like a charm, but if I try the same thing with IS NULL instead of NOT NULL, I get nothing where I should get a result set of several rows, just like when I try it directly in MySQL with
SELECT * FROM ( `availablePictures` AS a LEFT JOIN `images` AS i ON a.filename = i.oldFilename ) WHERE i.`ref` IS NULL
I get the impression Zend doesn't like my IS NULL or am I doing something wrong?