1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Query and LEFT JOIN

Discussion in 'XenForo Development Discussions' started by Mythotical, Jul 12, 2012.

  1. Mythotical

    Mythotical Well-Known Member

    I am attempting to LEFT JOIN 3 tables into the main but if I do it this way:
    PHP:
            $db XenForo_Application::get('db');
            
            return 
    $db->fetchAll('
                SELECT info.posts, info.likes, info.user_id, info.forum_id, user.user_id, user.username, node.title, node.node_id, levels.mye_post_amount, levels.mye_like_amount, levels.mye_title
                    FROM myexpert_levels AS levels
                    LEFT JOIN myexpert_info AS info ON (info.posts = levels.mye_post_amount) AND (info.likes = levels.mye_like_amount)
                    LEFT JOIN xf_user AS user ON (user.user_id = info.user_id)
                    LEFT JOIN xf_node AS node ON (node.node_id = info.forum_id)

                '
    );
    Then my expert levels work, display title and all but no user or node information is pulled, but if I do this:
    PHP:
            $db XenForo_Application::get('db');
            
            return 
    $db->fetchAll('
                SELECT info.posts, info.likes, info.user_id, info.forum_id, user.user_id, user.username, node.title, node.node_id, levels.mye_post_amount, levels.mye_like_amount, levels.mye_title
                    FROM myexpert_info AS info
                    LEFT JOIN xf_user AS user ON (user.user_id = info.user_id)
                    LEFT JOIN xf_node AS node ON (node.node_id = info.forum_id)
                    LEFT JOIN myexpert_levels AS levels ON (info.posts = levels.mye_post_amount) AND (info.likes = levels.mye_like_amount)
                '
    );
    I get the forum titles, user information but no level information.

    I am probably doing the join wrong so if someone with better experience with joins could take a look and provide a suggestion for a fix I would appreciate it.
     
  2. Naatan

    Naatan Well-Known Member

    First off, you are aware that LEFT JOIN will still "succeed" (with empty values) even if the join failed, right? You shouldn't use it unless you want this behaviour.

    That said, your levels join seems a bit weird as you are looking for an exact match on both those criteria's. Don't you mean to check for larger / smaller than results (ie. a range, not an exact match)?

    Regardless, it's a bit hard to say exactly what's wrong without knowing the table structure and it's intended use.

    By the way, one tip, I see you are only joining the node and user tables for their usernames and titles (you already have the id's), depending on the traffic you may be expecting on these tables you might be better off duplicating this information to your info table and removing those 2 joins, as storage is dirt cheap ($) and joins are expensive (processing).
     
  3. Mythotical

    Mythotical Well-Known Member

    Yep I am aware of that, thanks.

    The levels only has exact matches but you have a point seeing as how I could look for < or > which would accomplish the samething. I will actually move the username and avatar to the info table and the forum name and keep the node id in the info table. Didn't think to do that, will save time on my part and minimize the size of the query being ran, instead of 3 left joins then we would only have 1.
     
  4. Mythotical

    Mythotical Well-Known Member

    All works, considering this pulls from levels is there an if condition to only display current level earned for each user?
     
  5. Mythotical

    Mythotical Well-Known Member

    Nevermind think I figured out how to do it.
     

Share This Page