$data['collect'] = Collect::all(function (Query $query) use ($id) {
$query->field('id,start_date,end_date,contract_num,villa_id,staff_id,CASE WHEN (TIMESTAMPDIFF(SECOND,CURDATE(),end_date) <= 60*60*24*30 and TIMESTAMPDIFF(SECOND,CURDATE(),end_date) > 0) THEN 3 WHEN TIMESTAMPDIFF(SECOND,CURDATE(),end_date) > 60*60*24*30 THEN 2 WHEN TIMESTAMPDIFF(SECOND,CURDATE(),end_date) <= 0 THEN 1 END AS t')->where(['customer_id'=>$id])->order('t desc,end_date asc')->fetchSql(true);
});查询如上但是解析出来的sql就不对了 SELECT `id`,`start_date`,`end_date`,`contract_num`,`villa_id`,`staff_id`,CASE WHEN (TIMESTAMPDIFF(SECOND,CURDATE(),end_date) <= 60*60*24*30 and TIMESTAMPDIFF(SECOND,end_date) > 0) THEN 3 WHEN TIMESTAMPDIFF(SECOND,end_date) > 60*60*24*30 THEN 2 WHEN TIMESTAMPDIFF(SECOND,end_date) <= 0 THEN 1 END AS t FROM `collect_house_contract` WHERE `collect_house_contract`.`delete_time` IS NULL AND `customer_id` = 14026 ORDER BY t desc,end_date asc这是解析出来的sql。。。从第二个TIMESTAMPDIFF函数开始就丢失了CURDATE函数了。。。研究了1个小时不管怎么写都丢失CURDATE函数解决办法field写成数组
最佳答案
