[PHP知识库]laravel 分表后多表联查统计

	//商品售出数量销售总额统计
    public function count_order_goods($store_ids, $goods_id)
    {
        $start_time = request('start_time');//开始时间
        $end_time = request('end_time');//结束时间

        return (new \App\Model\SmallShop\SmallOrderGoodsView())->setOrderGoodsVieTable($store_ids)
            ->select(DB::raw("sum(num) as total"), DB::raw("sum(price*num) as total_amount"))
            ->when(!empty($start_time) && !empty($end_time), function ($query) use ($start_time, $end_time) {
                $query->whereBetween('created_at', [$start_time, $end_time. ' 23:59:59']);
            })
            ->where('goods_id', $goods_id)->first()->toArray();
    }

<?php

namespace App\Model\SmallShop;

use App\Model\BaseModel;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class SmallOrderGoodsView extends BaseModel
{
    protected $table = 'small_order_goods_view';

    /**
     * @param $data
     * @return SmallOrderGoodsView
     */
    public function setOrderGoodsVieTable($data)
    {
        /*if (Schema::hasTable('z_' . $v . '_small_order_goods')) { }*/
        $field = 'goods.num, goods.price, goods.goods_id, order.created_at';
        $sql = 'SELECT ' . $field . ' FROM ';
        foreach ($data as $k => $v) {
            $join = 'as `goods` inner join `jhkj_z_' . $v . '_small_orders` as `order` on `goods`.`order_id` = `order`.`id` where `order`.`pay_time` is not null';
            if ($k == 0) {
                $sql .= 'jhkj_z_' . $v . '_small_order_goods ' . $join;
            } else {
                $sql .= ' UNION ALL SELECT ' . $field . ' FROM jhkj_z_' . $v . '_small_order_goods ' . $join;
            }
        }

        return $this->setTable(DB::raw("({$sql}) AS small_order_goods_view"));
    }
}