How To Find Data From Joined Tables

Yii 1.1 General Discussion
1

There are three tables, Colors, Products and ProductStyles. The logical relation is Products HAS_MANY ProductStyles and Products also HAS_MANY Colors. I need to find the image path which stores in ProductStyles base on the Inventory which stories is Colors.

the SQL statement should be

SELECT DISTINCT     p.ProductID, p.FilePath 


FROM            Colors as c, Product_Styles as p 


WHERE            c.TryAtHome_Inventory > 0


AND            c.ProductID = p.ProductID

However, If I use CDbCriteria or other method instead of complete SQL statement, how I can do that? Also, the findBySql returns base on the select attribute in the statement or base on a model? What it will return if I use model()->findBySql(‘upper SQL statement’)?

2

In your case, Colors HAS_AND_BELONGS_TO_MANY ProductStyles via pivot table Products.

Docs.

3

this example:




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

									->select( 

											   'nid, title,date,n.uid, 

											   body_value as Body

											   ')

									->from('node n')

									->leftjoin('field_data_body b', 'b.entity_id=n.nid')

									->queryAll();