title: CakePHP 3 left join and union in same query
body: <p>I have a products table and a metadata table that I want to allow searching of. Having managed to write the query so it works super fast, I'm having trouble migrating it to CakePHP 3.x </p>
  
  <p>The tables are standard parent->child setup with foreign keys and fulltext indexes on the relevant data fields.</p>
  
  <p>The query I want to emulate is:</p>
  
  <pre><code>select products.*, sum(hits.relevance) as relevance from (
      SELECT  products.id, MATCH(products.code, products.title) AGAINST('"mm mmm"' IN BOOLEAN MODE) as relevance
      FROM    products
      WHERE   MATCH(products.code, products.title) AGAINST('"mm mmm"' IN BOOLEAN MODE)
  union all
      SELECT  pim1.product_id as id, MATCH(pim1.value) AGAINST('"mm mmm"' IN BOOLEAN MODE) as relevance
      FROM    pim1 
      WHERE   MATCH(pim1.value) AGAINST('"mm mmm"' IN BOOLEAN MODE)
  ) as hits
  left join products on products.id = hits.id
  group by products.id
  order by relevance desc
  </code></pre>
  
  <p>Essentially this is allowing MySQL to use the indexes much faster than a left join does, unions the results, then uses that as the primary table to left join the products data to to hand off to paginate() and the view.</p>
  
  <p>I have the unions all sorted, but I can't seem to get the outer query to work.</p>
  
  <pre><code>$pim = $this-&gt;Products  
         -&gt;association("Pim1")    
         -&gt;find('all')    
         -&gt;select(['fk' =&gt; 'product_id']) 
         -&gt;select(['relevance' =&gt; 'MATCH(value) AGAINST(:search IN BOOLEAN MODE)'])   
         -&gt;where("MATCH(value) AGAINST(:search IN BOOLEAN MODE)") 
         -&gt;bind(":search", $this-&gt;request-&gt;session()-&gt;read('search.wild_terms'));
  $prd = $this-&gt;Products
         -&gt;find('all')    
         -&gt;select(['fk' =&gt; 'id']) 
         -&gt;select(['relevance' =&gt; 'MATCH(code, title) AGAINST(:search IN BOOLEAN MODE)']) 
         -&gt;where("MATCH(code, title) AGAINST(:search IN BOOLEAN MODE)")   
         -&gt;bind(":search", $this-&gt;request-&gt;session()-&gt;read('search.wild_terms'));
  </code></pre>
  
  <p>This bit works and runs the union as per the subquery above</p>
  
  <pre><code>$query = $prd-&gt;unionAll($pim);
  </code></pre>
  
  <p>This bit doesn't then allow me to attach the products data to the results of that union</p>
  
  <pre><code>$query-&gt;leftJoinWith("Products", function ($q) { return $q-&gt;where(['Products.id' =&gt; 'fk']); });
  </code></pre>
  
  <p>It throws an error</p>
  
  <blockquote>
    <p>Products is not associated with Products</p>
  </blockquote>
  
  <p>Any guidance on how to convert my successful SQL into Cake would be greatly appreciated.</p>