Cakephp 2.6
我有一个模型，Temps，它有很多票。在 Temps 的索引视图中，我想为每条记录返回日期最接近当前日期的票证。
在mySQL中，它可以完成为：
'SELECT expiry_date FROM uploads WHERE expiry_date > CURDATE() ORDER BY expiry_date ASC LIMIT 1'
但是我不知道如何将其作为子查询运行。我当前生成结果的查询如下：(请记住，这已为数据表配置(票证是上传模型的别名
public function getAjaxIndexData($data) {
$tokens = explode(" ", $data['searchString']);
$conditions = array(
$this->alias . '.deleted' => false,
'OR' => array(
'CONCAT(' . $this->alias . '.first_name," ",' . $this->alias . '.last_name) LIKE' => '%' . implode(' ', $tokens) . '%',
),
$data['columnsFilter']
);
$fields = array(
'id',
'full_name',
'pps_number',
'mobile',
'email',
'start_date',
'time_served'
);
$order = array(
$data['orderField'] => $data['order']
);
$contain = array(
'LocalOffice.name',
);
$options = array(
'conditions' => $conditions,
'fields' => $fields,
'order' => $order,
'contain' => $contain,
'limit' => $data['limit'],
'offset' => $data['start']
);
$optionsNoFields = array(
'conditions' => $conditions,
'contain' => $contain,
);
$result['draw'] = $data['draw'];
$result['recordsTotal'] = $recordTotal = $this->find('count');
$result['recordsFiltered'] = $this->find('count', $optionsNoFields);
$result['data'] = $this->find('all', $options); //standard search
$result['data'] = $this->formatTable($result['data']);
return json_encode($result);
}
在此查询中，我想添加一个字段，显示每个 Temp 的最近到期日期。
我将如何构建它？
动态创建虚拟字段：
$this->virtualFields['nearest'] = '(SELECT expiry_date FROM uploads WHERE expiry_date > CURDATE() AND uploads.owner_id = '.$this->alias.'.ticket_id ORDER BY expiry_date ASC LIMIT 1')';
然后调整字段数组
$fields = array(
'id',
'full_name',
'pps_number',
'mobile',
'email',
'start_date',
'time_served',
'nearest'
);
此外，查询可以重写为("temp"需要替换为模型别名(
SELECT MIN(expiry_date)
FROM uploads
WHERE expiry_date > CURDATE()
AND uploads.owner_id = temp.ticket_id;
这意味着可能性能更好的查询是将该子查询从 SELECT 语句的列中移到 JOIN。例如：
SELECT *
FROM temp
LEFT JOIN (SELECT MIN(expiry_date) AS expiry,owner_id
FROM uploads
WHERE expiry_date > CURDATE())
GROUP BY owner_id) AS next_dates
ON next_dates.owner_id = temp.ticket_id;