Join And Querybuider

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



        $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']))



        $dataProvider=new CArrayDataProvider($searchresult);

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.


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')

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