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

Left Join Union

Discussion in 'General PHP and MySQL Discussions' started by Snog, Jun 10, 2015.

  1. Snog

    Snog Well-Known Member

    I have a need for a left join query that includes a union. I've tried every iteration I can think of but none of them seem to work. This is in an extension for the thread model (fetchOptions['jointables'])

    This is the closest thing I've come to a working query is where the error thrown is saying the first instance of thread.node_id doesn't exist...
    PHP:
    'LEFT JOIN (
         (SELECT mt1.field1,
           mt1.field2
           FROM xf_mytable AS mt1
           WHERE mt1.node_id = thread.node_id)
       UNION
         (SELECT mt2.field1,
           mt2.field2
           FROM xf_mytable AS mt2
           WHERE mt2.field5 = other_result.field5)
       ) AS aggregate_result
       ON (aggregate_result.node_id = thread.node_id)'
    ;
    Any help would be greatly appreciated.

    EDIT:

    The reason for the union is this left join is causing a monstrous load on MySql on a big board. I'm hoping a union will cure the load problem.
    PHP:
      'LEFT JOIN xf_mytable AS mt1 ON
       (mt1.node_id = thread.node_id OR mt1.field5 = other_result.field5)'
     
    Last edited: Jun 10, 2015
  2. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Subqueries as joins can't access outside members (thread.node_id) so that column will not be found on the inside. Also, your subquery isn't returning node_id so aggregate_result.node_id will be undefined in the ON clause on the outside.

    Code:
    'LEFT JOIN (
         (SELECT mt1.field1,
           mt1.field2
           FROM xf_mytable AS mt1
           WHERE mt1.node_id = thread.node_id)
       UNION
         (SELECT mt2.field1,
           mt2.field2
           FROM xf_mytable AS mt2
           WHERE mt2.field5 = other_result.field5)
       ) AS aggregate_result
       ON (aggregate_result.node_id = thread.node_id)';
    
    I don't want to suggest a fix until I have more information about the problem and the data. What are you trying to accomplish with the query? Which tables are involved and how big are they?

    Inefficient joins might respond well to additional indexes on the tables in question.
     
  3. Snog

    Snog Well-Known Member

    It's while reading the thread table and joining my custom table.

    The thread table has over 5 million threads and my custom table has over 2000 entries keyed on a previous join response (other_result.field5) that is indexed in my custom table (mt1.field5).

    If I remove the 'OR' from this join, mysql load drops to normal..
    Code:
    'LEFT JOIN xf_mytable AS mt1 ON
    (mt1.node_id = thread.node_id OR mt1.field5 = other_result.field5)'
    The reason I was thinking UNION would work is because the result should combined into one response.

    I believe I've solved the problem by using IF(other_result.field5,result1,result2) AS result and two LEFT JOINS. The timing on my test server dropped to near normal by doing that and I'm waiting for feedback from a test site to see if it works in a live environment.
     

Share This Page