Replicate a MySQL LEFT OUTER JOIN with Zend_Db_Select

Question

I have tried over and over again to replicate my MySQL query using the Zend_Db_Select object and I can't replicate the results in Zend.

The MySQL query:

SELECT s.id, s.project_id 
FROM staging s 
LEFT OUTER JOIN results r ON s.id = r.id 
WHERE r.id IS NULL

What I have in Zend:

$query = $db->select()
           ->from(array('s' => 'staging'), array('id', 'project_id'))
           ->joinLeft(array('r' => 'results'), 's.id = r.id')
           ->where('r.id IS NULL');

The output of the query is giving me one empty row of results in Zend, and the correct rows directly in MySQL.

Answer 1

Apparently I left out a required 3rd parameter in the leftJoin, in this case an empty array array().

So, now my final working code looks like this:

$query = $db->select()
           ->from(array('s' => 'staging'), array('id', 'project_id'))
           ->joinLeft(array('r' => 'results'), 's.id = r.id', array())
           ->where('r.id IS NULL');

Here is the Zend_Db_Select documentation.