Quantcast

Zend_Db_Table_Select - Select query cannot join with another table

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Zend_Db_Table_Select - Select query cannot join with another table

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: 071.9208116  Fax: 071.2814722
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.
-------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Zend_Db_Table_Select - Select query cannot join with another table

weierophinney
Administrator
-- 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: 071.9208116  Fax: 071.2814722
> 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/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Zend_Db_Table_Select - Select query cannot join with another table

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: 071.9208116  Fax: 071.2814722
>> 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/
>

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Zend_Db_Table_Select - Select query cannot join with another table

weierophinney
Administrator
-- 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: 071.9208116  Fax: 071.2814722
>>> 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/
Loading...