title: Cant figure out joins
body: <p>So I am making a Businesses web app with the <code>filters</code> feature. There are two filters that I have problem with: <strong>Order By</strong> and <strong>Attributes</strong>(Has following attributes) features. Which looks like this:</p>
  <p>Order By</p>
  <ul>
  <li>Highest Rated (<em>radio button</em>)</li>
  <li>Most reviews (<em>radio button</em>)</li>
  </ul>
  <p>Attributes</p>
  <ul>
  <li>Accepts Credit Cards (<em>checkbox</em>)</li>
  <li>Accepts Events (<em>checkbox</em>)</li>
  <li>Alcohol (<em>checkbox</em>)</li>
  <li>Delivery (<em>checkbox</em>)</li>
  <li>Smoking (<em>checkbox</em>)</li>
  </ul>
  <p>So when <strong>Order By</strong> option is clicked this function is executed. Where <code>$term</code> is value of <code>order_by</code> <code>get request</code> parameter.</p>
  <p><strong>BusinessFilter.php</strong></p>
  <pre><code>public function orderby($term)
      {
          if ($term == 'reviews_count') {
              return $this-&gt;builder
                  -&gt;leftJoin('reviews', 'businesses.id', '=', 'reviews.business_id')
                  -&gt;groupBy('businesses.id')
                  -&gt;selectRaw('businesses.*, COUNT(reviews.id) as reviews_count')
                  -&gt;orderByDesc('reviews_count');
          } else if ($term == 'rating') {
              return $this-&gt;builder
                  -&gt;leftJoin('reviews', 'businesses.id', '=', 'reviews.business_id')
                  -&gt;groupBy('businesses.id')
                  -&gt;selectRaw('businesses.*, AVG(reviews.rating) AS average')
                  -&gt;orderByDesc('average');
          } else {
              return $this-&gt;builder;
          }
  
      }
  </code></pre>
  <p>It works ok and the result is correct.</p>
  <p>Now when <strong>Attribute</strong> have some <code>check boxes</code> this function is executed where <code>$term</code> is an array with set of ids.</p>
  <p><strong>BusinessFilter.php</strong></p>
  <pre><code>    public function attributes($term)
      {
          $attributes= json_decode($term);
  
          if (count($attributes) == 0) {
              return $this-&gt;builder;
          }
  
          return $this-&gt;builder
              -&gt;select('businesses.*')
              -&gt;join('business_attribute_value', 'businesses.id', '=', 'business_attribute_value.business_id')
              -&gt;join('values', 'business_attribute_value.attribute_value_id', '=', 'values.id')
              -&gt;whereIn('values.id', $attributes)
              -&gt;groupBy('businesses.id')
              -&gt;havingRaw('COUNT(*) = ?', [count($attributes)]);
      }
  </code></pre>
  <p>the result is correct here too.</p>
  <p>Now the problem is when both filters have values it executes both queries together and It doesn't return the correct result. I assume it has something to do with joins. Am I doing something wrong? Please help. And if you need more info or code please let me know. Thank you, you are the best guys!</p>
  <p>This is how I execute filters</p>
  <pre><code>public function getSearch(BusinessFilter $filters)
      {
          $businesses = Business::filter($filters)-&gt;paginate(30);
  
          return $businesses;
      }
  </code></pre>
  <p>This is <code>QueryFilter</code> class. Basically what it does is that it goes through each request parameter and executes its function that was mentioned above.</p>
  <pre><code>class QueryFilters{
  
      protected $request;
      protected $builder;
  
      public function __construct( Request $request )
      {
          $this-&gt;request = $request;
      }
  
      public function apply(Builder $builder)
      {
          $this-&gt;builder = $builder;
  
          foreach( $this-&gt;filters() as $name =&gt; $value ){
              if( !method_exists($this, $name ) ){
                  continue;
              }
              if(strlen($value)){
                  $this-&gt;$name($value);
              } else {
                  $this-&gt;$name();
              }
          }
  
          return $this-&gt;builder;
      }
  
      public function filters()
      {
          return $this-&gt;request-&gt;all();
      }
  }
  </code></pre>