ZF-4796: problem with left join
Assume i have the following mysql tables:
create table product ( product_id int(11) NOT NULL auto_increment, product_no varchar(20) NOT NULL, primary key (product_id) ) create table language ( language_id int(11) NOT NULL auto_increment, code char(2) NOT NULL, primary key (language_id) ) create table product_description ( product_id int(11) NOT NULL, language_id int(11) NOT NULL, description varchar(100) NOT NULL, primary key (product_id, language_id) )
now i want to query all products and their english description. If a product doesn't have an english description, it should be returned anyway. This is the desired query:
select p.*, pd.description from product as p left join product_description as pd join language as l on pd.language_id = l.language_id and l.code = 'en' on p.product_id = pd.product_id
at the moment there's only the following workaround:
$select = $db->select(); $select ->from(array('p' => 'product')); $select->joinLeft(array('pd' => 'product_description'), '', 'description'); $select->join(array('l' => 'language'), 'pd.language_id = l.language_id and code = \'en\' ON p.product_id = pd.product_id', 'description');
i think there should be better way to do that, because adding the ON-clause in the join of another table isn't that clean.