2008年7月29日星期二

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

Funny enough the code works in my view when i replace:

$events['City'] // i get an error like an defined index City but i do have
a 'City' column on the City table. If it can retrieve the id of the city why
not the name?

with $event['CityID']; // shows the appropriate id of the city but i want
the name!

dele454 wrote:
>
> 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-tp18688983p18709216.html
Sent from the Zend DB mailing list archive at Nabble.com.

没有评论: