Createcommand Is Not Producing Proper Result

Yii 1.1 General Discussion
1

Hi,

i have facing some strength issue with


Yii::app()->db->createCommand()

.

i have following query. when i runs it on sql command line its giving me proper result but when i am trying it with createCommand() its showing somethings else only.




         $sql = "SELECT * from project p join (select * from project where id=:PROJECTID) 

             pp ON p.category=pp.category ORDER BY RAND() limit 4";

        

        $bindValues = array(

            ':PROJECTID' => $projectId,

        );

        

        $kal = Yii::app()->db

                    ->createCommand($sql)

                    ->bindValues($bindValues)

                    ->queryAll();

       

        echo '<pre>', print_r($kal); exit;

Can anyone help me to solve/understand this?

Thanks in advance

2

What specifically is wrong with the result you’re getting?

3

There is difference in result between Sql and createCommand.

i have tried running following query through createCommand() and Sql Command Prompt (Phpmyadmin).




        $sql = "SELECT * FROM user_fund_project ufp

                JOIN reward ON reward.id = ufp.reward_id 

                JOIN project ON project.id = ufp.project_id 

                WHERE ufp.id = :UFID";


        $funds = $this->fetch($sql,array(':UFID' => $ufid),'queryRow');


     //fetch function

        public function fetch($sql="", $bindValues = array(),$method = 'queryAll') {

              return Yii::app()->db

                    ->createCommand($sql)

                    ->bindValues($bindValues)

                    ->{$method}();

    }

Getting following difference :

CreateCommand()

return 58 Fields <?php count($funds); ?>

SQL Prompt

return 63 fields

The fields difference goes the following way

  • sql returned 3 different id column (1 from user_fund_project, 1 from reward and 1 from project) where createCommand() returned 1 id column, others got overwritten.

  • sql returned 2 different user_id column(1 from reward, 1 from user_fund_project) where createCommand() returned 1 user_id column, others got overwritten.

  • sql returned 2 different project_id column(1 from reward, 1 from user_fund_project) where createCommand() returned 1 project_id column, others got overwritten.

  • sql returned 2 different status column(1 from project, 1 from user_fund_project) where createCommand() returned 1 status column, others got overwritten.

i think it’s a bug in createCommand().

Thanks

4

It’s not a bug in createCommand(), it’s a side effect of how PDO returns the result. For each row returned, you get an associative array in the following form:




array(

    'colName1'=>'colValue1',

    'colName2'=>'colValue2',

    ...

)

The column name doesn’t include the table name or alias, so only one column with the same name can be retrieved.

You either need to create a specific column alias for each ambiguous column, or you could try setting the first parameter of queryAll() to false, to return the rows indexed by a numeric ID.

The first option is the most robust.