Re: [fw-db] Using the Zend_Db_Table to return referenced row values

dele454 wrote:
>
> The table join on this model only works up until event. How can i connect
> the AreaID from the event table to that of Area, then from Area to City to
> complete the join ...
>

Your query is this:

SELECT e.eventName, v.VenueName, c.City
FROM EventVenue ev
LEFT JOIN Event e ON ev.eventid=e.eventid
LEFT JOIN Venue v ON ev.venueid=v.venueid
LEFT JOIN Area a ON e.areaid=a.areaid
LEFT JOIN City c ON a.cityid=c.cityid;

Here's how I'd write it using the select interface:

$sql = $this->select()->setIntegrityCheck(false)
->from($this, array() )
->joinLeftUsing( array('e'=>'Event'), 'EventId', array('EventName') )
->joinLeftUsing( array('v'=>'Venue'), 'VenueId', array('VenueName') )
->joinLeft( array('a'=>'Area'), 'e.AreaId = a.AreaId', array() )
->joinLeft( array('c'=>'City'), 'a.CityId = c.CityId', array('City') );

It seems that Zend_Db_Select::joinLeftUsing() assumes that you're joining to
the first table in the FROM clause. It produces SQL with a standard
equi-join instead of the USING clause. I've logged a bug for this:
http://framework.zend.com/issues/browse/ZF-3792

Regards,
Bill Karwin
--
View this message in context: http://www.nabble.com/Using-the-Zend_Db_Table-to-return-referenced-row-values-tp18688983p18716475.html
Sent from the Zend DB mailing list archive at Nabble.com.