A Query To Retrieve Data From One Table By Joining Multiple Tables

I have 4 tables: Dishes(dish_id,…),Spot_branch_menu(branch_id,menu_id,…), Menu_category(menu_id,category_id,…),category_dishes(category_id,dish_id,…).

Now i want to get unique dishes belonging to particular branch id.

I used the following code but im not getting the correct result.


$id=812052509;

		$criteria = new CDbCriteria;

		$criteria->select = 't.*';

		$criteria->distinct=true;

		$criteria->join ='JOIN spot_branch_menu sbm ON sbm.branch_id = :value JOIN menu_category mc ON mc.menu_id=sbm.menu_id JOIN category_dishes cd ON cd.category_id=mc.category_id' ;

		$criteria->condition = 't.dish_id=cd.dish_id';

		$criteria->params = array(":value" => $id);

		$allDishes=Dishes::model()->findAll($criteria);

		$total=sizeOf($allDishes);

		$pages = new CPagination($total);

		$pages->pageSize = 4;

		$pages->applyLimit($criteria);



Please help me by providing correct query.

Hi

Is there an error message or the result is incorrect?

Please give us more details

There is no error but the result is incorrect

Ok, run a sql query directly in your mysql and confirm that the result is the correct

post this sql query to compare with Yii criteria

How to post sql query to yii Criteria?

post the sql query here!

I will try to make it with Yii criteria :)

I dont know the query. That is where i need help.

check this sql if returns the desired records (in phpmyadmin)

select distinct t.id, * from table_Dishes t JOIN spot_branch_menu sbm ON sbm.branch_id = 1 JOIN menu_category mc ON mc.menu_id=sbm.menu_id JOIN category_dishes cd ON cd.category_id=mc.category_id

Also tell us what exactly you want from query to do