Divebo Diving Divebo Diving - 8 months ago 43
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.


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.