« 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 Taylor Barstow
::
Rate this Message:
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 <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/
>
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 <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
| Free embeddable forum powered by Nabble | Forum Help |
