我已经在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
)计数(行表)
1条答案
按热度按时间fae0ux8s1#
这不是laravel的问题您的查询是无效的,因为它包含一些未聚合的列,并且在group子句中没有提到。要更正此问题,您需要将group by子句更新为
在查询生成器中
如果有多个
type
(s) 根据复合目录,我猜您也需要groupconcat,并从groupby子句中删除此列。语法错误更新,您没有正确使用lower函数,只需传递要将其值转换为小写的列名,如
如果数据库排序规则未设置为区分大小写,则无需使用lower()函数了解更多信息请参阅字符串搜索中的b.5.4.1区分大小写
另外,如果您在查询中使用mysql函数,您可能需要
raw()
laravel的查询生成器方法