Join And Querybuider

Yii 1.1 General Discussion
1

I am trying to fetch information from the related table after putting condition in the query, but it doesn’t work. It works correctly if i remove the join statement and don’t ask for data of column in related table




if(isset($_POST['ProductIndex']))

  {

        $searchresult = Yii::app()->db->createCommand()

                ->select('p.product_id, p.product_name, r.price, p.master_key') //works fine without r.price

                ->from('tbl_product_index p') 

                ->join('tbl_pricing r', 'r.product_id=p.product_id') //works fine without this sentence

                ->where('master_key=:master_key', array(':master_key'=>$_POST['ProductIndex']['brand']['1']))

                ->queryAll();

        

        $dataProvider=new CArrayDataProvider($searchresult);
2

Have you tried embedding your SQL directly in createCommand()? Something like this:


Yii::app()->db->createCommand("SELECT p.product_id, p.product_name, r.price, p.master_key FROM tbl_product_index p LEFT JOIN tbl_pricing r ON r.product_id=p.product_id WHERE ---INSERT CLAUSES HERE----")->queryAll();

The above works great for me for any degree of complex queries.

B

3

Hi spritespirit,

According to the reference, join should be written like the following:




                ->join('join tbl_pricing r on r.product_id=p.product_id')

http://www.yiiframework.com/doc/api/1.1/CDbCommand#setJoin-detail

4

Thanks both of you … I had left my related table empty that’s why no result was coming… sorry for posting stupid issue :(