Thread: Using Zend_Db_Table to return referenced values

hi,

I have two tables: Venue & VenueCategory table with def such as

Venue(VenueID, VenueName, CategoryID)
VenueCategory(CategoryID, CategoryName)

I want to convert my query in sql to using the Zend_Db_Table:

Code:

SELECT v.VenueName, c.Category FROM Venue v LEFTJOIN VenueCategory ON
v.CategoryID = c.CategoryID;

I have my model as:

Code:

<?php

        class Venues extends Zend_Db_Table_Abstract {
       
           //protected $_rowClass = 'Venue';
           protected $_primary = 'VenueID';
           protected $_name = 'venue';
           protected $_referenceMap     = array(
                'Category'         => array(
                                    'columns'       => 'CategoryID',
                                        'refTableClass' => 'venuecategory',
                                        'refColumns'    => 'CategoryID'
                         ),
                                 
                                 'Area'             => array(
                                    'columns'       => 'AreaID',
                                        'refTableClass' => 'area',
                                        'refColumns'    => 'AreaID',
                                       
                                  )
            );
                                 
                       
                // returns all venues in the db
                public function getAllVenues() {
               
               
                     $sql    = $this->select();
                              ->joinLeftUsing('venuecategory', 'CategoryID');

                    $result = $this->fetchAll($sql)->toArray();
                    return $result;

               
                }
}

The Question is how can I join the two tables like the query above to return all venues with matching category names. I want to use the joinLeftUsing() function from the the Zend_Db_Select class. The Reference Guide only gives samples with using joins on a rowset.

At the moment the page is blank in my view script - i know something is wrong but i cant figure it out. How can i use those arrays declared for the tables in my query statement?


Please help is needed.


Thanks