CakePHP style database query results in Zend Framework using Zend_Db_Table?

Question

If you have a complex SQL query involving many joins (for example returning Articles with their associated many to many Tags) is there anything in Zend Framework that will produce the lovely CakePHP style database results:

Array
(
    [0] => Array
        (
            [ModelName] => Array
                (
                    [id] => 83
                    [field1] => value1
                    [field2] => value2
                    [field3] => value3
                )

            [AssociatedModelName] => Array
                (
                    [id] => 1
                    [field1] => value1
                    [field2] => value2
                    [field3] => value3
                )

        )
)

I don't mind if it's an object rather than an array, I just wondered if by using Zend_Db_Table to build a SELECT JOIN query I could save some leg work and get some nicely formatted results.

Here is the kind of code I'm using to build the query:

$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);

As someone who has also gone from a Cake background to having to work with ZF, I feel your pain and would love to see the answers you get. — Dunhamzzz, Jan 09 '12 at 17:24
Well it's certainly not out of choice, I can tell you that much. Inheriting code, what fun. Let's see if we get any feedback. This is about as close as I've seen so far: http://mattmccormick.ca/2010/04/24/how-to-easily-create-models-and-table-relationships-in-zend-framework/ — BeesonBison, Jan 09 '12 at 17:35
I think at the rate I'm going with Zend Framework I will be resorting to using SQL query based solutions like MySQL GROUP_CONCAT http://stackoverflow.com/questions/2358300/mysql-join-group-concat-second-table — BeesonBison, Jan 09 '12 at 17:48
There's also this SQL based work-around (although it's nowhere close to answering the question from a ZF perspective) for LEFT JOINS and MySQL GROUP_CONCAT: http://stackoverflow.com/questions/4455958/mysql-group-concat-with-left-join — BeesonBison, Jan 09 '12 at 17:59

score 1 · Accepted Answer · answered Jan 15 '12 at 07:21

Nothing as pretty as what you're used to just the data I asked for. The normal Result would be a rowset object, however the ->toArray() is available for most *Zend_DbTable_Abstract* methods.

The $result->toArray() truncated and dumped using Zend_debug::dump():

Lead Tracks array(7) {
  [0] => array(9) {
    ["trackid"] => string(2) "24"
    ["weekendid"] => string(1) "8"
    ["shiftid"] => string(1) "1"
    ["bidlocationid"] => string(1) "1"
    ["qty"] => string(1) "2"
    ["lead"] => string(1) "1"
    ["bidloc"] => string(14) "out of service"
    ["deptcode"] => string(3) "491"
    ["stationid"] => string(1) "1"
}

The query:

where = $this->select(Zend_Db_Table::SELECT_WITH_FROM_PART)
                     ->setIntegrityCheck(FALSE);
    $where->where('track.bidlocationid = ?', $bidlocationId)
          ->where('lead = ?', $lead)
          ->join('bidlocation', 'bidlocation.bidlocationid = track.bidlocationid')
          ->where('bidlocation.stationid = ?', $stationId)
          ->order('shiftid ASC')
          ->order('weekendid ASC');

    $result = $this->fetchAll($where);

sorry, just utility :)

CakePHP style database query results in Zend Framework using Zend_Db_Table?

1 Answers1