Excluding data from result

Very Very rusty here so feel free to roast me and kick me into the right direction!!

What my desired result is I am wanting to exclude data from my result if $find is not found within the Other OE Referances column.

Controller:

public function index()
    {
        $find = 'G060162A2';

        $letsFind = DB::table('parts')
                  ->when($find, function(Builder $query, string $find) {
                    $query->where('OE Number', $find)
                          ->orWhere('Other OE Referances', $find);
                  })
                    ->get();

        echo "<pre>";
            var_dump($letsFind);
        echo "<pre>";

        return view('parts');
    }
}

Database

object(stdClass)#584 (4) {
  ["ID"]=>
  int(23)
  ["internal ID"]=>
  string(6) "160980"
  ["OE Number"]=>
  string(9) "G060162A2"
  ["Other OE Referances"]=>
  string(37) "83222152426 ,83222289720, 83222305397"
}

LaryAI

1 year ago

Best Answer

Level 58

Certainly! The issue is that your current query will return rows where either the OE Number or the Other OE Referances column matches $find. But you want to only include rows where $find is found within the Other OE Referances column (and not just an exact match).

Since Other OE Referances is a string containing multiple comma-separated values, you need to use a LIKE query to check if $find exists as a whole value within that string.

Here's how you can adjust your query:

public function index()
{
    $find = 'G060162A2';

    $letsFind = DB::table('parts')
        ->when($find, function($query, $find) {
            // Remove the OE Number check if you only want to search in Other OE Referances
            $query->whereRaw("CONCAT(',', `Other OE Referances`, ',') LIKE ?", ['%,'.$find.',%']);
        })
        ->get();

    echo "<pre>";
        var_dump($letsFind);
    echo "<pre>";

    return view('parts');
}

Explanation:

CONCAT(',', \Other OE Referances`, ',')adds commas to the start and end, so you can safely search for,G060162A2,and avoid partial matches (e.g., not matchingG060162A21`).
The LIKE clause ensures $find is matched as a whole value within the comma-separated list.

If you want to include both OE Number and Other OE Referances, but only if $find is present in either:

public function index()
{
    $find = 'G060162A2';

    $letsFind = DB::table('parts')
        ->when($find, function($query, $find) {
            $query->where('OE Number', $find)
                  ->orWhereRaw("CONCAT(',', `Other OE Referances`, ',') LIKE ?", ['%,'.$find.',%']);
        })
        ->get();

    echo "<pre>";
        var_dump($letsFind);
    echo "<pre>";

    return view('parts');
}

Tip: For better database design, consider normalizing Other OE Referances into a related table, but the above will work for your current structure!