Zend DB: joinLeft problem

Question

I am trying to construct this query with Zend DB:

SELECT
    `s` . * ,
    `a` . *
FROM 
    `shr_statement` AS  `s`
LEFT JOIN  
    `shr_answer` AS a`
ON 
    a.statement_id = s.statement_id 
    AND
    a.user_id =1
WHERE
    (s.language =  'en_US') AND (s.is_active =1 )
ORDER BY 
    `order` ASC

I read about a possible issue with the double 'ON' statement ...

Thanks!

Answer 1

It seems you're missing the left backtick from your alias.. ie it should be "shr_answer AS a".

Or even better get rid of all your backticks. They are evil and unnecessary! ;)

Answer 2

This should do the trick, where $db is an instance of your Zend_Db adapter

$select = $db->select()
    ->from(array('s' => 'shr_statement'))
    ->joinLeft(array('a' => 'shr_answer'), 'a.statement_id = s.statement_id')
    ->where('s.language = ?', 'en_US')
    ->where('s.is_active = ?', 1)
    ->where('a.user_id = ?', 1)