php - SQL 查询在 Laravel 中不起作用

Question

在 phpMyAdmin 中这个 SQL 查询工作正常

SELECT products.id FROM products, characteristics 
                WHERE products.id = characteristics.pr_id AND products.c_id = 1
                AND characteristics.value IN ('8-core', 'Quad-Core') 
                AND price BETWEEN 50 AND 5000 GROUP BY products.id

但是当我试图让它在我的模型中工作时，它失败了。

$products = DB::select('SELECT products.id FROM products, characteristics 
        WHERE products.id = characteristics.pr_id AND products.c_id = ?
        AND characteristics.value IN (?) 
        AND price BETWEEN ? AND ? GROUP BY products.id', [$catId, $query, $minPrice, $maxPrice]); 
dd($products);

PS:如果在模型中，我将只使用 $query 中的一个参数(例如“四核”)，一切正常。但是，一旦我尝试使用多个参数，我就会得到空数组。

Controller

$values = Input::get('value');
$category = Input::get('cat');
$brand = Input::get('brand');
$minPrice = Input::get('min_price');
$maxPrice = Input::get('max_price');
$query = implode('", "', $values);

$product = new Product();
$products = $product->getProductsByPar($query, $minPrice, $maxPrice, $category, $brand);

模型

public function getProductsByPar($query, $minPrice, $maxPrice, $cat, $brand)
{
    $category = new Category();
    $brands = new Brand();
    $minPrice = (int)$minPrice;
    $maxPrice = (int)$maxPrice;
    $catId = $category->getCategoryId($cat)->id;

    if($brand == 'none')
    {
    $products = DB::select('SELECT products.id FROM products, characteristics 
            WHERE products.id = characteristics.pr_id AND products.c_id = ?
            AND characteristics.value IN (?) 
            AND price BETWEEN ? AND ? GROUP BY products.id', [$catId, $query, $minPrice, $maxPrice]);            
    }
    else
    {
    $brandId = $brands->getBrandId($brand)->id;
    $products = DB::select('SELECT products.id FROM products, characteristics 
            WHERE products.id = characteristics.pr_id AND products.c_id = ?
            AND products.b_id = ? AND characteristics.value IN (?) 
            AND price BETWEEN ? AND ? GROUP BY products.id', [$catId, $brandId, $query, $minPrice, $maxPrice]);             
    }
    dd($products);
    return $products;

我在 Model 中做查询有什么问题？

Answer 1

尝试使用 Laravel query builder .

// Prepare parameters
$categoryId = 1;
$values = ['8-core', 'Quad-Core'];
$minPrice = 50;
$maxPrice = 5000;

$products = DB::table('products')
    ->select('products.id, characteristics.*')
    ->join('characteristics', 'products.id', '=', 'characteristics.pr_id')
    ->where('products.c_id', $categoryId)
    ->whereIn('characteristics.value', $values)
    ->whereBetween('products.price', [$minPrice, $maxPrice])
    ->groupBy('products.id')
    ->get();