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

I have another scenario here joining from a look up table (eventvenue) to its
parent tables (venue & event).

The sql query for it is:

// this runs as expected on my query client
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;

the eventvenue table has the columns(EventID,VenueID) which just acts as a
look up for events with matching venues.

class EventVenue extends Zend_Db_Table_Abstract {

// returns all events in the db
public function getAllEvents() {

$sql = $this->select()->setIntegrityCheck(false)
->from('$this')
->joinLeftUsing('venue', 'VenueID')
->joinLeftUsing('event', 'EventID')
->joinLeftUsing('area', 'AreaID')
->joinLeftUsing('city', 'CityID');

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

}
}

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 like i have for my sql query:

//where e=event, ev=eventvenue
[code]
SELECT e.eventName, v.VenueName, c.City
FROM EventVenue ev
...
LEFT JOIN Area a
ON e.areaid=a.areaid LEFT JOIN City c ON
a.cityid=c.cityid;

[/code]

I have tried including the area & city table names in the from() function
but to no avail.

Also from the function definition of the joinLeftUsing(table, join,
[columns]), what is the 'join' parameter for? I dont seem to get it

Thanks will appreciate this.

dele454 wrote:
>
> Thank you so much Bill. I missed out on the typo. In terms of the
> parameters i used for the joinLeftUsing(); function, does this function
> make calls to the rules i set for the referenced tables? What i have now
> is the exact table name and column name of the table 'venuecategory'.
>
>
>
>
> Bill Karwin wrote:
>>
>>
>> dele454 wrote:
>>>
>>> $sql = $this->select();
>>> ->joinLeftUsing('venuecategory', 'CategoryID');
>>>
>>
>> This is simply a syntax error based on a typo. You have ended a line
>> with a semicolon and then expected to continue the expression on the next
>> line with `->' object notation.
>>
>> This is a rudimentary mistake and you should be able to resolve it
>> without posting to a mailing list. Are you familiar with reviewing PHP
>> error messages in your Apache log file?
>>
>> Correcting the syntax error, there is still a problem, because
>> Zend_Db_Table_Select requires you to specify a from() clause before you
>> can join to another table. I don't know why this was designed this way.
>>
>> You also need to set integrity check to false to permit joins. So your
>> function should be as follows:
>>
>> public function getAllVenues() {
>> $sql = $this->select()->setIntegrityCheck(false)
>> ->from($this)
>> ->joinLeftUsing('venuecategory', 'CategoryID');
>> $result = $this->fetchAll($sql)->toArray();
>> return $result;
>> }
>>
>> Regards,
>> Bill Karwin
>>
>
>

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