thinkphp with leftjoin以及where

主表 tp_company_customer

CREATE TABLE `tp_company_customer` (
  `customer_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_code` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `name_zh` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `addr` int(11) DEFAULT NULL,
  PRIMARY KEY (`customer_id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

插入数据

INSERT INTO `tp_company_customer` VALUES ('1', 'CD-CBD', '茶百道', null);
INSERT INTO `tp_company_customer` VALUES ('2', 'CD-KFC', '肯德基', null);

附表 tp_service_contract

CREATE TABLE `tp_service_contract` (
  `contract_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(10) NOT NULL DEFAULT '0',
  `service_type` int(3) NOT NULL DEFAULT '0',
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  PRIMARY KEY (`contract_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

插入数据

INSERT INTO `tp_service_contract` VALUES ('1', '1', '1', '2024-11-20', '2025-10-19');
INSERT INTO `tp_service_contract` VALUES ('2', '1', '2', '2024-12-06', '2025-11-05');
INSERT INTO `tp_service_contract` VALUES ('3', '2', '2', '2024-11-21', '2025-10-20');

附表

CREATE TABLE `tp_job_order` (
  `job_id` int(11) NOT NULL AUTO_INCREMENT,
  `contract_id` int(10) NOT NULL DEFAULT '0',
  `customer_id` int(10) NOT NULL DEFAULT '0',
  `job_date` date DEFAULT NULL,
  PRIMARY KEY (`job_id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `tp_job_order` VALUES ('1', '1', '1', '2024-11-22');
INSERT INTO `tp_job_order` VALUES ('2', '2', '1', '2024-11-23');
INSERT INTO `tp_job_order` VALUES ('3', '3', '2', '2024-11-24');
INSERT INTO `tp_job_order` VALUES ('4', '1', '1', '2024-11-25');

模型

<?php
namespace app\common\model\company;

use app\common\model\work\OrderModel;
use think\Model;

class CustomerModel extends Model
{
    // 表名
    protected $name = 'company_customer';
    // 表主键
    protected $pk = 'customer_id';



    public function orderList()
    {
        return $this->hasMany(OrderModel::class,'contract_id','contract_id');
    }
}

<?php

namespace app\common\service\company;

use app\common\model\company\CustomerModel;
use think\facade\Db;

class CustomerService
{
    public static function list(){
        echo "<pre>";
        $param['start_date'] = '2024-01-01';
        $param['end_date'] = '2024-11-23';

        $list = (new CustomerModel())
            ->alias('a')
            ->field('a.customer_id, a.customer_code, a.name_zh, b.contract_id, b.service_type')
            ->leftjoin('tp_service_contract b','b.customer_id = a.customer_id')
            ->with(['orderList'=>function($query) use ($param){
                $start_date = $param['start_date'];
                $end_date = $param['end_date'];

                $query->alias('a');
                $query->field('a.job_id, a.job_date, a.contract_id, b.content');
                $query->leftjoin('tp_order_info b','b.job_id = a.job_id');

                $query->where('job_date','>=', $start_date);
                $query->where('job_date','<=', $end_date);

            }])
            ->order('customer_id','desc')
            ->select()->toArray();

        print_r($list);

    }
}