title: Laravel Eloquent and Mysql join a table IF another join is null
body: <p><strong>Three main tables:</strong></p>
  
  <p>products</p>
  
  <p>advertisers</p>
  
  <p>locations</p>
  
  <p><strong>Two pivot tables:</strong></p>
  
  <p>advertisers_locations</p>
  
  <p>products_locations</p>
  
  <p><strong>Relationships:</strong></p>
  
  <p>A product belongs to an advertiser and an advertiser has many locations (Locations it can ship products to)</p>
  
  <p>A product can also have it own set of locations that override the advertiser locations (Some products have delivery restrictions)</p>
  
  <p><strong>What I need to do is:</strong></p>
  
  <ol>
  <li><p>Select all products</p></li>
  <li><p>Check if products_locations table for product ID and join it.</p></li>
  <li><p>If it does not exist then join the advertisers locations table</p></li>
  </ol>
  
  <p>Is this possible to do in one query and using eloquent? Here's my code - struggling with the conditional:</p>
  
  <pre><code>public function scopeWhereShippableToLocation($query)
      {
          $location_id = session('location_id');
  
          $query-&gt;where(function ($q) use ($location_id) {
              $q-&gt;join('products_locations', 'products_locations.product_id', '=', 'products.id')
                  -&gt;where('products_locations.location_id', '=', $location_id);
          });
  
          $query-&gt;orWhere(function ($q) use ($location_id) {
              $q-&gt;join('advertisers_locations', 'advertisers_locations.advertiser_id', '=', 'products.advertiser_id')
                  -&gt;where('advertisers_locations.location_id', '=', $location_id);
          });
  
  
          //dd($q-&gt;toSql());
  
          return $query;
      }
  </code></pre>
  
  <p>This is currently producing a MySQL error:</p>
  
  <pre><code>    Column not found: 1054 Unknown column 'products_locations.location_id' in 'where clause' (SQL: select `products`.*, 
  </code></pre>