Divebo Diving Divebo Diving - 1 month ago 5
MySQL Question

Add 'where' in query with foreach or something similar

this is my problem.

product_field_glue table:

id | field_id | value
----------------------
1 | 50 | blue
2 | 51 | wood
3 | 50 | red
4 | 35 | car


I need to search products by this fields according _GET params

/?field[50][]=blue&field[35][]=car


this should return all blue cars

$result = Products::select('products.*')
->leftJoin('product_field_glue', function ($join) {
$join->on('product_field_glue.product_code', '=', 'products.product_code');
})
->where(function($query) use ($id, $value){
$query->where('product_fields.field_id', $id);
$query->where('product_fields.value', $value);
})


This is modified (part of) query. As you can see I need to set 'where' for all
_GET['field']
and
$key=>$value
of GET parameter need to be in grouped where.
Maybe there is better solution for this kind of search which you can recommend.

Answer

I like to use scopes for this.

Inside your Products class:

/**
 * Warning: requires a join on product_field_glue table
 *
 * @param  \Illuminate\Database\Eloquent\Builder  $query
 * @param  array  $fields
 * @return \Illuminate\Database\Eloquent\Builder
 */
public function scopeSearchFields($query, $fields) {
    if (empty($fields)) {
        return $query;
    }

    foreach ($fields as $id => $values) {
        $query = $query->where(function ($subquery) use($id, $values) {
            $subquery->where('product_field_glue.field_id', $id);
            $subquery->whereIn('product_field_glue.value', $values);
        });
    }

    return $query;
}

Notice that the method is called scopeSearchFields. You can attach it to any Products query as a chained searchFields method:

$result = Products::select('products.*')
    ->leftJoin('product_field_glue', function ($join) {
        $join->on('product_field_glue.product_code', '=', 'products.product_code');
    })
    ->searchFields($request->input('fields'))
    ->get();

The beauty of this is that it's extremely reusable and keeps all of the if and for loops inside of the scope method.

Comments