How to prioritize eloquent query to create granular searches?

Hi all,

I am a bit stuck on this one.....

Imagine a form with 3 dropdown buttons. color(black,white), size(big,medium,small) and material(plastic,metal,wood).

All choices are from related tables. All choices are multiple select choices (array []).

Table "productss" with: id color_id size_id material_id

Each id is related to another table (hasMany)



            $results = Choice::select('id')
                ->with(['color' => static function ($query) use ($colorid) {
                    $query->whereIn('id', $colorid);
                }])
                ->with(['size' => static function ($query) use ($sizeid) {
                    $query->whereIn('id', $sizeid);
                }])
                ->with(['material' => static function ($query) use ($materialid) {
                    $query->whereIn('id', $materialid);
                }])
                ->paginate(20);

My problem at the moment is this: Imagine a choice as:

id color size material 1 1 2 2 2 2 3 1

My problem is this:

The users may select only 1 of the dropdown, or 2 or all 3 at the same time

When the user select the last dropdown (material), I would like it to take over all the others(but stay organised based on the other choices), ie:

Right now what is happening to me is this: If I select dropdown 1(color) and then dropdown 2(size) and finally dropdown 3(Material), the precise material that I selected in dropdown 3 does not show up, but instead, all the material in all the dropdown menu 1 are showing at once. I have been on it for 2 days without luck. I just need to create a more granular search.

Thanks for your help.

hallindavid

5 years ago

Level 5

I think you should be able to do this...

eager load first, and then filter

$colorid = $request->input('colorid') ?? null;
$sizeid = $request->input('sizeid') ?? null;
$materialid = $request->input('materialid') ?? null;


 $results = Choice::with(['color', 'size','material'])
  ->select('id')
  ->when(!is_null($colorid), function($query) use ($colorid) {
      $query->where('color.id', $colorid);
   })->when(!is_null($sizeid), function($query) use ($sizeid) {
      $query->where('size.id', $sizeid);
   })->when(!is_null($materialid), function($query) use ($materialid) {
      $query->where('material.id', $materialid);
   })->paginate(20);

I think it should work... alternatively, because you're only selecting the ID from the the choice, you could do probalby do a whereHas

$colorid = $request->input('colorid') ?? null;
$sizeid = $request->input('sizeid') ?? null;
$materialid = $request->input('materialid') ?? null;


 $results = Choice::select('id')
  ->when(!is_null($colorid), function($query) use ($colorid) {
      $query->whereHas('color', function(Builder $query) use ($colorid) {
		$query->where('id', '=', $colorid);
       });
   ->when(.....)
   })->paginate(20);

Please or to participate in this conversation.