CDbCriteria - Query from two tables

Yii 1.1 General Discussion
1

Hello All,

I am a noob in Yii framework. I want to query two tables using CDbCriteria, for example for the following SQL query:


$query = "SELECT business_posts.*,

 		business_photos.image_source_path as postscraped_photo 

		from business_posts 

 		LEFT JOIN business_photos ON business_posts.post_id=business_photos.post_id 

WHERE business_posts.business_name = '".$business_name."' AND  (business_posts.message!='' OR business_posts.description !='' OR business_posts.post_id !='') AND (business_posts.message  != '') AND (business_posts.is_visible  != '1') ORDER BY `created_time` DESC limit 20";

			

$postModel = Yii::app()->db->createCommand($query)->queryAll();

Right now, this query works but I know this isn’t the best way to run a query. How do, I write this query using CDbCriteria and improve the execution time?

I would appreciate any help and any detailed tutorial on using CDbCriteria.

Thanks.

2

Please check this it may be useful to u,

     $criteria = new CDbCriteria;


     $criteria->select = '*';


     $criteria->join = 'LEFT JOIN business_photos ON business_photos.post_id = post_id';


     $criteria->addCondition('business_name = '.$business_name);


     $criteria->addCondition('message!= "" OR description != "" OR post_id != "");


     $criteria->addCondition('message  != ""');


     $criteria->addCondition('is_visible  != "1"');


     $criteria->order = 'created_time DESC';


     $criteria->limit = 20;


     $result = BUSINESS_POSTS::model()->findAll();
3

I think you are on the wrong way. Before you write your own SQL statements, you should generate models. Model is class in PHP which defines your database table columns and relations from a database, and much more (rules, labels etc…).

You can create models using Gii module, and there are a lot of tutorials explaining this process.

What is benefit when using modules?

In your case, table business_post is connected with table business_photos. I assume that bussines_post has multiple business_photos. Using Gii, you can generate model for table business_post and business_photo, and after that you can do something like this:




$post = new BusinessPost::model()->findByPk($id);//Finds your business_post by primary key

$photos=$post->businessPhotos;//Gets all business_photos for current post (let's assume that connection between business_post and business_photo tables is called businessPhotos)

If you have already generated modules, please see the post from the @Dineshkumar.

4

Great answer. Little detail though, you miss $criteria parameter in last line :)


$result = BUSINESS_POSTS::model()->findAll($criteria);