ZF-4796: problem with left join

Issue Type: Improvement Created: 2008-11-04T15:40:17.000+0000 Last Updated: 2012-11-20T20:53:28.000+0000 Status: Closed Fix version(s): Reporter: Michael Virnstein (gigamouse) Assignee: None Tags: - Zend_Db_Select

Related issues: Attachments:


Assume i have the following mysql tables:

<pre class="highlight">
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:

<pre class="highlight">
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:

<pre class="highlight">
$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.


Posted by Rob Allen (rob) on 2012-11-20T20:53:28.000+0000

Bulk change of all issues last updated before 1st January 2010 as "Won't Fix".

Feel free to re-open and provide a patch if you want to fix this issue.

Have you found an issue?

See the Overview section for more details.


© 2006-2018 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.