我已经在mysql中构建了下面的查询。我也试了很多次想变成拉威尔，但没有成功。

SELECT u.id, u.purchase_item_name,u.sales_item_name, us.type,
GROUP_CONCAT(p.itemcode) AS purchase_items,
GROUP_CONCAT(s.itemcode) as sales_items 
FROM composite_inventories as u 
LEFT JOIN composite_has_inventories as us ON u.id = us.composite_inventory_id 
LEFT JOIN inventories as s ON US.inventory_id = s.id AND us.type='sale'
LEFT JOIN inventories as p ON US.inventory_id = p.id AND us.type='purchase'
GROUP BY u.id

我试图在上面的查询中使用laraveldatatabel查询，但它给出了如下错误。
sqlstate[42000]：语法错误或访问冲突：1055“saas.composite\u inventory.purchase\u item\u name”不在group by中（sql:选择group\u concat（p.itemcode）作为purchase\u items，选择group\u concat（s.itemcode）作为sales\u items， composite_inventories . id , composite_inventories . purchase_item_name , composite_inventories . sales_item_name , us . type 从 composite_inventories 左连接 composite_has_inventories 作为 us 在 composite_inventories . id = us . composite_inventory_id 左连接 inventories 作为 s 在 US . inventory_id = s . id 以及 us . type =“sale”左连接 inventories 作为 p 在 US . inventory_id = p . id 以及 us . type ='购买'分组 composite_inventories . id )
我试过下面的拉威尔查询。

$result = Compositeinventory::select([
            DB::raw('GROUP_CONCAT(p.itemcode) as purchase_items'),
            DB::raw('GROUP_CONCAT(s.itemcode) as sales_items'),
            'composite_inventories.id',
            'composite_inventories.purchase_item_name',
            'composite_inventories.sales_item_name',
            'us.type'
        ])->leftJoin('composite_has_inventories as us', 'composite_inventories.id', '=', 'us.composite_inventory_id')
            ->leftJoin('inventories as s', function($join)
                 {
                    $join->on('US.inventory_id', '=', 's.id');
                    $join->on('us.type','=',DB::raw("'sale'"));
                 })
            ->leftJoin('inventories as p', function($join)
                 {
                    $join->on('US.inventory_id', '=', 'p.id');
                    $join->on('us.type','=',DB::raw("'purchase'"));
                 })
        ->groupBy('composite_inventories.id')->get();

我刚才试过了

$row = DB::table('composite_inventories as u')->select([
            'u.id',
            'u.purchase_item_name',
            'u.sales_item_name',
            DB::raw('GROUP_CONCAT(p.itemcode) as purchase_items'),
            DB::raw('GROUP_CONCAT(s.itemcode) as sales_items')
        ])->leftJoin('composite_has_inventories as us', 'u.id', '=', 'us.composite_inventory_id')
            ->leftJoin('inventories as s', function($join)
                 {
                    $join->on('US.inventory_id', '=', 's.id');
                    $join->on('us.type','=',DB::raw("'sale'"));
                 })
            ->leftJoin('inventories as p', function($join)
                 {
                    $join->on('US.inventory_id', '=', 'p.id');
                    $join->on('us.type','=',DB::raw("'purchase'"));
                 })
        ->groupBy('u.id', 'u.purchase_item_name','u.sales_item_name');

上面的查询在显示数据表时起作用。但当我尝试搜索过滤器时，它会给出以下错误，因为数据库中不存在“购买物品”和“销售物品”字段，但它们只是别名。错误是
异常消息：↵↵sqlstate[42000]：语法错误或访问冲突：对本机函数'lower'的调用中有1583个不正确的参数（sql:select count）  作为聚合自（选择 u . id , u . purchase_item_name , u . sales_item_name ，组\u concat（p.itemcode）作为采购\u项，组\u concat（s.itemcode）作为销售\u项 composite_inventories 作为 u 左连接 composite_has_inventories 作为 us 在 u . id = us . composite_inventory_id 左连接 inventories 作为 s 在 US . inventory_id = s . id 以及 us . type =“sale”左连接 inventories 作为 p 在 US . inventory_id = p . id 以及 us . type ='购买'其中（较低( composite_inventories 作为 u.purchase_item_name )如%a%或更低( composite_inventories 作为 u.purchase_items )如%a%或更低( composite_inventories 作为 u.sales_item_name )如%a%或更低( composite_inventories 作为 u.sales_items )例如%a%）分组 u . id , u . purchase_item_name , u . sales_item_name )计数（行表）