Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Problems with join()

  1. #1
    flowh is offline Junior Member
    Join Date
    Mar 2008
    Posts
    2

    Default Problems with join()

    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]$table = new Members();
    $select = $table->select();
    $select->join('groups', 'members.group_id=groups.id', 'groups.name');
    $rows = $table->fetchAll($select);
    [/PHP]

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

    Members.php:
    [PHP]class Members extends Zend_Db_Table_Abstract {
    protected $_name = 'members';
    }[/PHP]
    and Groups.php
    [PHP]class Groups extends Zend_Db_Table_Abstract {
    protected $_name = 'groups';
    }[/PHP]

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

    I hope someone can help me.

    Cheers,
    Florian

  2. #2
    garfield is offline Junior Member
    Join Date
    Mar 2008
    Posts
    8

    Default setintegritycheck

    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

  3. #3
    flowh is offline Junior Member
    Join Date
    Mar 2008
    Posts
    2

    Default Checking

    Thanks, garfield.
    I'll try this soon.

  4. #4
    walterheck is offline Junior Member
    Join Date
    Mar 2008
    Location
    Belo Horizonte, Brasil
    Posts
    4

    Default

    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.

  5. #5
    walterheck is offline Junior Member
    Join Date
    Mar 2008
    Location
    Belo Horizonte, Brasil
    Posts
    4

    Default

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

  6. #6
    ablock is offline Junior Member
    Join Date
    Mar 2008
    Posts
    6

    Default

    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?

  7. #7
    xentek is offline Senior Member
    Join Date
    Feb 2008
    Posts
    112

    Default

    Are you defining your relationships between the tables?

    Zend Framework: Documentation

  8. #8
    ablock is offline Junior Member
    Join Date
    Mar 2008
    Posts
    6

    Default

    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?

  9. #9
    mel
    mel is offline Junior Member
    Join Date
    Feb 2008
    Posts
    14

    Default

    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]
    $select = $this->select();
    $select->join('user', 'user.id = message.user_id')
    ->where('u.name LIKE ?', $name);
    [/PHP]

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

  10. #10
    mel
    mel is offline Junior Member
    Join Date
    Feb 2008
    Posts
    14

    Default

    And, I've just found a solution
    Adapted from a Nabble forum thread (second-to-last post):

    [PHP]$select = $this->select();
    $select->join('user', 'user.id = message.user_id', array())
    ->where('u.name LIKE ?', $name); [/PHP]

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

Page 1 of 2 12 LastLast

Similar Threads

  1. join with 3 tables
    By jake142 in forum Databases
    Replies: 0
    Last Post: 09-06-2009, 02:35 PM
  2. Zend_db_table and join
    By zf123 in forum General Q&A on Zend Framework
    Replies: 2
    Last Post: 11-21-2008, 11:11 AM
  3. complex join
    By EducatedFool in forum Databases
    Replies: 0
    Last Post: 09-24-2008, 01:27 PM
  4. join,left join and right join in zend_db_table
    By aniltc in forum Databases
    Replies: 0
    Last Post: 05-28-2008, 06:00 AM
  5. join problems. output isn't in the variable
    By garfield in forum Databases
    Replies: 2
    Last Post: 03-14-2008, 11:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •