Zend_Db_Table_Select - Select query cannot join with another table

[Michele Brodoloni]

Hello,
i'm having troubles with table joins within Zend_Db_Table_Select.
I'm trying this code on my model:

class MyTable extends Zend_Db_Table
{
    protected $_name     = 'my_table';
    protected $_primary  = 'id';
    protected $_sequence = false;

    protected $_cols = array(
        'id',
        'descrizione',
        'indice'
    );

    public function listByCategory($cat_id)
    {
       $select = $this->select();
       $select->from(array('t1' => $this->_name),
                     array('id', 'descrizione'))
               ->join(array('t2' => 'my_table_2'),
                      't2.foreignKey = t1.id')
               ->where('t2.category_id = ?', $cat_id)
               ->order('indice ASC');

       // Which should translate into something like:
       // SELECT t1.id, t1.descrizione FROM my_table AS t1
       // LEFT JOIN my_table_2 AS t2 ON (t2.foreignKey = t1.id)
       // WHERE t2.category_id = {$cat_id} ORDER BY indice ASC;
    }
}

If i try to print out the SQL query using Zend_Db_Select::__toString()
method, i get this error:
Warning: Select query cannot join with another table in
/var/www/zendFramework/library/Zend/Db/Table/Select.php on line 191

Perhaps a db scheme may help understand better:

Table: my_table
+------+-------------+--------+
| id   | descrizione | indice |
+------+-------------+--------+
| VAL1 | desc. 1     | 0      |
+------+-------------+--------+
| VAL2 | desc. 2     | 1      |
+------+-------------+--------+

