将mysql查询转换为雄辩的查询问题

ogq8wdun  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(320)

我已经在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 . typecomposite_inventories 左连接 composite_has_inventories 作为 uscomposite_inventories . id = us . composite_inventory_id 左连接 inventories 作为 sUS . inventory_id = s . id 以及 us . type =“sale”左连接 inventories 作为 pUS . 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 作为 usu . id = us . composite_inventory_id 左连接 inventories 作为 sUS . inventory_id = s . id 以及 us . type =“sale”左连接 inventories 作为 pUS . 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 )计数(行表)

fae0ux8s

fae0ux8s1#

这不是laravel的问题您的查询是无效的,因为它包含一些未聚合的列,并且在group子句中没有提到。要更正此问题,您需要将group by子句更新为

group by u.id, u.purchase_item_name,u.sales_item_name, us.type

在查询生成器中

->groupBy('composite_inventories.id', 'composite_inventories.purchase_item_name','composite_inventories.sales_item_name', 'composite_has_inventories.type')

如果有多个 type (s) 根据复合目录,我猜您也需要groupconcat,并从groupby子句中删除此列。
语法错误更新,您没有正确使用lower函数,只需传递要将其值转换为小写的列名,如

WHERE (
      LOWER(u.purchase_item_name) LIKE '% a %'
      OR LOWER(u.purchase_items) LIKE '% a %'
      OR LOWER(u.sales_item_name`) LIKE '% a %' 
      OR LOWER(u.sales_items) LIKE '% a %'
)

如果数据库排序规则未设置为区分大小写,则无需使用lower()函数了解更多信息请参阅字符串搜索中的b.5.4.1区分大小写
另外,如果您在查询中使用mysql函数,您可能需要 raw() laravel的查询生成器方法

相关问题