« Return to Thread: Zend_Db_Table_Select - Select query cannot join with another table

Re: Zend_Db_Table_Select - Select query cannot join with another table

by weierophinney :: Rate this Message:

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

« Return to Thread: Zend_Db_Table_Select - Select query cannot join with another table