Mysql Select problem

I have a little complex Database model on which i am not able to make a proper find

user(id, name…)

    1 Harsha
    
    2 Jasmine

modules (id, name )

    1 Users
    
    2 Restaurants
    
    3 Dishes

restaurant (id, name… )

    1 KFC 
    
    2 Pizza Hut

dishes (id, name, restaurant_id … )


    1 Cheese Pizza 2

items (id, module_id, item_id)

    1 1 1 (refers to User Harsha)
    
    2 2 1 (Refers to KFC)
    
    3 2 2 (Refers to Pizza Hut)
    
    4 1 2 (Refers to User Jasmine)
    
    5 3 1 (Refers to Cheese Pizza)

reviews (id, parent_id, message, item_id, commenters_id)

    1 0 "I love the ambience of Pizza Hut" 3 1 (Refers to Harsha reviewing Pizza Hut)
    
    2 1 "You remind me of that kid in the next table who freaked me out." 3 2 (Refers to Jasmine Replying to Harsha's review on Pizza Hut)
    
    3 0 "I love Cheese Pizza in Pizza Hut" 5 1 (Refers to the Cheese Pizza Review by Harsha)


i am trying to find the reviews done by Harsha of all the restaurants but am not able to limit them to the reviews of Restaurants alone. I am getting back the Dish Reviews also

Here is the code i have used

Cakephp Code (below Find the SQL Queries it created


		// Checks if the User is logged in if yes gathers the ID
		$id = $this->_loggedIN();

		// Find the ItemID from the Item Table
		$itemId = $this->User->Item->itemId('1', $id);

		// Finding the User Data and last Status Message
		$user = $this->User->Item->find('first', array('conditions' => array('Item.id' => $itemId), 'contain' => array('User', 'StatusMessage' => array('limit' => 1, 'order' => 'StatusMessage.created DESC'))));

		// Find the Restaurant Reviews of the Current User
		$reviews = $this->User->Item->Review->find('all', array('conditions' => array('Review.commenters_item_id' => $itemId, 'Review.pid = 0'), 'order' => array('Review.created DESC'), 
        'contain' => array(
            'Item' => array(
                'User' => array(
                    'fields' => array('id', 'first_name', 'last_name', 'username', 'file')), 
                'Restaurant' => array('fields' => array('id', 'name', 'slug', 'file', 'area_id', 'city_id'), 
                    'Area')), 
            'ReviewReply' => array(
                'Item' => array(
                    'User' => array(
                        'fields' => array('id', 'first_name', 'last_name', 'username', 'file')))))));

SQL

oh. my. $deity.

where do we begin?

you only have, like, two dozen queries listed there …

have you narrowed down which query is returning the wrong results?

i vaugely know whats happeneing… i provide the itemID (item table) of the user i want to search for all the reviews… it fetches them amazingly untill i find the results include restaurant and dishes review. only way to limit this would be… from the review record -> item-> item(record) -M make sure module_id = 2 and not 3.

hmvrulz, can you post a “SHOW CREATE TABLE” for each of the tables and post an example of the output your after?

this is the tables.
http://pastebin.com/RBXGfXk0

i want something like this

but

            [Item] => Array
                (
                    [id] => 3
                    [module_id] => 2
                    [item_id] => 2

in this the module_id should only be 2 and not anything else for the review.