将指定MySQL查询转换为Laravel query builder代码的实现方法

需求说明 #

现有一段可正常运行的MySQL查询，需要转换为等效的Laravel Query Builder代码用于农产品管理系统开发，原查询语句如下：

SELECT
    vegitables.name, vegitables.image, vegitables.catagory,
    AVG(price_wholesale),
    SUM(CASE WHEN rank = 1 THEN price_wholesale ELSE 0 END) today,
    SUM(CASE WHEN rank = 2 THEN price_wholesale ELSE 0 END) yesterday
FROM (
    SELECT
        veg_id, price_wholesale, price_date,
        RANK() OVER (PARTITION BY veg_id ORDER BY price_date DESC) as rank
    FROM old_veg_prices
) p
INNER JOIN vegitables ON p.veg_id = vegitables.id
WHERE rank in (1,2)
GROUP BY veg_id

查询涉及两张数据表，结构如下：

1. vegitables表结构 #

CREATE TABLE `vegitables` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `image` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `catagory` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `total_area` int(11) NOT NULL COMMENT 'Total area of culativate in Sri Lanka (Ha)',
  `total_producation` int(11) NOT NULL COMMENT 'Total production particular product(mt)',
  `annual_crop_count` int(11) NOT NULL COMMENT 'how many time can crop pre year',
  `short_dis` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `vegitables`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `vegitables`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
COMMIT;

2. old_veg_prices表结构 #

CREATE TABLE `old_veg_prices` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `veg_id` int(11) NOT NULL,
  `price_wholesale` double(8,2) NOT NULL,
  `price_retial` double(8,2) NOT NULL,
  `price_location` int(11) NOT NULL,
  `price_date` date NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `old_veg_prices`
ADD PRIMARY KEY (`id`);
ALTER TABLE `old_veg_prices`
MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
COMMIT;

实现代码 #

通过Laravel查询构造器的fromSub方法处理子查询，配合selectRaw实现窗口函数和聚合逻辑，完整代码如下：

<?php
// 构造内层带RANK窗口函数的子查询
$subQuery = \DB::table('old_veg_prices')
    ->selectRaw('veg_id, price_wholesale, price_date, RANK() OVER (PARTITION BY veg_id ORDER BY price_date DESC) as `rank`');

// 外层查询关联蔬菜表，执行聚合统计
$result = \DB::table('p')
    ->fromSub($subQuery, 'p')
    ->join('vegitables', 'p.veg_id', '=', 'vegitables.id')
    ->whereIn('p.rank', [1, 2])
    ->selectRaw('
        vegitables.name, 
        vegitables.image, 
        vegitables.catagory,
        AVG(p.price_wholesale) as avg_wholesale_price,
        SUM(CASE WHEN p.rank = 1 THEN p.price_wholesale ELSE 0 END) as today_price,
        SUM(CASE WHEN p.rank = 2 THEN p.price_wholesale ELSE 0 END) as yesterday_price
    ')
    ->groupBy('p.veg_id')
    ->get();

兼容调整说明 #

• 若使用的Laravel版本低于5.6不支持fromSub方法，可替换为from(\DB::raw("({$subQuery->toSql()}) as p"))，同时追加->mergeBindings($subQuery->getQuery())绑定子查询参数
• 若开启了MySQL严格模式，需要把vegitables.name、vegitables.image、vegitables.catagory也加入分组条件，修改为->groupBy('p.veg_id', 'vegitables.name', 'vegitables.image', 'vegitables.catagory')即可避免SQL报错
• 可根据业务需要自定义聚合字段的别名，方便后续逻辑调用

内容的提问来源于stack exchange，提问作者Nipun Sachinda