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中只使用一个参数(例如“四核”)，一切都很好。但是，一旦我尝试使用几个参数，我就得到了空数组。

控制器

$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();

Answer 2

问题是，您正在尝试在准备好的语句中使用in，但您不能这样做。

您的问题将被评估为：

characteristics.value IN ("'8-core', 'Quad-Core'")

这并不是真的有意义。