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-13 13:18:30Z

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, 2010 at 13:18

answered Nov 13, 2010 at 10:35

simnom

2,6201 gold badge25 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Martin Over a year ago

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.

simnom Over a year ago

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.

Martin Over a year ago

That’s got it, thanks!! I am assuming you can't do this kind of thing when using Zend_Db_Table.

Phil Over a year ago

@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".

Alexar Over a year ago

@Shimnon: hey! your little empty array() saved me!!

Collectives™ on Stack Overflow

Zend Framework Query with Joins

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related