在Laravel中查询

Question

Hello guys,

        $filterArray = explode("_", $filters);

        $data['articles'] = \DB::table('products')->join('product_category', function ($q) {
            $q->on('product_category.product_id', '=', 'products.id');
        })->where('product_category.category_id', '=', $id)
            ->select('products.*')
            ->whereBetween('price_retail_1', array($priceFrom, $priceTo))
            ->whereHas('filters', function ($query, $filterArray) {
                $query->whereIn('filter_id', $filterArray);
            })
            ->orderBy('products.' . $sort, $sortOrder)
            ->get();
    }

I have the following query and I'm having some issues on the whereHas method. I'm getting an error

Unknown column 'has' in 'where clause

most likely because the $filterArray variable is out of scope for the function ( or at least that is what I am guessing. Any help on how to solve the issue is appreciated.

Answer 1

You cannot use whereHas method in the Query Builder context. The whereHas method is only for Eloquent Query Builder that is comming from the Eloquent models and their relationships.

What you can do is to use joins. So you can try like this:

$filterArray = explode("_", $filters);

$data['articles'] = \DB::table('products')->join('product_category', function ($q) {
        $q->on('product_category.product_id', '=', 'products.id');
    })->where('product_category.category_id', '=', $id)
        ->select('products.*')
        ->whereBetween('price_retail_1', array($priceFrom, $priceTo))
        ->join('filters', 'products.filter_id', '=', 'filters.filter_id')
        ->whereIn('filter_id', $filterArray);
        ->orderBy('products.' . $sort, $sortOrder)
        ->get();

I don't know how you connecting these two tables so here is only the example data:

->join('filters', 'products.filter_id', '=', 'filters.filter_id')