Divebo Diving Divebo Diving - 11 months ago 54
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


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
of GET parameter need to be in grouped where.
Maybe there is better solution for this kind of search which you can recommend.

Answer Source

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');

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