**TP5 使用 union 后 order by 排序与 paginate分页**
在项目开发过程中遇到需要使用 union() 方法的情况,出现了很多问题,于是记录一下。
在最开始时使用
$listInfo = (new AdminFinancial())->alias('f')
->field('f.*,fa.adjustment_type as details_type,fa.currency_amount,fa.currency_code')
->join('admin_financial_adjustment fa ',"f.id = fa.financial_id" ,"left")
->where("fa.currency_amount != 0")
->union("SELECT f.*,fc.charge_type as details_type,fc.currency_amount,fc.currency_code FROM `admin_financial` `f` inner JOIN `admin_financial_charge` `fc` ON `f`.`id` = fc.financial_id WHERE f.auth_id = '$shop_id' AND f.poste_date BETWEEN '$start_date' AND '$end_date' and fc.currency_amount != 0")
->union("SELECT f.*,ff.fee_type as details_type,ff.currency_amount,ff.currency_code FROM `admin_financial` `f` inner JOIN `admin_financial_fee` `ff` ON `f`.`id` = ff.financial_id WHERE f.auth_id = '$shop_id' AND f.poste_date BETWEEN '$start_date' AND '$end_date' and ff.currency_amount != 0 ")
->select();
后,可以查出所需数据,但加入 order() 进行排序时,会出现
Incorrect usage of UNION and ORDER BY
这种错误,把order by 放入到 union 子句中也并没有什么效果,之后经过查找资料发现可以构建子查询进行解决
$info1 = (new AdminFinancial())->alias('f')
->field('f.*,fa.adjustment_type as details_type,fa.currency_amount,fa.currency_code')
->join('admin_financial_adjustment fa ',"f.id = fa.financial_id" ,"left")
->where("fa.currency_amount != 0")
->buildSql();
$info2 = (new AdminFinancial())->alias('f')
->field('f.*,fc.charge_type as details_type,fc.currency_amount,fc.currency_code')
->join('admin_financial_charge fc ',"f.id = fc.financial_id" ,"left")
->where("fc.currency_amount != 0")
->buildSql();
$info3 = (new AdminFinancial())->alias('f')
->field('f.*,ff.fee_type as details_type,ff.currency_amount,ff.currency_code')
->join('admin_financial_fee ff ',"f.id = ff.financial_id" ,"left")
->where("ff.currency_amount != 0")
->union([$info1,$info2])
->buildSql();
$listInfo = db::table($info3.' as f')
->order("f.id asc")
->paginate('10', false, ['page' => '1'])
->toArray();
结果正确,分页也可以使用。
注意:构建子查询是 查询字段需要保持一致