"IS NULL" in Zend_Db_Table select not working

Question

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?

Have you tried Zend_Debug::dump($select->__toString());exit; to see the sql it generates? If you copy paste that sql-statement into mysql, do you get the correct results then? If not, what's different from the manually typed version? — PatrikAkerstrand
– PatrikAkerstrand, Commented Mar 10, 2010 at 14:49
Thank you Machine! Doing so I discovered that Zend was creating an inner join instead of the left join I needed. So, by using ->joinLeft() my issue was solved. — Peter
– Peter, Commented Mar 10, 2010 at 15:17

Peter · Accepted Answer · 2010-03-10 15:23:14Z

13

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.

answered Mar 10, 2010 at 15:23

Peter

1,2114 gold badges17 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Peter Over a year ago

@Karim, I can't! Not for 2 days. Stackoverflow only allows accepting your own answer after 2 days. :)

karim79 · Accepted Answer · 2010-03-10 14:59:34Z

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

answered Mar 10, 2010 at 14:59

karim79

343k67 gold badges420 silver badges409 bronze badges

1 Comment

Peter Over a year ago

Hey Karim, thanks for the input, but it is NULL in that column, not '' nor 0, I checked and just double checked. Currently tracing Machine's comment, he is onto something.

Collectives™ on Stack Overflow

"IS NULL" in Zend_Db_Table select not working

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related