时间戳

首页 > 解决方案 > 如何修复在“group by”子句中添加不必要元素的错误

php - 如何修复在“group by”子句中添加不必要元素的错误

问题描述

我有一个查询，其中我通过逗号分隔获取多个条形码。Syntax error or access violation: 1055 'prod_test.products.id' isn't in GROUP BY当我放入products.idgroup by 子句时收到错误，它给了我错误Syntax error or access violation: 1055 'prod_test.products.name' isn't in GROUP BY，似乎我必须按 select 语句中提到的每个元素进行分组。有什么办法可以解决这类问题。以下是我的查询：

return Product::select(
            'products.id',
            'products.name',
            'products.name_ur',
            'products.barcode',
            'products.unit',
            'products.category_id',
            'products.image',
            'vendor_products.trade_price',
            'products.desc',
            'categories.name as category_name',
            'vendor_categories.category_status as category_status',
            DB::raw("(GROUP_CONCAT(products_barcodes.barcode SEPARATOR ',')) as multiple_barcodes")
        )
        ->leftJoin('vendor_products', function($join){
            $join->on('products.id', '=','vendor_products.product_id');
        })
        ->leftJoin('products_barcodes', function($join){
            $join->on('products.id','=','products_barcodes.product_id');
        })
        ->leftJoin('categories', function($join){
            $join->on('products.category_id','=','categories.id');
        })
        ->leftJoin('vendor_categories', function($join){
            $join->on('categories.id', '=', 'vendor_categories.category_id');
        })
        ->where('products.barcode', $barcode)
        ->groupBy('products.id')
        ->first();

标签： phpmysqllaravel

解决方案

您在选择列表中缺少“products_barcodes.product_id”

return Product::select(
            'products.id',
            'products.name',
            'products.name_ur',
            'products.barcode',
            'products.unit',
            'products.category_id',
            'products.image',
            'products_barcodes.product_id',
            'vendor_products.trade_price',
            'products.desc',
            'categories.name as category_name',
            'vendor_categories.category_status as category_status',
            DB::raw("(GROUP_CONCAT(products_barcodes.barcode SEPARATOR ',')) as multiple_barcodes")
        )
        ->leftJoin('vendor_products', function($join){
            $join->on('products.id', '=','vendor_products.product_id');
        })
        ->leftJoin('products_barcodes', function($join){
            $join->on('products.id','=','products_barcodes.product_id');
        })
        ->leftJoin('categories', function($join){
            $join->on('products.category_id','=','categories.id');
        })
        ->leftJoin('vendor_categories', function($join){
            $join->on('categories.id', '=', 'vendor_categories.category_id');
        })
        ->where('products.barcode', $barcode)
        ->groupBy('products.id')
        ->first();

推荐阅读