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

Yii 1.1 General Discussion
1

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.

2

Hi

Is there an error message or the result is incorrect?

Please give us more details

3

There is no error but the result is incorrect

4

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

5

How to post sql query to yii Criteria?

6

post the sql query here!

I will try to make it with Yii criteria :)

7

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

8

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