3

I have a query, that does a LEFT JOIN on a subselect. This query is run in a high load environment and performs within the set requirements. The query (highly simplified) looks like:

SELECT
  table_A.pKey
, table_A.uKey
, table_A.aaa
, table_B.bbb
, alias_C.ccc
, alias_C.ddd
FROM table_A
INNER JOIN table_B ON table_A.pKey = table_B.pKey
LEFT JOIN (

    SELECT
      table_X.pKey
    , table_X.ccc
    , table_Y.ddd
    FROM table_X
    INNER JOIN table_Y ON table_X.pKey = table_Y.pKey

  ) AS alias_C ON table_A.uKey = alias_C.pKey;

(for various reasons, it is not possible to rewrite the subselect as a (direct) LEFT JOIN).

Now, I cannot get the LEFT JOIN on subselect to work with Zend_Db_Select. I've tried everything I could come up with, but it does not work.


So my question is:

  • Is it not possible to do a query as described above with Zend_Db_Select?
  • What syntax do I need to get it to work within Zend Framework?
Jacco
  • 23,534
  • 17
  • 88
  • 105

2 Answers2

8

I think that it should work like this:

$subselect = $db->select->from(array('x' => 'table_X'), array('x.pKey', 'x.ccc', 'y.ddd'), 'dbname')
                        ->join(array('Y' => 'table_Y'), 'x.pkey = y.pkey', array(), 'dbname');

$select = $db->select->from(array('a' => 'table_A'), array(/*needed columns*/), 'dbname')
                     ->join(array('b' => 'table_B'), 'a.pkey = b.pkey', array(), 'dbname')
                     ->joinLeft(array('c' => new Zend_Db_Expr('('.$subselect.')'), 'c.pkey = a.ukey', array())

I haven't tried it but I believe it'll work.

Splendor
  • 1,386
  • 6
  • 28
  • 62
Jakub Truneček
  • 8,800
  • 3
  • 20
  • 35
  • Does that mean it needs to actually execute 2 queries? – Jacco Jul 12 '11 at 12:21
  • 1
    No it will produce one query. Zend_Db_Select is just fluent wrapper to create query. It contains method `toSting()` or `__toString()`. You can try some debugs by calling something like `die($select)` and it will print the query in dialect of db you use. – Jakub Truneček Jul 12 '11 at 12:51
  • 1
    great `new Zend_Db_Expr('('.$subselect.')')` is the key, very useful also on Magento ! – WonderLand Apr 16 '14 at 12:27
  • thanks - very helpful - just missing the closing bracket in the first array of the last line - it should be: `->joinLeft(array('c' => new Zend_Db_Expr('('.$subselect.')')), 'c.pkey = a.ukey', array())` - can't edit it as SO requires > 6 char change – goredwards Jun 19 '15 at 18:57
1

... ->joinLeft(array('c' => new Zend_Db_Expr('(' . $subselect->assemble() . ')'), 'c.pkey = a.ukey', array())

Andryi Ilnytskyi
  • 36
  • 3