title: How to do a conditional mysql left join based on table in the actual fields using query builder
body: <p>When setting up a product I have the option to limit its availability to a customer's country, region, and city. Based on this, I only show the product to relevant customers in the system who match this criterion.</p>
  
  <p>In some cases, I want to set a product to a country/region only and ignore city and I am wondering how best to write this query.</p>
  
  <p>The way I see it, I have two options. </p>
  
  <ol>
  <li>If a product is relevant to all cities and regions, simply include them all (a lot of data). </li>
  <li>If a city/region is not required, simply ignore the statements that limit them.</li>
  </ol>
  
  <p>PRODUCT:</p>
  
  <pre><code>+---------------------+------------------+------+-----+---------+----------------+
  | Field               | Type             | Null | Key | Default | Extra          |
  +---------------------+------------------+------+-----+---------+----------------+
  | id                  | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
  | uuid                | binary(16)       | NO   | UNI | NULL    |                |
  +---------------------+------------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>MANY TO MANY JOIN TO LOCATION</p>
  
  <pre><code>+------------------+------------------+------+-----+---------+-------+
  | Field            | Type             | Null | Key | Default | Extra |
  +------------------+------------------+------+-----+---------+-------+
  | product_id          | int(10) unsigned | NO   | PRI | NULL    |       |
  | meta_location_id | int(11)          | NO   | PRI | NULL    |       |
  +------------------+------------------+------+-----+---------+-------+
  </code></pre>
  
  <p>LOCATION</p>
  
  <pre><code>+-------------+---------------+------+-----+---------+----------------+
  | Field       | Type          | Null | Key | Default | Extra          |
  +-------------+---------------+------+-----+---------+----------------+
  | id          | int(11)       | NO   | PRI | NULL    | auto_increment |
  | iso         | varchar(50)   | YES  |     | NULL    |                |
  +-------------+---------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>QUERY BUILDER CODE</p>
  
  <pre><code>$qb = $this-&gt;repository-&gt;createQueryBuilder('product');
  $qb-&gt;orderBy('product.id', 'ASC');
  
  //If we are limiting by country
  if (isset($options['country']) and null !== $options['country']) {
      $qb-&gt;andWhere('product.country = :country')
          -&gt;setParameter('country', $options['country']);
  }
  </code></pre>
  
  <p>REGIONS/CITIES:</p>
  
  <p>The region option is the customer's current region, this is sent regardless and I have no control over it. Since I am searching in the product table, I need to check if the product region exists or not. If it does then I must limit it against the existing region otherwise ignore it.</p>
  
  <p>CURRENT QUERY:
  With this query, if region does not match, then it will not list the product. I would like to change this to a condition where it only applies IF data exists in the product table. </p>
  
  <pre><code>   if (isset($options['region']) and null !== $options['region']) {
          $qb-&gt;leftJoin('product.regions', 'region');
          -&gt;andWhere(($qb-&gt;expr()-&gt;in('region', [$options['region']])));
      }
  </code></pre>
  
  <p>I am guessing I need something along the lines of</p>
  
  <pre><code>    $qb-&gt;add('select', 'CASE WHEN region.id IS NOT NULL then do some query...
  </code></pre>
  
  <p>Any ideas would be appreciated!</p>