"IS NULL" in Zend_Db_Table select not working

Peter picture Peter · Mar 10, 2010 · Viewed 12.1k times · Source

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?

Answer

Peter picture Peter · Mar 10, 2010

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.