title: SQLSTATE[42S22]: Error using when clause in Laravel 7 advance filter with raw sql
body: <p><strong>UPDATED</strong>
  I am making a search filter and using raw sql in query builder, so there are min and max mileage and prices for the filters of cars in database, with assumed columns...
  Actually I am calculating the prices and mileage in standard units which is Euro and kilometers in raw sql for application of operators on the output...
  for example if user store 110 usd then it will be converted to 100 euro at first in raw sql then we will determine the results greater or lesser thans...
  The method works for one condition but when I activate others it gives error that the assumed column is not found...</p>
  <pre><code>//query for min price:
      $product1 = DB::table('sellers') 
  -&gt;leftJoin('users','sellers.user_id','=','users.id')
  -&gt;leftJoin('prices_privates','sellers.id','=','prices_privates.user_id')
  -&gt;when($request-&gt;get('price_min'), function($query) use ($request) {
                          $price = $request-&gt;get('price_min');
                          $usd = $this-&gt;currency(1 , 'USD');
                          $gbp = $this-&gt;currency(1 , 'GBP');
                          $cad = $this-&gt;currency(1 , 'CAD');
                          $aud = $this-&gt;currency(1 , 'AUD');
                          $mxn = $this-&gt;currency(1 , 'MXN');
                          $brl = $this-&gt;currency(1 , 'BRL');
                          $query-&gt;select(&quot;*&quot;,
                          \DB::raw('(CASE 
                         WHEN currency = &quot;USD&quot; THEN FORMAT((price * '.$usd.'),2)
                         WHEN currency = &quot;GBP&quot; THEN FORMAT((price * '.$gbp.'),2)
                         WHEN currency = &quot;CAD&quot; THEN FORMAT((price * '.$cad.'),2)
                         WHEN currency = &quot;AUD&quot; THEN FORMAT((price * '.$aud.'),2)
                         WHEN currency = &quot;MXN&quot; THEN FORMAT((price * '.$mxn.'),2)
                         WHEN currency = &quot;BRL&quot; THEN FORMAT((price * '.$brl.'),2)
                         WHEN currency = &quot;EUR&quot; THEN FORMAT((price * 1),2)
                         END) AS price_conv'))
                    -&gt;having(&quot;price_conv&quot; ,'&gt;=', $price);
                  })   
      //query for min mileage
   -&gt;when($request-&gt;get('mileage_from'), function($query) use ($request) {
                          $mileage_from = $request-&gt;mileage_from;
                          $query-&gt;select(&quot;*&quot;,
                          \DB::raw('(CASE 
                              WHEN mileage_in = &quot;miles&quot; THEN mileage * 1.60934 
                              WHEN mileage_in = &quot;km&quot; THEN mileage + 0 
                              END) AS kilometers'))
                         -&gt;having(&quot;kilometers&quot; ,'&gt;=', $mileage_from);
                      })
  
   //Radius Search
                  
                  -&gt;when($request-&gt;input('distance_km')&amp;&amp; $request-&gt;input('loc_latitude') &amp;&amp; $request-&gt;input('loc_longitude') , function ($query) use ($request){
  
                      $lat = $request-&gt;get('loc_latitude');
                      $long = $request-&gt;get('loc_longitude');
                      $radius = $request-&gt;get('distance_km');
                      $query-&gt;select(&quot;*&quot;,
                           \DB::raw('( 6371 * acos( cos( radians(?) ) *
                            cos( radians( location_privates.latitude ) )
                            * cos( radians( location_privates.longitude ) - radians(?)
                            ) + sin( radians(?) ) *
                            sin( radians( location_privates.latitude ) ) )
                           ) AS distance', [$lat, $long, $lat]))
                          -&gt;having(&quot;distance&quot;, '&lt;=', $radius);
                          // -&gt;orderBy(&quot;distance&quot;,'asc');
  
                  })
  
      -&gt;get(['sellers.*',
                      'users.user_type',
                      'prices_privates.price',
                      'prices_privates.currency',
                      'location_privates.address',
                      'location_privates.latitude',
                      'location_privates.longitude'
                   ]);
  </code></pre>
  <p><a href="https://i.stack.imgur.com/wWRa2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/wWRa2.png" alt="enter image description here" /></a></p>
  <p>if I comment one function the other works..
  they do not work at the same time..
  this problem is only with queries which includes raw sql.</p>
  <p>Here the whole function :</p>
  <pre><code>    $product1 = DB::table('sellers') 
                          -&gt;leftJoin('users','sellers.user_id','=','users.id')
                          -&gt;leftJoin('location_privates','sellers.id','=','location_privates.user_id')
                          -&gt;leftJoin('prices_privates','sellers.id','=','prices_privates.user_id')
                   
                                          
                      -&gt;when($request-&gt;get('condition') == 'classic', function($query) use ($request) {
                          $year = Carbon::now()-&gt;format('Y');
                          $used = ($year - '20' );
                          $query-&gt;where('manufact_year', '&lt;=', $used);
                      })  
                      -&gt;when($request-&gt;get('condition') == 'used', function($query) use ($request) {
                          $year = Carbon::now()-&gt;format('Y');
                          $used = ($year - '20' );
                          $query-&gt;where('manufact_year', '&gt;=', $used);
                      }) 
                      -&gt;when($request-&gt;get('condition') == 'user', function($query) use ($request) {
                          $query-&gt;where('user_type', '=', 'user');
                      })
                      -&gt;when($request-&gt;get('condition') == 'company', function($query) use ($request) {
                          $query-&gt;where('user_type', '=', 'company');
                      })
  
                      -&gt;when($request-&gt;get('search_make'), function($query) use ($request) {
                          $make = $request-&gt;get('search_make');
                          $query-&gt;where('make', 'like', &quot;%$make%&quot;);
                      })
                      
                      -&gt;when($request-&gt;get('style'), function($query) use ($request) {
                          $style = $request-&gt;get('style');
                          $query-&gt;where('car_body', 'like', &quot;%$style%&quot;);
                      })
                      -&gt;when($request-&gt;get('model'), function($query) use ($request) {
                          $model = $request-&gt;get('model');
                          $query-&gt;where('model', 'like', &quot;%$model%&quot;);
                      })
      
                      
                      -&gt;when($request-&gt;get('color'), function($query) use ($request) {
                          $color = $request-&gt;get('color'); 
                          $query-&gt;where('color', 'like', &quot;%$color%&quot;);
                      })
                      -&gt;when($request-&gt;get('country'), function($query) use ($request) {
                          $country = $request-&gt;get('country');
                          $query-&gt;where('address', 'like', &quot;%$country%&quot;);
                      })
                      
                      -&gt;when($request-&gt;get('year_min'), function($query) use ($request) {
                          $query-&gt;where('manufact_year', '&gt;=', $request-&gt;get('year_min'));
                      })
                      
                      
                      -&gt;when($request-&gt;get('year_max'), function($query) use ($request) {
                          $query-&gt;where('manufact_year', '&lt;=', $request-&gt;get('year_max'));
                      })
      
                    
                  -&gt;when($request-&gt;get('price_min') &amp;&amp; $request-&gt;get('price_max'), function($query) use ($request) {
                      $price = $request-&gt;get('price_min');
                      $price_2 = $request-&gt;get('price_max');
                      $usd = $this-&gt;currency(1 , 'USD');
                      $gbp = $this-&gt;currency(1 , 'GBP');
                      $cad = $this-&gt;currency(1 , 'CAD');
                      $aud = $this-&gt;currency(1 , 'AUD');
                      $mxn = $this-&gt;currency(1 , 'MXN');
                      $brl = $this-&gt;currency(1 , 'BRL');
  
                      $query-&gt;select(&quot;*&quot;,
  
                      \DB::raw('(CASE 
                              
  
                          WHEN currency = &quot;USD&quot; THEN FORMAT((price * '.$usd.'),2)
                          WHEN currency = &quot;GBP&quot; THEN FORMAT((price * '.$gbp.'),2)
                          WHEN currency = &quot;CAD&quot; THEN FORMAT((price * '.$cad.'),2)
                          WHEN currency = &quot;AUD&quot; THEN FORMAT((price * '.$aud.'),2)
                          WHEN currency = &quot;MXN&quot; THEN FORMAT((price * '.$mxn.'),2)
                          WHEN currency = &quot;BRL&quot; THEN FORMAT((price * '.$brl.'),2)
                          WHEN currency = &quot;EUR&quot; THEN FORMAT((price * 1),2)
                          END) AS price_conv')
                          )
                          -&gt;having('price_conv between $price and $price_2');
                          // -&gt;having(&quot;price_conv&quot; ,'&lt;=', $price_2);
                       
  
                  })
                  
                  // -&gt;when($request-&gt;get('price_max'), function($query) use ($request) {
                  //     $price_2 = $request-&gt;get('price_max');
                  //     $usd = $this-&gt;currency(1 , 'USD');
                  //     $gbp = $this-&gt;currency(1 , 'GBP');
                  //     $cad = $this-&gt;currency(1 , 'CAD');
                  //     $aud = $this-&gt;currency(1 , 'AUD');
                  //     $mxn = $this-&gt;currency(1 , 'MXN');
                  //     $brl = $this-&gt;currency(1 , 'BRL');
  
                  //     $query-&gt;select(&quot;*&quot;,
  
                  //     \DB::raw('(CASE 
                              
  
                  //         WHEN currency = &quot;USD&quot; THEN FORMAT((price * '.$usd.'),2)
                  //         WHEN currency = &quot;GBP&quot; THEN FORMAT((price * '.$gbp.'),2)
                  //         WHEN currency = &quot;CAD&quot; THEN FORMAT((price * '.$cad.'),2)
                  //         WHEN currency = &quot;AUD&quot; THEN FORMAT((price * '.$aud.'),2)
                  //         WHEN currency = &quot;MXN&quot; THEN FORMAT((price * '.$mxn.'),2)
                  //         WHEN currency = &quot;BRL&quot; THEN FORMAT((price * '.$brl.'),2)
                  //         WHEN currency = &quot;EUR&quot; THEN FORMAT((price * 1),2)
                  //         END) AS price_conv')
                  //         )
                  //         -&gt;having('price_conv' ,'&lt;=', $price_2);
                       
  
                  // })
  
                          // WHEN currency = &quot;miles&quot; THEN 0
  
                  // -&gt;when($request-&gt;get('mileage_from'), function($query) use ($request) {
                  //     $mileage_from = $request-&gt;mileage_from;
  
                  //     $query-&gt;select(&quot;*&quot;,
  
                  //     \DB::raw('(CASE 
  
                  //         WHEN mileage_in = &quot;miles&quot; THEN mileage * 1.60934 
  
                  //         WHEN mileage_in = &quot;km&quot; THEN mileage + 0 
  
                  //         END) AS kilometers'))
                          
                  //         -&gt;having(&quot;kilometers&quot; ,'&gt;=', $mileage_from);
                  // })
                  
                  // -&gt;when($request-&gt;get('mileage_to') &amp;&amp; $request-&gt;get('mileage_to') != '200000', function($query) use ($request) {
                  //     $mileage_to = $request-&gt;get('mileage_to');
                  //                     $query-&gt;select(&quot;*&quot;,
  
                  //     \DB::raw('(CASE 
  
                  //         WHEN mileage_in = &quot;miles&quot; THEN mileage * 1.60934 
  
                  //         WHEN mileage_in = &quot;km&quot; THEN mileage + 0 
  
                  //         END) AS kilometers'))
  
                  //     -&gt;having(&quot;kilometers&quot;, '&lt;=', $mileage_to);
                  // })
     
     //Radius Search
                  
                  // -&gt;when($request-&gt;input('distance_km')&amp;&amp; $request-&gt;input('loc_latitude') &amp;&amp; $request-&gt;input('loc_longitude') , function ($query) use ($request){
  
                  //     $lat = $request-&gt;get('loc_latitude');
                  //     $long = $request-&gt;get('loc_longitude');
                  //     $radius = $request-&gt;get('distance_km');
                  //     $query-&gt;select(&quot;*&quot;,
                  //          \DB::raw('( 6371 * acos( cos( radians(?) ) *
                  //           cos( radians( location_privates.latitude ) )
                  //           * cos( radians( location_privates.longitude ) - radians(?)
                  //           ) + sin( radians(?) ) *
                  //           sin( radians( location_privates.latitude ) ) )
                  //          ) AS distance', [$lat, $long, $lat]))
                  //         -&gt;having(&quot;distance&quot;, '&lt;=', $radius);
                  //         // -&gt;orderBy(&quot;distance&quot;,'asc');
  
                  // })
  
  //End Radius Search
  
                 
                  -&gt;get(['sellers.*',
                  'users.user_type',
                  'prices_privates.price',
                  'prices_privates.currency',
                  'location_privates.address',
                  'location_privates.latitude',
                  'location_privates.longitude'
                  ]);
  </code></pre>
  <p>That part:</p>
  <pre><code> '*, ( 6371 * acos( cos( radians(?) ) * ' at line 11 (SQL: select *, (CASE WHEN currency = &quot;USD&quot; THEN FORMAT((price * 0.8149295086),2) WHEN currency = &quot;GBP&quot; THEN FORMAT((price * 1.112309934),2) WHEN currency = &quot;CAD&quot; THEN FORMAT((price * 0.6396724877),2) WHEN currency = &quot;AUD&quot; THEN FORMAT((price * 0.629089079),2) WHEN currency = &quot;MXN&quot; THEN FORMAT((price * 0.0409567497),2) WHEN currency = &quot;BRL&quot; THEN FORMAT((price * 0.1568996627),2) WHEN currency = &quot;EUR&quot; THEN FORMAT((price * 1),2) END) AS price_conv, *, ( 6371 * acos( cos( radians(13.7563309) ) * cos( radians( location_privates.latitude ) ) * cos( radians( location_privates.longitude ) - radians(100.5017651) ) + sin( radians(13.7563309) ) * sin( radians( location_privates.latitude ) ) ) ) AS distance from `sellers` left join `users` on `sellers`.`user_id` = `users`.`id` left join `location_privates` on `sellers`.`id` = `location_privates`.`user_id` left join `prices_privates` on `sellers`.`id` = `prices_privates`.`user_id` having `price_conv` &gt;= 100 and `distance` &gt;= 500)
  </code></pre>