[#ZF-1856] Using subquery in from() is broken because of new schema logic - Zend Framework Issue Tracker

Details

Type: Bug
Status: Resolved
Priority: Major
Resolution: Won't Fix
Affects Version/s: 1.0.1
Fix Version/s: 1.0.4
Component/s: Zend_Db_Select
Labels:
None

Fix Version Priority:
Should Have

Description

Using a subquery as the table argument in the from() call used to work fine, like so:

$select = $db->select();
$select->from("(select people.id from people)", "count(id)");

I know there are cleaner ways to get the same result, but this is a simplified version and in some cases it is necessary to do a select like this, for example when doing your own pagination you may want to put the sql from one query into a call like this to find out the number of rows returned from it.

When doing this, if the code in the table argument contains a period, the new schema code on line 358 in Select.php will break the query:

// Schema from table name overrides schema argument
if (false !== strpos($tableName, '.')) { list($schema, $tableName) = explode('.', $tableName); }

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Thomas Weidner added a comment - 16/Aug/07 1:30 PM

Assigned to Bill

Show

Thomas Weidner added a comment - 16/Aug/07 1:30 PM Assigned to Bill

Hide

Permalink

julien PAULI added a comment - 27/Nov/07 2:24 PM

Same behavior with subselect queries with Zend_Db_Select :

$db->select()
->from ('table1', array('t1_label'))
->joinInner(
      array('T2' => new Zend_Db_Expr (
         '('.
         $db->select()
         ->from('table2', array('t2_label'))
         ->where('condition')
         .')'
      )),
      'table1.t2_id = T2.t2_id',
      array('t2_label')
)

breaks it as well :

`(SELECT
``table2```.```t2_label``
FROM ``table2``
WHERE condition)`

patch could be :

if (!$tableName instanceof Zend_Db_Expr && false !== strpos($tableName, '.')) {
            list($schema, $tableName) = explode('.', $tableName);
        }

Show

julien PAULI added a comment - 27/Nov/07 2:24 PM Same behavior with subselect queries with Zend_Db_Select :

$db->select()
->from ('table1', array('t1_label'))
->joinInner(
      array('T2' => new Zend_Db_Expr (
         '('.
         $db->select()
         ->from('table2', array('t2_label'))
         ->where('condition')
         .')'
      )),
      'table1.t2_id = T2.t2_id',
      array('t2_label')
)

breaks it as well : `(SELECT ``table2```.```t2_label`` FROM ``table2`` WHERE condition)` patch could be :

if (!$tableName instanceof Zend_Db_Expr && false !== strpos($tableName, '.')) {
            list($schema, $tableName) = explode('.', $tableName);
        }

Hide

Permalink

Simon Mundy added a comment - 18/Jan/08 9:38 PM

This use-case can be avoided by using the Zend_Db_Expr object to enclose the subquery.

Show

Simon Mundy added a comment - 18/Jan/08 9:38 PM This use-case can be avoided by using the Zend_Db_Expr object to enclose the subquery.

People

Assignee:

Simon Mundy

Reporter:

Bill Monkman

Vote (0)

Watch (2)

Dates

Created:

15/Aug/07 5:51 PM

Updated:

26/Feb/08 12:55 PM

Resolved:

18/Jan/08 9:38 PM

Time Tracking

Estimated:

Remaining:

Logged:

Not Specified