SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Sep 2008
    Location
    HidePoint
    Posts
    195
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Mysql Select problem

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

    user(id, name...)

    PHP Code:
        1 Harsha
        
        2 Jasmine 
    modules (id, name )

    PHP Code:
        1 Users
        
        2 Restaurants
        
        3 Dishes 
    restaurant (id, name.... )

    PHP Code:
        1 KFC 
        
        2 Pizza Hut 
    dishes (id, name, restaurant_id .. )
    PHP Code:
        1 Cheese Pizza 2 
    items (id, module_id, item_id)

    PHP Code:
        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)

    PHP Code:
        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
    PHP Code:
            // 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
    http://pastebin.com/3xzUxvbk

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by hmvrulz View Post
    Cakephp Code (below Find the SQL Queries it created
    SQL
    http://pastebin.com/3xzUxvbk
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Sep 2008
    Location
    HidePoint
    Posts
    195
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,032
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    hmvrulz, can you post a "SHOW CREATE TABLE" for each of the tables and post an example of the output your after?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  5. #5
    SitePoint Zealot
    Join Date
    Sep 2008
    Location
    HidePoint
    Posts
    195
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    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
    http://pastebin.com/kdYzGz8D


    but

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

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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •