时间戳

首页 > 解决方案 > 将原始 mysql 查询隐蔽到 yii2 查询 2 左连接子查询并汇总所有

yii2 - 将原始 mysql 查询隐蔽到 yii2 查询 2 左连接子查询并汇总所有

问题描述

我在 mysql 中创建了一个运行良好的原始查询。我正在尝试将其转换为 yii2 查询，但我无法完成它谁能帮助我

原始查询：

SELECT
  ship.month_name,
  MIN(ship.average_value_percent) as minimum_container_utilization,
  MAX(ship.average_value_percent) as maximum_container_utilization,
  SUM(ship.average_value) / COUNT(ship.shipment) as volume,
  SUM(ship.average_weight) / COUNT(ship.shipment) as volume_kgs,
  SUM(ship.average_volume_cbm) / COUNT(ship.shipment) as volume_cbm,
  SUM(ship.average_value_percent) / COUNT(ship.shipment) as volume_percent
FROM
  (
    SELECT
      sh.JS_UniqueConsignRef as shipment,
      SUM(sp.volume) as average_volume_cbm,
      SUM(sp.volume) / SUM(sc.teu) AS average_value,
      SUM(sp.volume) / SUM(sc.capacity) AS average_value_percent,
      SUM(sp.weight) as average_weight,
      date_format(sh.JS_E_ARV, '%b') AS month_name,
      if(
        month(sh.JS_E_ARV) >= month(now()),
        month(sh.JS_E_ARV),
        month(sh.JS_E_ARV) + 12
      ) as months_order
    FROM
      ship_head sh
      JOIN (
        SELECT
          SUM(RC_TEU) teu,
          SUM(RC_Cubiccapacity) capacity,
          ship_head_id,
          JC_ContainerMode,
          JC_ContainerNum
        FROM
          ship_container
        GROUP BY
          ship_head_id,
          JC_ContainerNum,
          JC_ContainerMode
      ) sc ON sc.ship_head_id = sh.sh_id
      JOIN (
        SELECT
          SUM(JL_ActualVolume) volume,
          SUM(JL_ActualWeight) weight,
          ship_head_id,
          JC_ContainerNum
        FROM
          ship_pack
        GROUP BY
          ship_head_id,
          JC_ContainerNum
      ) sp ON sp.ship_head_id = sh.sh_id
    WHERE
      sh.is_latest = 1
      AND sp.JC_ContainerNum = sc.JC_ContainerNum
      AND sc.JC_ContainerMode IN ('FCL', 'FTL')
      AND sh.JS_E_ARV < NOW() - INTERVAL 1 MONTH
      and sh.JS_E_ARV > Date_add(NOW() - INTERVAL 1 MONTH, interval - 12 month)
    GROUP BY
      sh.sh_id
  ) AS ship
GROUP BY
  ship.month_name,
  ship.months_order
ORDER BY
  ship.months_order

Yii2查询

$shipContainerQuery = ShipContainer::find()
    ->select(['SUM(RC_TEU) AS teu' ,'SUM(RC_Cubiccapacity) AS capacity', 'ship_head_id', 'JC_ContainerMode', 'JC_ContainerNum'])
    ->groupBy('ship_head_id', 'JC_ContainerNum', 'JC_ContainerMode')
    ->all();

    $shipPackQuery = ShipPack::find()
    ->select(['UM(JL_ActualVolume) AS volume', 'SUM(JL_ActualWeight) AS weight', 'ship_head_id', 'JC_ContainerNum'])
    ->groupBy('ship_head_id', 'JC_ContainerNum')
    ->all();

    $shipHeadQuery = ShipHead::find()
    ->select(['sh.JS_UniqueConsignRef AS shipment', 'SUM(sp.volume) AS average_volume_cbm', 'SUM(sp.volume) / SUM(sc.teu) AS average_value', 'SUM(sp.volume) / SUM(sc.capacity) AS average_value_percent', 'SUM(sp.weight) AS average_weight', 'date_format(sh." . $filterField . ",'%b') AS month_name', 'if(month(sh." . $filterField . ")>=month(now()), month(sh." . $filterField . "), month(sh." . $filterField . ")+12) as months_order)'])
    ->from(ShipHead::tableName() .' AS sh')
    ->leftJoin(['sc' => $shipContainerQuery], "sc.ship_head_id = sh.sh_id AND sc.JC_ContainerMode IN ('FCL', 'FTL')")
    ->leftJoin(['sp' => $shipPackQuery],'sp.ship_head_id = sh.sh_id AND sp.JC_ContainerNum = sc.JC_ContainerNum')
    ->where(['sh.is_latest' => 1])
    ->andWhere('sh.JS_E_ARV < NOW() - INTERVAL 1 MONTH and sh.JS_E_ARV > Date_add(NOW() - INTERVAL 1 MONTH,interval - 12 month)')
    ->groupBy('sh.sh_id');

    $shipQuery = $shipQuery
    ->addSelect(["SUM(ship.average_value) / COUNT(ship.shipment) AS volume", "'teu' as volume_unit", "SUM(ship.average_value_percent) / COUNT(ship.shipment) AS average_container_utilization"])

