title: Yii2 - left join on multiple condition
body: <p>I have three tables with the following relations,</p>
  
  <pre><code>  ------- 1        0..* ------------
   |Product|-------------|Availability|
    -------               ------------
      1 |
        |
      1 |
    --------
   |MetaData|
    --------
  </code></pre>
  
  <p>my raw sql looks like this</p>
  
  <pre><code>SELECT p.ID FROM product p 
  LEFT JOIN availability a ON a.productID=p.ID 
            AND a.start&gt;=DATE_ADD(DATE(now()), INTERVAL 7 DAY)
  LEFT JOIN meta_data m ON m.ID=p.meta_dataID
  WHERE a.ID IS NULL
  AND m.published_state=1;
  </code></pre>
  
  <p>That is, find each <code>Product</code> with a <code>MetaData.published_state</code> equal to <code>1</code> and with no <code>Availability</code> such that <code>Availability.start</code> more than 7 days from <code>now()</code>. </p>
  
  <p>I'm trying to accomplish the same using <code>ActiveRecord</code> methods, using something like the following,</p>
  
  <pre><code>$products = Product::find()
              -&gt;joinWith('metaData')
              -&gt;joinWith('availability')
              -&gt;onCondition(['&gt;=', 'availability.start', strtotime('+7 days')])
              -&gt;where(['is', 'availability.ID', NULL])
              -&gt;andWhere(['=', 'meta_data.published_state', 1])
              -&gt;all();
  </code></pre>
  
  <p>however, this is returning no results. Using <code>Connection::createCommand()</code> to run the raw sql returns the rows I'd expect so there is no issue with the data.</p>
  
  <p>I suspect the issue is being caused by the <code>join</code> conditions and the <code>where</code> conditions 'bleeding' into each other; both join and where being applied to either the joining or the where rather than separately. </p>
  
  <p>How can I output the actual sql query being run? this is in an action being called from a console controller.</p>
  
  <p>How can I alter my code to return the desired <code>Products</code>?</p>