Re: Zend_Db_Table_Select - Select query cannot join with another table

> 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.

> On May 20, 2008, at 7:49 AM, Matthew Weier O'Phinney wrote:
>
>> -- Michele Brodoloni <michele.brodoloni@...> 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: 071.9208116  Fax: 071.2814722
>>> e-mail: michele.brodoloni@...
>>> 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 postmaster@... Thank you.
>>> -------------------------------------------------------------------------
>>>
>>
>> --
>> Matthew Weier O'Phinney
>> Software Architect       | matthew@...
>> Zend - The PHP Company   | http://www.zend.com/
>>
>