标签： yii2

解决方案

您的代码的主要问题是，您all()在子查询中调用方法。多亏了这一点，您将它们的结果传递给另一个查询，而不是将它们作为查询传递。

另一个问题是，在您使用的 php 代码中，leftJoin但在原始 SQL 中，您有JOIN这意味着INNER JOIN。

指定多列时需要使用数组groupBy()。

您可能希望在\yii\db\Expression实例中包装更复杂的表达式，以防止 Yii 在尝试引用表名或列时弄乱您的表达式。

您还可以使用$shipHeadQuery->alias('sh')而不是为 ActiveQuery 添加别名$shipHeadQuery->from(ShipHead::tableName() .' AS sh')。

您可以使用数组来指定选定列的别名以提高可读性。

整个查询可能看起来像这样

//Add following two lines to the other use clauses if they are not already there.
use \yii\db\Expression; 
use \yii\db\Query;

$shipContainerQuery = ShipContainer::find()
    ->select([
        'teu' => new Expression('SUM(RC_TEU)'),
        'capacity' => new Expression('SUM(RC_Cubiccapacity)'),
        'ship_head_id',
        'JC_ContainerMode',
        'JC_ContainerNum',
    ])->groupBy(['ship_head_id', 'JC_ContainerNum', 'JC_ContainerMode']);

$shipPackQuery = ShipPack::find()
    ->select([
        'volume' => new Expression('SUM(JL_ActualVolume)'),
        'weight' => new Expression('SUM(JL_ActualWeight)'),
        'ship_head_id',
        'JC_ContainerNum',
    ])->groupBy(['ship_head_id', 'JC_ContainerNum']);

$shipHeadQuery = ShipHead::find()
    ->select([
        'shipment' => 'sh.JS_UniqueConsignRef',
        'average_volume_cbm' => new Expression('SUM(sp.volume)'),
        'average_value' => new Expression('SUM(sp.volume) / SUM(sc.teu)'),
        'average_value_percent' => new Expression('SUM(sp.volume) / SUM(sc.capacity)'),
        'average_weight' => new Expression('SUM(sp.weight)'),
        'month_name' => new Expression("date_format(sh.JS_E_ARV, '%b')"),
        'months_order' => new Expression('if(month(sh.JS_E_ARV) >= month(now()),  month(sh.JS_E_ARV),month(sh.JS_E_ARV) + 12)'),
    ])->alias('sh')
    ->innerJoin(['sc' => $shipContainerQuery], 'sc.ship_head_id = sh.sh_id')
    ->innerJoin(['sp' => $shipPackQuery], 'sp.ship_head_id = sh.sh_id')
    ->where(['sh.is_latest' => 1])
    ->andWhere('sp.JC_ContainerNum = sc.JC_ContainerNum')
    ->andWhere(['in', 'sc.JC_ContainerMode', ['FCL', 'FTL']])
    ->andWhere(new Expression('sh.JS_E_ARV < NOW() - INTERVAL 1 MONTH'))
    ->andWhere(new Expression('sh.JS_E_ARV > Date_add(NOW() - INTERVAL 1 MONTH, interval - 12 month)')
    ->groupBy('sh.sh_id');

$shipQuery = new Query()
    ->select([
        'ship.month_name',
        'minimum_container_utilization' => new Expression('MIN(ship.average_value_percent)'),
        'maximum_container_utilization' => new Expression('MAX(ship.average_value_percent)'),
        'volume' => new Expression('SUM(ship.average_value) / COUNT(ship.shipment)'),
        'volume_kgs' => new Expression('SUM(ship.average_weight) / COUNT(ship.shipment)'),
        'volume_cbm' => new Expression('SUM(ship.average_volume_cbm) / COUNT(ship.shipment)'),
        'volume_percent' => new Expression('SUM(ship.average_value_percent) / COUNT(ship.shipment)'),
    ])->from(['ship' => $shipHeadQuery])
    ->groupBy(['ship.month_name', 'ship.months_order'])
    ->orderBy(['ship.months_order' => SORT_ASC]);

然后您可以通过调用获得结果，$shipQuery->all()也可以在需要的地方使用查询。

因为我没有数据库结构，所以我没有测试查询。可能有一些拼写错误或查询可能需要一些调试。

推荐阅读