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

Query and LEFT JOIN

Mythotical

Well-known member
#1
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.
 

Naatan

Well-known member
#2
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).
 

Mythotical

Well-known member
#3
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).
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.