Re: Complex SQL queries

On Wed, Oct 27, 2010 at 12:16 PM, joe_90 <joe.warren.meeks@gmail.com> 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 cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en