Table: my_table_2 (the table i'm trying to have a join with)
+-----+-------------+-------------+
| id  | foreignKey  | category_id |
+-----+-------------+-------------+
|   1 | VAL1        | CAT1        |
+-----+-------------+-------------+
|   2 | VAL2        | CAT1        |
+-----+-------------+-------------+
|   3 | VAL1        | CAT2        |
+-----+-------------+-------------+


I can't figure out where's the problem :(
Maybe i should use the $_referenceMap array in some way?

Hope you get the point.

Thank you all.

[Michele Brodoloni]

After posting i quickly found the solution on a forum:

$select = $this->select()
$select->setIntegrityCheck(false);

This works, but is it the right way?
Thanks


Michele Brodoloni ha scritto:

> Hello,
> i'm having troubles with table joins within Zend_Db_Table_Select.
> I'm trying this code on my model:
>
> class MyTable extends Zend_Db_Table
> {
>    protected $_name     = 'my_table';
>    protected $_primary  = 'id';
>    protected $_sequence = false;
>
>    protected $_cols = array(
>        'id',
>        'descrizione',
>        'indice'
>    );
>
>    public function listByCategory($cat_id)
>    {
>       $select = $this->select();
>       $select->from(array('t1' => $this->_name),
>                     array('id', 'descrizione'))
>               ->join(array('t2' => 'my_table_2'),
>                      't2.foreignKey = t1.id')
>               ->where('t2.category_id = ?', $cat_id)
>               ->order('indice ASC');
>
>       // Which should translate into something like:
>       // SELECT t1.id, t1.descrizione FROM my_table AS t1
>       // LEFT JOIN my_table_2 AS t2 ON (t2.foreignKey = t1.id)
>       // WHERE t2.category_id = {$cat_id} ORDER BY indice ASC;
>    }
> }
>
> If i try to print out the SQL query using Zend_Db_Select::__toString()
> method, i get this error:
> Warning: Select query cannot join with another table in
> /var/www/zendFramework/library/Zend/Db/Table/Select.php on line 191
>
> Perhaps a db scheme may help understand better:
>
> Table: my_table
> +------+-------------+--------+
> | id   | descrizione | indice |
> +------+-------------+--------+
> | VAL1 | desc. 1     | 0      |
> +------+-------------+--------+
> | VAL2 | desc. 2     | 1      |
> +------+-------------+--------+
>
> Table: my_table_2 (the table i'm trying to have a join with)
> +-----+-------------+-------------+
> | id  | foreignKey  | category_id |
> +-----+-------------+-------------+
> |   1 | VAL1        | CAT1        |
> +-----+-------------+-------------+
> |   2 | VAL2        | CAT1        |
> +-----+-------------+-------------+
> |   3 | VAL1        | CAT2        |
> +-----+-------------+-------------+
>
>
> I can't figure out where's the problem :(
> Maybe i should use the $_referenceMap array in some way?
>
> Hope you get the point.
>
> Thank you all.

--
-------------------------------------------------------------------------
Michele Brodoloni
XTNET SRL
Tel: [phone]  Fax: [phone]
e-mail: [hidden email]
http://www.xtnet.it
-------------------------------------------------------------------------
CONFIDENTIALITY NOTICE
This message and its attachments are addressed solely to the persons
above and may contain confidential information. If you have received
the message in error, be informed that any use of the content hereof
is prohibited. Please return it immediately to the sender and delete
the message. Should you have any questions, please contact us by
replying to [hidden email] Thank you.
-------------------------------------------------------------------------

[weierophinney]

-- Michele Brodoloni <[hidden email]> wrote
(on Tuesday, 20 May 2008, 11:58 AM +0200):
> After posting i quickly found the solution on a forum:
>
> $select = $this->select()
> $select->setIntegrityCheck(false);
>
> This works, but is it the right way?

Yes.

The rationale is that Rows returned via such a join have to be read-only
-- you can't update the joined table for data integrity and
normalization purposes. The flag you set above is basically your way of
telling Zend_Db_Table that you're aware of this restriction. (Setting
the flag simply enables Zend_Db_Table to perform the select; the Rows
remain locked to read-only, and will throw an exception if you attempt
to save them.)

BTW, I'm discussing with the Zend_Db developers having the above
flag setting be the default, as this catches many developers unawares.

> Michele Brodoloni ha scritto:
>> Hello,
>> i'm having troubles with table joins within Zend_Db_Table_Select.
>> I'm trying this code on my model:
>>
>> class MyTable extends Zend_Db_Table
>> {
>>    protected $_name     = 'my_table';
>>    protected $_primary  = 'id';
>>    protected $_sequence = false;
>>
>>    protected $_cols = array(
>>        'id',
>>        'descrizione',
>>        'indice'
>>    );
>>
>>    public function listByCategory($cat_id)
>>    {
>>       $select = $this->select();
>>       $select->from(array('t1' => $this->_name),
>>                     array('id', 'descrizione'))
>>               ->join(array('t2' => 'my_table_2'),
>>                      't2.foreignKey = t1.id')
>>               ->where('t2.category_id = ?', $cat_id)
>>               ->order('indice ASC');
>>
>>       // Which should translate into something like:
>>       // SELECT t1.id, t1.descrizione FROM my_table AS t1
>>       // LEFT JOIN my_table_2 AS t2 ON (t2.foreignKey = t1.id)
>>       // WHERE t2.category_id = {$cat_id} ORDER BY indice ASC;
>>    }
>> }
>>
>> If i try to print out the SQL query using Zend_Db_Select::__toString()  
>> method, i get this error:
>> Warning: Select query cannot join with another table in  
>> /var/www/zendFramework/library/Zend/Db/Table/Select.php on line 191
>>
>> Perhaps a db scheme may help understand better:
>>
>> Table: my_table
>> +------+-------------+--------+
>> | id   | descrizione | indice |
>> +------+-------------+--------+
>> | VAL1 | desc. 1     | 0      |
>> +------+-------------+--------+
>> | VAL2 | desc. 2     | 1      |
>> +------+-------------+--------+
>>
>> Table: my_table_2 (the table i'm trying to have a join with)
>> +-----+-------------+-------------+
>> | id  | foreignKey  | category_id |
>> +-----+-------------+-------------+
>> |   1 | VAL1        | CAT1        |
>> +-----+-------------+-------------+
>> |   2 | VAL2        | CAT1        |
>> +-----+-------------+-------------+
>> |   3 | VAL1        | CAT2        |
>> +-----+-------------+-------------+
>>
>>
>> I can't figure out where's the problem :(
>> Maybe i should use the $_referenceMap array in some way?
>>
>> Hope you get the point.
>>
>> Thank you all.
>
>
> --
> -------------------------------------------------------------------------
> Michele Brodoloni
> XTNET SRL
> Tel: [phone]  Fax: [phone]
> e-mail: [hidden email]
> http://www.xtnet.it
> -------------------------------------------------------------------------
> CONFIDENTIALITY NOTICE
> This message and its attachments are addressed solely to the persons
> above and may contain confidential information. If you have received
> the message in error, be informed that any use of the content hereof
> is prohibited. Please return it immediately to the sender and delete
> the message. Should you have any questions, please contact us by
> replying to [hidden email] Thank you.
> -------------------------------------------------------------------------
>

--
Matthew Weier O'Phinney
Software Architect       | [hidden email]
Zend - The PHP Company   | http://www.zend.com/

[Taylor Barstow]

Actually there's another wrinkle to this story as well.

In my application, I often want to select real, writeable rows from a  
table but use one or more joins to limit the found set.

For this I've found the following works:

$table_a->select()
         ->join('table_b', 'table_b.table_a_id=table_a.id', array())
         ->where('table_b.foo = ?', 'bar');

The last argument to join() tells Zend_Db_Select not to try to select  
any columns from the joined table, but the join clause still gets  
added.  This works and gives me back writeable rows from the "table_a"  
table.

Some might argue that I'd be better off using a view in this scenario,  
but there are actually some cases where this is impractical.

Taylor

On May 20, 2008, at 7:49 AM, Matthew Weier O'Phinney wrote:

> -- Michele Brodoloni <[hidden email]> wrote
> (on Tuesday, 20 May 2008, 11:58 AM +0200):
>> After posting i quickly found the solution on a forum:
>>
>> $select = $this->select()
>> $select->setIntegrityCheck(false);
>>
>> This works, but is it the right way?
>
> Yes.
>
> The rationale is that Rows returned via such a join have to be read-
> only
> -- you can't update the joined table for data integrity and
> normalization purposes. The flag you set above is basically your way  
> of
> telling Zend_Db_Table that you're aware of this restriction. (Setting
> the flag simply enables Zend_Db_Table to perform the select; the Rows
> remain locked to read-only, and will throw an exception if you attempt
> to save them.)
>
> BTW, I'm discussing with the Zend_Db developers having the above
> flag setting be the default, as this catches many developers unawares.
>
>
>> Michele Brodoloni ha scritto:
>>> Hello,
>>> i'm having troubles with table joins within Zend_Db_Table_Select.
>>> I'm trying this code on my model:
>>>
>>> class MyTable extends Zend_Db_Table
>>> {
>>>   protected $_name     = 'my_table';
>>>   protected $_primary  = 'id';
>>>   protected $_sequence = false;
>>>
>>>   protected $_cols = array(
>>>       'id',
>>>       'descrizione',
>>>       'indice'
>>>   );
>>>
>>>   public function listByCategory($cat_id)
>>>   {
>>>      $select = $this->select();
>>>      $select->from(array('t1' => $this->_name),
>>>                    array('id', 'descrizione'))
>>>              ->join(array('t2' => 'my_table_2'),
>>>                     't2.foreignKey = t1.id')
>>>              ->where('t2.category_id = ?', $cat_id)
>>>              ->order('indice ASC');
>>>
>>>      // Which should translate into something like:
>>>      // SELECT t1.id, t1.descrizione FROM my_table AS t1
>>>      // LEFT JOIN my_table_2 AS t2 ON (t2.foreignKey = t1.id)
>>>      // WHERE t2.category_id = {$cat_id} ORDER BY indice ASC;
>>>   }
>>> }
>>>
>>> If i try to print out the SQL query using  
>>> Zend_Db_Select::__toString()
>>> method, i get this error:
>>> Warning: Select query cannot join with another table in
>>> /var/www/zendFramework/library/Zend/Db/Table/Select.php on line 191
>>>
>>> Perhaps a db scheme may help understand better:
>>>
>>> Table: my_table
>>> +------+-------------+--------+
>>> | id   | descrizione | indice |
>>> +------+-------------+--------+
>>> | VAL1 | desc. 1     | 0      |
>>> +------+-------------+--------+
>>> | VAL2 | desc. 2     | 1      |
>>> +------+-------------+--------+
>>>
>>> Table: my_table_2 (the table i'm trying to have a join with)
>>> +-----+-------------+-------------+
>>> | id  | foreignKey  | category_id |
>>> +-----+-------------+-------------+
>>> |   1 | VAL1        | CAT1        |
>>> +-----+-------------+-------------+
>>> |   2 | VAL2        | CAT1        |
>>> +-----+-------------+-------------+
>>> |   3 | VAL1        | CAT2        |
>>> +-----+-------------+-------------+
>>>
>>>
>>> I can't figure out where's the problem :(
>>> Maybe i should use the $_referenceMap array in some way?
>>>
>>> Hope you get the point.
>>>
>>> Thank you all.
>>
>>
>> --
>> -------------------------------------------------------------------------
>> Michele Brodoloni
>> XTNET SRL
>> Tel: [phone]  Fax: [phone]
>> e-mail: [hidden email]
>> http://www.xtnet.it
>> -------------------------------------------------------------------------
>> CONFIDENTIALITY NOTICE
>> This message and its attachments are addressed solely to the persons
>> above and may contain confidential information. If you have received
>> the message in error, be informed that any use of the content hereof
>> is prohibited. Please return it immediately to the sender and delete
>> the message. Should you have any questions, please contact us by
>> replying to [hidden email] Thank you.
>> -------------------------------------------------------------------------
>>
>
> --
> Matthew Weier O'Phinney
> Software Architect       | [hidden email]
> Zend - The PHP Company   | http://www.zend.com/
>

[weierophinney]

-- Taylor Barstow <[hidden email]> wrote
(on Tuesday, 20 May 2008, 11:21 AM -0400):

> Actually there's another wrinkle to this story as well.
>
> In my application, I often want to select real, writeable rows from a  
> table but use one or more joins to limit the found set.
>
> For this I've found the following works:
>
> $table_a->select()
>         ->join('table_b', 'table_b.table_a_id=table_a.id', array())
>         ->where('table_b.foo = ?', 'bar');
>
> The last argument to join() tells Zend_Db_Select not to try to select  
> any columns from the joined table, but the join clause still gets added.  
> This works and gives me back writeable rows from the "table_a" table.

Ah, right -- that's where the integrity check occurs. If the result
would have columns from multiple tables, the integrity check occurs; if
not, then no integrity check is needed. Forgot to mention that earlier.
:-)

> Some might argue that I'd be better off using a view in this scenario,  
> but there are actually some cases where this is impractical.

Understandable.

> On May 20, 2008, at 7:49 AM, Matthew Weier O'Phinney wrote:
>
>> -- Michele Brodoloni <[hidden email]> wrote
>> (on Tuesday, 20 May 2008, 11:58 AM +0200):
>>> After posting i quickly found the solution on a forum:
>>>
>>> $select = $this->select()
>>> $select->setIntegrityCheck(false);
>>>
>>> This works, but is it the right way?
>>
>> Yes.
>>
>> The rationale is that Rows returned via such a join have to be read-
>> only
>> -- you can't update the joined table for data integrity and
>> normalization purposes. The flag you set above is basically your way  
>> of
>> telling Zend_Db_Table that you're aware of this restriction. (Setting
>> the flag simply enables Zend_Db_Table to perform the select; the Rows
>> remain locked to read-only, and will throw an exception if you attempt
>> to save them.)
>>
>> BTW, I'm discussing with the Zend_Db developers having the above
>> flag setting be the default, as this catches many developers unawares.
>>
>>
>>> Michele Brodoloni ha scritto:
>>>> Hello,
>>>> i'm having troubles with table joins within Zend_Db_Table_Select.
>>>> I'm trying this code on my model:
>>>>
>>>> class MyTable extends Zend_Db_Table
>>>> {
>>>>   protected $_name     = 'my_table';
>>>>   protected $_primary  = 'id';
>>>>   protected $_sequence = false;
>>>>
>>>>   protected $_cols = array(
>>>>       'id',
>>>>       'descrizione',
>>>>       'indice'
>>>>   );
>>>>
>>>>   public function listByCategory($cat_id)
>>>>   {
>>>>      $select = $this->select();
>>>>      $select->from(array('t1' => $this->_name),
>>>>                    array('id', 'descrizione'))
>>>>              ->join(array('t2' => 'my_table_2'),
>>>>                     't2.foreignKey = t1.id')
>>>>              ->where('t2.category_id = ?', $cat_id)
>>>>              ->order('indice ASC');
>>>>
>>>>      // Which should translate into something like:
>>>>      // SELECT t1.id, t1.descrizione FROM my_table AS t1
>>>>      // LEFT JOIN my_table_2 AS t2 ON (t2.foreignKey = t1.id)
>>>>      // WHERE t2.category_id = {$cat_id} ORDER BY indice ASC;
>>>>   }
>>>> }
>>>>
>>>> If i try to print out the SQL query using  
>>>> Zend_Db_Select::__toString()
>>>> method, i get this error:
>>>> Warning: Select query cannot join with another table in
>>>> /var/www/zendFramework/library/Zend/Db/Table/Select.php on line 191
>>>>
>>>> Perhaps a db scheme may help understand better:
>>>>
>>>> Table: my_table
>>>> +------+-------------+--------+
>>>> | id   | descrizione | indice |
>>>> +------+-------------+--------+
>>>> | VAL1 | desc. 1     | 0      |
>>>> +------+-------------+--------+
>>>> | VAL2 | desc. 2     | 1      |
>>>> +------+-------------+--------+
>>>>
>>>> Table: my_table_2 (the table i'm trying to have a join with)
>>>> +-----+-------------+-------------+
>>>> | id  | foreignKey  | category_id |
>>>> +-----+-------------+-------------+
>>>> |   1 | VAL1        | CAT1        |
>>>> +-----+-------------+-------------+
>>>> |   2 | VAL2        | CAT1        |
>>>> +-----+-------------+-------------+
>>>> |   3 | VAL1        | CAT2        |
>>>> +-----+-------------+-------------+
>>>>
>>>>
>>>> I can't figure out where's the problem :(
>>>> Maybe i should use the $_referenceMap array in some way?
>>>>
>>>> Hope you get the point.
>>>>
>>>> Thank you all.
>>>
>>>
>>> --
>>> -------------------------------------------------------------------------
>>> Michele Brodoloni
>>> XTNET SRL
>>> Tel: [phone]  Fax: [phone]
>>> e-mail: [hidden email]
>>> http://www.xtnet.it
>>> -------------------------------------------------------------------------
>>> CONFIDENTIALITY NOTICE
>>> This message and its attachments are addressed solely to the persons
>>> above and may contain confidential information. If you have received
>>> the message in error, be informed that any use of the content hereof
>>> is prohibited. Please return it immediately to the sender and delete
>>> the message. Should you have any questions, please contact us by
>>> replying to [hidden email] Thank you.
>>> -------------------------------------------------------------------------
>>>
>>
>> --
>> Matthew Weier O'Phinney
>> Software Architect       | [hidden email]
>> Zend - The PHP Company   | http://www.zend.com/
>>
>

--
Matthew Weier O'Phinney
Software Architect       | [hidden email]
Zend - The PHP Company   | http://www.zend.com/