如何从特定数据执行order_by
如何从特定数据执行order_by
提问于 2019-08-02 17:48:04
我有一个这样的数据库
order_id | date | qty
------------------------------------
a |2018-11-11 10:03:33 |1
b |2018-11-12 10:03:33 |1
c |2018-11-11 12:03:33 |1我有一个这样的模型，
public function get_total_sales_per_day($date_from, $date_to)
{
$this->db->select('order_id, SUM(qty) as total, date');
$this->db->from("order");
$this->db->group_by('date');
$this->db->order_by('date','asc');
$query = $this->db->get();
log_message("debug", $this->db->last_query());
return $query->result();
}如何仅从年、月、日使用group_by date？
回答 2
Stack Overflow用户
发布于 2019-08-02 19:22:29
如果只想像(y-m-d)那样按日期分组，则不按时间应用分组
然后在代码中进行一些更改，如：
在您的代码中进行一些修改
public function get_total_sales_per_day($date_from, $date_to)
{
$this->db->select('order_id, SUM(qty) as total, date');
$this->db->from("order");
$this->db->group_by(DATE_FORMAT('date', "%y-%y-%d"));
$this->db->order_by('date','asc');
$query = $this->db->get();
log_message("debug", $this->db->last_query());
return $query->result();
}Stack Overflow用户
发布于 2019-08-02 19:48:16
你只需要在使用DATE()函数时进行转义
/*You have not used arguments (date_from, date_to)
anywhere in active record, decide whether you need that,
*/
public function get_total_sales_per_day($date_from, $date_to)
{
$this->db->select('order_id, SUM(qty) as total, date');
$this->db->from("order");
/* This is what you need, $escape = FALSE */
$this->db->group_by('DATE(`date`)',FALSE);
/* If field exists in select then
you can also use : $this->db->order_by('3','asc',FALSE);
meaning sort by 3rd field
OR like below more readable
*/
$this->db->order_by('DATE(`date`)','asc',FALSE);
/* if you wish to order by datetime then you may keep
$this->db->order_by('date','asc');
*/
$query = $this->db->get();
log_message("debug", $this->db->last_query());
return $query->result();
}你可以在这里阅读源代码:
public function group_by($by, $escape = NULL)
public function order_by($orderby, $direction = '', $escape = NULL)
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/57323949
复制相关文章
相似问题
腾讯云开发者
