CODEWITHSUNDEEP
phplaraveleloquent
s_p
s_p

Reputation: 4693

Where clause parentheses in Laravel model

When I echo out my query using dd($query->toSql()); I get:

"select `item_num`, `item_name`, `primary_color`, `content` 
    where (`item_name` NOT LIKE ? and `item_name` NOT LIKE ? and `item_name` NOT LIKE ? ) 
and `primary_color` = ? or `primary_color` = ? or `primary_color` = ? 
order by `item_name` asc ◀"

What I need are parantheses around (primary_color= ? orprimary_color= ? orprimary_color = ?) so that the query looks like:

"select `item_num`, `item_name`, `primary_color`, `content` 
    where (`item_name` NOT LIKE ? and `item_name` NOT LIKE ? and `item_name` NOT LIKE ? ) 
and (`primary_color` = ? or `primary_color` = ? or `primary_color` = ? )
order by `item_name` asc ◀"

Here is my code:

$query = DB::table('my_table');
$query = $query->select(array('item_num', 'item_name', 'color', 'content'));
    
$query = $query->where($this->filterArr);

$cc = count($myArr);
$ii = 0;

foreach ($myArr as $key => $value) {
    $myArr = explode(" ", $value);
    if($ii==0)
      $query = $query->where( function(){ $column, '=', $myArr[0]);
    else
      $query = $query->orWhere($column, '=', $myArr[0]);

      if(count($myArr)>1){
        foreach($myArr as $filter){

          if ($key=='collection'){
            $filter = str_replace("-", " ", $filter);
          }

          $query = $query->orWhere($column, '=', $filter);
        }
      }
    }

  $ii++;
}

$query = $query->orderBy('item_name', 'asc')
              ->simplePaginate(50);

from this I get an error syntax error, unexpected ',' from this line $query = $query->where( function(){ $column, '=', $myArr[0]);

Can anyone lend a hand adding parentheses around WHERE statement please. Thank you.

edit $myArr is a set of filters

  Array (
    [color] => blue
    [color] => purple
    [material] => slick granite
  )

Upvotes: 0

Views: 203

Answers (1)

Alzafan Christian
Alzafan Christian

Reputation: 589

try this, btw your loop looks so messy XD

if this is what you want to achieve screenshot assumed :

$myArr = Array (
  "color" => "blue"
  "color" => "purple"
  "material" => "slick granite",
  "collection" => "slick-granite-blue-purple"
);

and if array has key name "collection" then replace array value ("-") to (" ")

$query = DB::table('your_table');
$query = $query->select(array('item_num', 'item_name', 'color', 'content'));
        
$query = $query->where(function($query){
  $query = $query->where('item_name', '!=', 'item1');
  $query = $query->where('item_name', '!=', 'item2');
  $query = $query->where('item_name', '!=', 'item3');
});//im using !=, replace with your need

$query = $query->where(function($filter) use($myArr){
  $ii=0;
  foreach ($myArr as $key => $value) {
    if($ii==0){
      if($key == 'collection')
        $value = str_replace('-', ' ', $value);
      $filter = $filter->where($key, '=', $value);
    }else{
      if($key == 'collection')
        $value = str_replace('-', ' ', $value);
      $filter = $filter->orWhere($key, '=', $value);
    }
    $ii++;
  };
});

Upvotes: 1

Related Questions