php - SQL查询中的cakephp虚拟字段

我正在尝试在cakephp 1.3中使用cakephp virtualFields。我的SQL查询如下，但我需要day_index（我的虚拟字段）为“ DAYOFWEEK（start_date）”。

我需要重写查询

$data = $this->Calendar->query("SELECT *, DAYOFWEEK(start_date) as day_index, TIME(start_time) as time
    FROM calendars WHERE calendar_category_id =$cal ORDER BY day_index, time");

变成这种格式：

$sqlConditions = array("Calendar.calendar_category_id"=>$cal);
$sqlOrderBy = array("Calendar.day_index", "Calendar.time asc");
$sqlParams = array('conditions'=>$sqlConditions,'order'=>$sqlOrderBy);
$data = $this->Calendar->find('all',$sqlParams);
$this->set('data',$data);

因此，我不确定如何/在何处放置或声明虚拟字段。

$fields = $this->Calendar->virtualFields['day_index'].'AS 'DAYOFWEEK(start_date)';

最佳答案

找到了两种方法来做到这一点，不确定一种方法是否比另一种方法更好。
控制器仅编辑方法

$sqlFields = array("DAYOFWEEK(Calendar.start_date) as day_index",
    "TIME(Calendar.start_time) as time",
    "Calendar.start_date",
    "Calendar.calendar_category_id",
    "Calendar.start_time");
    $sqlConditions = array("Calendar.calendar_category_id"=>$cal);
    $sqlOrderBy = array("Calendar.day_index, Calendar.time asc");
    $sqlParams = array('fields'=>$sqlFields,'conditions'=>$sqlConditions,'order'=>$sqlOrderBy);
    $data = $this->Calendar->find('all',$sqlParams);
    return $data;
    exit();

基于该方法对控制器进行编辑的模型方法
型号>

var $virtualFields = array(
   'day_index' => "DAYOFWEEK(Calendar.start_date)",
   'time' => "TIME(Calendar.start_time)"
 );
 var $displayIndex = 'day_index';
 var $displayTime = 'time';

控制器>

$sqlConditions = array("Calendar.calendar_category_id"=>$cal);
    $sqlOrderBy = array("Calendar.day_index, Calendar.time asc");
    $sqlParams = array('conditions'=>$sqlConditions,'order'=>$sqlOrderBy);
    $data = $this->Calendar->find('all',$sqlParams);
    return $data;
    exit();

关于php - SQL查询中的cakephp虚拟字段，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/31885278/