Yii2 联表查询

表结构:

CREATE TABLE IF NOT EXISTS `attendance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `status` tinyint(1) DEFAULT '0' COMMENT '状态',
  `date` int(11) NOT NULL COMMENT '考勤日期',
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `attendance_item` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `attendance_id` int(11) NOT NULL COMMENT '考勤id',
  `employee_id` int(11) NOT NULL COMMENT '员工ID',
  `daily_price` decimal(19,2) DEFAULT '0.00' COMMENT '工价',
  `work_time` decimal(3,1) DEFAULT '0.0' COMMENT '上班时长',
  `status` int(11) DEFAULT '1' COMMENT '状态',
  `date` int(11) NOT NULL COMMENT '考勤日期',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=67 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

需求:

根据员工id以及时间段,查询员工在这个时间段里的上班时长的和,同时同兼顾第一张表里的状态值符合特定的值。

原生sql:

SELECT SUM(t2.work_time) FROM `attendance` t1, `attendance_item` t2 WHERE (t1.`id` = t2.`attendance_id`) AND (t1.`status` = 0) AND (t2.`employee_id`=31) AND (t2.`date` BETWEEN 1530374400 AND 1533052799)

结果:

$start_time = empty($date) ? strtotime(date('Y-m')) : strtotime($date);  // 当月第1秒
$end_time = strtotime(date('Y-m', $start_time) . "1 month") -1;  // 当月最后1秒

$query = self::find()->alias('item')
    ->leftJoin('attendance', 'attendance.id = item.attendance_id')
    ->where(['employee_id' => $employee_id])
    ->andWhere(['between', 'item.date', $start_time, $end_time])
    ->andWhere(['attendance.status' => Attendance::STATUS_SUCCESS]);

//var_dump($query->createCommand()->getRawSql());

return $query->sum('work_money');