Yii查询生成器结果（带有连接和子查询的纯Sql）

问题描述：

I have this syntax on My Controller

$checkbook = Yii::app()->db->createCommand("SELECT count(b.bookingid) as booking FROM (SELECT :fname AS firstname, :mname AS middlename, :lname AS lastname, :dob AS dob) n 
                                                    LEFT JOIN passenger p ON (p.firstname=n.firstname AND p.middlename=n.middlename AND p.lastname=n.lastname AND p.dob=n.dob)
                                                    LEFT JOIN pax USING (passengerid)
                                                    LEFT JOIN booking b USING (bookingid)
                                                    LEFT JOIN journey USING (bookingid)
                                                    LEFT JOIN flight f USING (flightid)
                                                    WHERE f.origin = :origin AND f.destination = :destination AND f.departure BETWEEN :datestart AND :dateend");
        $checkbook->bindValue(":fname", $fname);
        $checkbook->bindValue(":mname", $mname);
        $checkbook->bindValue(":lname", $lname);
        $checkbook->bindValue(":dob", $dob);
        $checkbook->bindValue(":origin", $origin);
        $checkbook->bindValue(":destination", $destination);
        $checkbook->bindValue(":datestart", $datestart);
        $checkbook->bindValue(":dateend", $dateend);
        $checkbook->queryRow();

        //If Count Result 1, then Status True. If Count Result more than 1,then false. 
        if ($checkbook['booking'] == 1) {
            $status = true;
        } else {
            $this->actionDoubleBook();
            $status = false;
            return $status;
        }

But I got this Error.

Fatal error: Cannot use object of type CDbCommand as array in /home/apihost/public_html/goflight/protected/controllers/BookingController.php on line 653

Any idea? And How to make A Good Query Builder with SQL Syntax like that.

我在My Controller上有这种语法 p>

  $ checkbook =  Yii :: app（） - ＆gt; db-＆gt; createCommand（“SELECT count（b.bookingid）as booking FROM（SELECT：fname AS firstname，：mname AS middlename，：lname AS lastname，：dob AS dob）n \  n LEFT JOIN乘客p ON（p.firstname = n.firstname AND p.middlename = n.middlename AND p.lastname = n.lastname AND p.dob = n.dob）
 LEFT JOIN pax USING（passengerid）
  LEFT JOIN预订b USING（bookingid）
 LEFT JOIN旅程USING（bookingid）
 LEFT JOIN flight f USING（flightid）
 WHERE f.origin =：origin AND f.destination =：destination AND f.departure BETWEEN：datestart  AND：dateend“）; 
 $ checkbook-＆gt  ; bindValue（“：fname”，$ fname）; 
 $ checkbook-＆gt; bindValue（“：mname”，$ mname）; 
 $ checkbook-＆gt; bindValue（“：lname”，$ lname）; 
  $ checkbook-＆gt; bindValue（“：dob”，$ dob）; 
 $ checkbook-＆gt; bindValue（“：origin”，$ origin）; 
 $ checkbook-＆gt; bindValue（“：destination”，$ destination）  ）; 
 $ checkbook-＆gt; bindValue（“：datestart”，$ datestart）; 
 $ checkbook-＆gt; bindValue（“：dateend”，$ dateend）; 
 $ checkbook-＆gt; queryRow（）; \  n 
 //如果计数结果1，则状态为真。 如果Count Result大于1，则为false。  
 if（$ checkbook ['booking'] == 1）{
 $ status = true; 
} else {
 $ this-＆gt; actionDoubleBook（）; 
 $ status = false; 
 return $ 状态; 
} 
  code>  pre> 
 
 但是我遇到了这个错误。   p> 
 
 
 致命错误：无法在第653行的/home/apihost/public_html/goflight/protected/controllers/BookingController.php中使用CDbCommand类型的对象作为数组
  code  >  pre> 
 
 有什么想法吗？ 以及如何使用SQL语法创建一个好的查询生成器。 p> 
  div>

答

You have to use the result.. queryRow() will not change the query object itself but returns the result. Therefore use this

$result = $checkbook->queryRow();

then

 if ($result['booking'] == 1) {
            $status = true;
        } else {
            $this->actionDoubleBook();
            $status = false;
            return $status;
        }

Yii查询生成器结果（带有连接和子查询的纯Sql）

相关推荐