On Wed, Oct 27, 2010 at 12:16 PM, joe_90 <[email protected]> wrote: > Hey guys, > > I've got a controller, which needs to do a complex SQL query: > > > function view($seatsRequired) { > $this->set('seatavail', $this->Seatavail->query(" > SELECT ThtId,ShoId,PerTime,SroID,firstUnused, IF(mincid IS NULL, > IFNULL(did,firstUnused),mincid-1) AS lastUnused > FROM ( > SELECT > first.ThtId,first.ShoId,first.PerTime,first.SroID,first.StaID AS > firstUnused, MIN(c.StaID) AS mincid, MAX(d.StaID) AS did > FROM ( > SELECT a.ThtId,a.ShoId,a.PerTime,a.SroID,a.StaID > FROM seatavail a > LEFT JOIN seatavail b ON a.StaID=b.StaID + 1 AND a.SroID = > b.SroID > WHERE a.StaStatus=0 AND (b.StaID IS NULL OR b.StaStatus=1) > AND a.ThtID = 500 > ) AS first > LEFT JOIN seatavail c ON first.StaID<c.StaID AND c.StaStatus=1 > AND c.SroID = first.SroID > LEFT JOIN seatavail d ON first.StaID<d.StaID AND d.StaStatus=0 > AND d.SroID = first.SroID > GROUP BY firstUnused > ) AS seatavail > HAVING lastUnused-firstUnused>=$seatsRequired-1;" > )); > $this->set(compact('seatavail')); > $this->detectagent(); > } > > Now, running the query by hand works as expected and outputs the right > data. However, from within Cake, I get : > > Warning (2): get_class() expects parameter 1 to be object, array given > [CORE/cake/libs/xml.php, line 200] > and > Fatal error: Call to undefined method Xml::addAttribute() in /usr/ > share/php/cake/libs/xml.php on line 275 > > Doing the same with a simple query works, so I believe I have the > syntax right. > > I can't understand what the problem is, the Seatavail model is generic > enough that it should work: > > class Seatavail extends AppModel { > > var $name = 'Seatavail'; > var $useTable = 'seatavail'; > var $primaryKey = 'ThtId'; > > } > > > Any pointers?
Create a database view. You'd be far better off letting the DB handle this than to have Cake wrestle with it. Even taking caching into consideration, a view would be superior as it's compiled. Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
