How to find Array values to search in FIND_IN

Hi I am facing a problem with Laravel eloquent query. I have a table named as 'Offer' and it is connected to another table using relation function 'ApplyOn'. here is the code

 $offer = Offer::whereHas('ApplyOn',function($query) use ($input){
                $query->whereRaw("find_in_set(".$input['size_id'].",size_ids)");
            })->get();

here $input['size_id'] is an array so I can't get the result using this query please help me to find out the solution.

if it is not possible with the find_in_set function then how can I solve this problem? I want to sort the data by the given array value.

The $input['size_id'] is an array. for example [1,2,3].

The table field 'size_ids' is a string value with comma-separated. for example 1,2,3,4,5

Offer table

ApplyOn table

CodePudding user response：

If you just want to filter the data with an array, you can use whereIn.

example -

$offer = Offer::whereHas('ApplyOn', function ($query) use ($input) {
    $query->whereIn("field_name", $input['size_id']);
})->get();

If you have saved the data as a string, and need to filter you can use like.

$offer = Offer::whereHas('ApplyOn', function ($query) use ($input) {
    $query->where("field_name", 'LIKE', '%' . $input['size_id'] . '%');
})->get();

CodePudding user response：

You want find all the size Ids matching with the $input['size_id'] and this is an array you can do something like

  $offer = Offer::whereHas('ApplyOn', function ($query) use ($input) {
            if (isset($input['size_id']) and is_array($input['size_id'])) {
                $ids_to_filter = $input['size_id'];
                $query->where(function ($sub) use ($ids_to_filter) {
                    foreach ($ids_to_filter as $value) {
                        $sub->where('size_ids', 'like', '%' . $value . '%');
                    }
                });
            }
        })->get();

If with only ONE is a valid ApplOn use an WHERE OR

 $sub->orWhere('size_ids', 'like', '%' . $value . '%');

Maybe this can help you