Re: Complex SQL queries

On Oct 27, 6:16 pm, joe_90 <joe.warren.me...@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]

how is the xml error related to your use of query.

using query inside a controller is pretty anti-MVC - at the very least
you should put the unmodified query into your model and call it from
the controller as:
$foo = $this->Model->functionICreated($params);

remember: fat models, thin controllers.

AD

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