Two joins in query builder

Yii 1.1 General Discussion
1

Hi All!

I have three tables :

Categories[has its own id field]

Sub_categories[has its own id and main_category_id fields]

Items[has its own id and sub_category_id fields]

I need all items on main_category id =1,so I have two joins i.e(items INNER JOIN sub_cat)INNER JOIN(Categories)…

This SQL query works okay for me :

SELECT * FROM items i INNER JOIN sub_categories sc ON sc.id = i.sub_category_id INNER JOIN categories c ON c.id = sc.main_category_id WHERE sc.main_category_id = 1…

now I want to do it with query builder:

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

->select()

->from(‘items i’)

->join(‘sub_categories sc’, ‘sc.id = i.sub_category_id’)

How can I implement the 2nd join here?

I’d really appreciate any help!

2

Hi Amm.

I am not sure if this will help You but:

[list=1]

[*]Your setup could be better handled via relation selections what are your relations atm? for example if you have a relation:




#

# this is invalid yii code

#

#in Item->relations():

'sub_cat' => ( BELONGS_TO sub_category );

'cat' => ( BELONGS_TO category ); 

#you would just need

$items = Item::model()->with('sub_cat')->findAll(array('where') => cat_id = $passed_id);

to get the same results.

this way you would get an array of Item obj where item.sub_cat will be an instance of the sub_category.

[*]why have two tables for categories? I usually create one categoory table that has a self referencing fk named parent. this way anytime you create a category you can assing an allready existing subcategory to it. This way you can have infinite depth sub categories and your queries get much more consice. If you need more info let me know and I will dig up some sample code.

[/list]

3

Hi Kiriakos!

Thanks a lot for the reply, i’m sorry for such a late reply from my side…since the SQL queries were working, I started working on other functions and almost forgot this…

My relations are as follows:

IN SUB_CATEGORIES:




                    'category' => array(self::BELONGS_TO, 'Category', 'main_category_id')

IN ITEMS:




                    'sub_category'=>array(self::BELONGS_TO,'Sub_category','sub_category_id')

In a different post, my question has been answered…with query builder :




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

->select()

->from('items i')

->join('sub_categories sc', 'sc.id = i.sub_category_id')

->join('categories c','c.id = sc.main_category_id');

And it works!..

As for the 2nd part of your post…Your logic is great, It never came to my mind! It should save a lot of effort… I’ll definitely keep that in mind for future!! Thanks!

4

Hi every one…I have applied this query to retrieve data from three tables.

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

		->select()


		->from('video_vocab vv')


		->join('vocabwords vw','vv.wordid = vw.id')


		->join('vocabmeanings vm','vm.id=vw.id')


		->where('vv.contentid =: contentid',array(':contentid'=>$contentid))


		->where('vw.sourcelanguage =:sourcelanguage',array(':sourcelanguage'=>$srcId));;

and the query is fine. But it suppose to return some values but its not.How to get the columns values by this query. ?

Regards

Shaik