Zend Framework Query with Joins

Question

I am trying to replicate this query using zend framework:

SELECT 
    activitytype.description, 
    activity.datecompleted

FROM
    clientactivity
INNER JOIN activity
    ON activity.activityID = clientactivity.activityid
INNER JOIN activitytype
    ON activitytype.activitytypeid = activity.activitytypeid

WHERE
    clientactivity.clientid = 100

This is what I have so far:

$select = $dbTable->select(Zend_Db_Table::SELECT_WITH_FROM_PART);

$select->setIntegrityCheck(false);
$select->where('clientactivity.clientid = ?', $clientID);

$select->join('activity', 'activity.activityid = clientactivity.activityid');
$select->join('activitytype', 'activitytype.activitytypeid = activity.activitytypeid');

$select->columns(array('activitytype.description', 'activity.datecompleted'));

I seem to be having problems with the columns option, it doens't seem to be limiting the columns and I am ending up with clientactivity.* etc in the column list in the query.

What am I doing wrong?

Thanks, Martin

simnom · Accepted Answer · 2010-11-13T13:18:30.720

1

Try instead of the $select->columns();

$select->from('activitytype.description', 'activity.datecompleted');

Reference - http://framework.zend.com/manual/en/zend.db.select.html

UPDATE:

This example makes us of a generic database handler:

$db = Zend_Db::factory('Pdo_Mysql', array(
            'host'     => '127.0.0.1',
            'username' => 'yourusername',
            'password' => 'somepassword',
            'dbname'   => 'yourdbname'
        ));

 $select = $db->select(Zend_Db_Table::SELECT_WITH_FROM_PART);
 $select->from('tableName','fieldName')
         ->join('joinTable', 'joinTable.keyId = tableName.keyId',array())
         ->where('tableName.userId = ?', $userId);

 $resultSet = $db->fetchAll($select);

The key piece is the blank array at the end of the join statements that specifies no records to be returned from the joined table.

edited Nov 13 '10 at 13:18

answered Nov 13 '10 at 10:35

simnom

2,590
1
24
34

I'm afraid that doesn't help, gives an error at first, when I make the fields an array and add null as the table name I get the fields in the list but still have the clientactivity.* etc. Thanks. – Martin Nov 13 '10 at 11:13
Hi, Are you using Zend_Db_Table to creat $select? Think this would have a bearing on the results with clientactivity being included by default. I'll update the answer with a working piece of code I've just knocked up using Zend_Db_Select. – simnom Nov 13 '10 at 13:14
That’s got it, thanks!! I am assuming you can't do this kind of thing when using Zend_Db_Table. – Martin Nov 13 '10 at 14:00
@Martin As Zend_Db_Table represents a physical DB table in OOP and you are not selecting any columns from that table, it's not really a case of "you can't", more a case of "you shouldn't". – Phil Nov 15 '10 at 01:31
@Shimnon: hey! your little empty array() saved me!! – Alexar Dec 21 '10 at 02:04

Zend Framework Query with Joins

1 Answers1