Problems with join()

flowh · #1 (**permalink**) 03-07-2008, 05:28 PM

Hello.

I have the following problem:

I want to use Zend_Db_Table_Select to join two tables, but I always get this error message:

Code:

Warning: Select query cannot join with another table in /home/.../Zend/Db/Table/Select.php on line 191

Fatal error: Uncaught exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[42000]: Syntax error or access violation: 1065 Query was empty' in /home/.../Zend/Db/Statement/Pdo.php:238
Stack trace: #0 /home/.../Zend/Db/Statement.php(283): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/.../Zend/Db/Adapter/Abstract.php(406): Zend_Db_Statement->execute(Array)
#2 /home/.../Zend/Db/Adapter/Pdo/Abstract.php(206): Zend_Db_Adapter_Abstract->query(Object(Zend_Db_Table_Select), Array)
#3 /home/.../Zend/Db/Table/Abstract.php(1184): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Table_Select))
#4 /home/.../Zend/Db/Table/Abstract.php(1039): Zend_Db_Table_Abstract->_fetch(Object(Zend_Db_Table_Select))
#5 /var/www/.../test/test.php(26): Zend_Db_Table_Abstract->fetchAll(Object(Zend_Db_Table_Select))
#6 {main} thrown in /home/.../Zend/Db/Statement/Pdo.php on line 238

This is the code I'm using:

PHP Code:


			
$table = new Members();

$select = $table->select();

$select->join('groups', 'members.group_id=groups.id', 'groups.name');

$rows = $table->fetchAll($select);

My database has the following tables:
members: id,group_id,[...]
groups: id,name

Members.php:

PHP Code:


			
class Members extends Zend_Db_Table_Abstract {

    protected $_name = 'members';

}

and Groups.php

PHP Code:


			
class Groups extends Zend_Db_Table_Abstract {

    protected $_name = 'groups';

}

I hope I gave all relevant information; if not, please tell me!

I hope someone can help me.

Cheers,
Florian

garfield · #2 (**permalink**) 03-10-2008, 01:06 PM

hi, i was told you need to set setIntegrityCheck to false
i also found an article about improvements in zend_db_table:
Zend Framework in Action Zend_Db_Table_Abstract in version 1.5

flowh · #3 (**permalink**) 03-10-2008, 01:20 PM

Thanks, garfield.
I'll try this soon.

walterheck · #4 (**permalink**) 03-14-2008, 07:02 PM

Does this property select() only exist in specific versions of ZF (i'm guessing 1.5?)? When I try to use it in my version 1.0.4 it says this is not a property.

walterheck · #5 (**permalink**) 03-14-2008, 07:12 PM

found my own answer. It is indeed only available in 1.5

ablock · #6 (**permalink**) 03-21-2008, 09:31 PM

Hi, I've been trying a similar thing, with setIntegrityCheck(false).
I have two tables, Prizes and Products (Prizes being a type of product, and they share the same primary key), which need to be joined together. I've been trying something like this:
$select->setIntegrityCheck(false)->joinNatural('Products')......
where $select is a zend_db_table_select from the zend_db_table for prizes
and I get a result that looks like this: SELECT `Products`.* FROM `Products`
when I echo out the select. Why is this happening?

xentek · #7 (**permalink**) 03-24-2008, 03:39 PM

Are you defining your relationships between the tables?

Zend Framework: Documentation

ablock · #8 (**permalink**) 03-25-2008, 01:09 PM

I defined my relationships, I'll double check that I did it correctly (in this case Products is a dependent table for Prizes?). If this is done correctly, would that enable me to join, without disabling the reference check?

mel · #9 (**permalink**) 06-03-2008, 10:48 AM

I think I'm having the same problem as the OP. I have 2 tables with correctly defined relationships, message and user. I want to get all messages (as a RowSet) for all users who meet certain criteria. Sort of like:

SELECT m.* FROM messages m
JOIN users u on u.id = m.user_id
WHERE u.name LIKE 'fred%'

At the moment, I'm doing (inside the Message class, ignoring quoting):

PHP Code:


			
$select = $this->select();

$select->join('user', 'user.id = message.user_id')

         ->where('u.name LIKE ?', $name);

My question: Is this possible, and how??? If it's not possible, it seems to me a major failing of the framework.

mel · #10 (**permalink**) 06-03-2008, 11:12 AM

And, I've just found a solution

Adapted from a Nabble forum thread (second-to-last post):

PHP Code:


			
$select = $this->select();

$select->join('user', 'user.id = message.user_id', array())

         ->where('u.name LIKE ?', $name);

The fix was adding an empty columns array to the join. Hope this helps someone...

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode