php - Mysql group by 和 Left join 关联表 PHP Yii2

Question

我有两个表 order_products 和 order_product_tax ，它们具有一对多关系。一个 order_products 可以有多个 order_product_tax

这是两个表的 SQL fiddle http://sqlfiddle.com/#!9/2608e

我需要得到这样的输出

[  
 {  
  "product_id":2809,
  "product_name":"Test Product1",
  "quantity":5,
  "amount":500,
  "taxes":[  
     {  
        "tax_id":47,
        "tax_name":"VAT",
        "tax_amount":21.14
     },
     {  
        "tax_id":48,
        "tax_name":"CST",
        "tax_amount":5.17
     }
  ]
 },
 {  
  "product_id":2810,
  "product_name":"Test Product2",
  "quantity":4,
  "amount":200,
  "taxes":[  
     {  
        "tax_id":47,
        "tax_name":"VAT",
        "tax_amount":12.57
     },
     {  
        "tax_id":48,
        "tax_name":"CST",
        "tax_amount":11.34
     }
  ]
 }
]

我怎样才能得到这个结果？

如果重要的话，我正在使用 PHP 和 Yii2。

任何帮助将不胜感激。

编辑:Yii2 模型

订购产品

class OrderProduct extends \yii\db\ActiveRecord
{

   public static function tableName()
   {
    return 'order_product';
   }


   public function rules()
   {
    return [
        [['product_id', 'order_id', 'quantity'], 'integer'],
        [['amount'], 'number'],
        [['product_name'], 'string', 'max' => 250],
    ];
   }

   public function getTaxes() {
    return $this->hasMany(OrderProductTax::className(),['order_product_id' => 'id']);
   }

   public function attributeLabels()
   {
    return [
        'id' => 'ID',
        'product_id' => 'Product ID',
        'order_id' => 'Order ID',
        'product_name' => 'Product Name',
        'amount' => 'Amount',
        'quantity' => 'Quantity',
    ];
   }
}

订单产品税

class OrderProductTax extends \yii\db\ActiveRecord
{

   public static function tableName()
   {
    return 'order_product_tax';
   }

   public function rules()
   {
    return [
        [['order_product_id', 'tax_id'], 'integer'],
        [['tax_amount'], 'number'],
        [['tax_name'], 'string', 'max' => 20],
    ];
   }

   public function attributeLabels()
   {
    return [
        'id' => 'ID',
        'order_product_id' => 'Order Product ID',
        'tax_id' => 'Tax ID',
        'tax_name' => 'Tax Name',
        'tax_amount' => 'Tax Amount',
    ];
   }
}

Answer 1

这是 SQL。您可以使用 group concat 将列组合成 json:

SELECT amounts.*
  ,GROUP_CONCAT(CONCAT('{tax_id:"', tax_id, '", tax_name:"',tax_name,'", tax_amount:"',tax_amount,'"}'))
FROM
(
SELECT op.product_id, 
op.product_name, 
SUM(op.amount) as amount, 
SUM(op.quantity) as quantity
FROM order_product op 
group by 1,2
)amounts
LEFT JOIN 
(
select product_id
,tax_id
,tax_name
,sum(tax_amount) as tax_amount
from order_product op
join ordeR_product_tax opt
on op.id = opt.order_producT_id
group by 1,2,3
) taxes
on amounts.product_id = taxes.product_id
group by 1,2,3,4