php - CI3.x Active Record - 如何使用别名字段链接查询
问题描述
这是我正在尝试构建的查询:
select
year(created_timestamp) as year,
month(created_timestamp)as month,
MONTHNAME(created_timestamp) month_name,
count(*) total_published
From posts
GROUP BY year, MONTH(created_timestamp), MONTHNAME(created_timestamp)
ORDER BY year DESC, month DESC';
现在我正在尝试使用活动记录构建上述查询,如下所示:
$this->db->simple_query('year(created_timestamp) as year,
month(created_timestamp) as month, monthname(created_timestamp) as monthname,
COUNT(*) post_count');
$this->db->group_by('year');
$this->db->group_by('monthname');
$this->db->group_by('month');
$this->db->order_by('year', 'desc');
$this->db->order_by('month', 'desc');
$this->db->from('posts');
$query = $this->db->get();
以下是我得到的错误。它显然不知道别名。如何让 CI 识别别名?
Unknown column 'year' in 'order clause'
SELECT * FROM `posts` GROUP BY `year`, `monthname`, `month` ORDER BY
`year` DESC, `month` DESC
一些调试工作产生了解决方案:
经过多次调试,这是解决方案:
function blog_archive(){
$this->db->select('year(created_timestamp) as year, month(created_timestamp) as month, monthname(created_timestamp) as monthname, COUNT(*) post_count');
$this->db->from('posts');
$this->db->group_by('year');
$this->db->group_by('monthname');
$this->db->group_by('month');
$this->db->order_by('year', 'desc');
$this->db->order_by('month', 'desc');
$blog_archive = $this->db->get()->result_array();;
return $blog_archive;
}
解决方案
解决方案是:
function blog_archive(){
$this->db->select('year(created_timestamp) as year, month(created_timestamp) as month, monthname(created_timestamp) as monthname, COUNT(*) post_count');
$this->db->from('posts');
$this->db->group_by('year');
$this->db->group_by('monthname');
$this->db->group_by('month');
$this->db->order_by('year', 'desc');
$this->db->order_by('month', 'desc');
$blog_archive = $this->db->get()->result_array();;
return $blog_archive;
}
推荐阅读
- mysql - MYSQL 添加索引 - 不添加索引?
- git - 防止未经检查的更改合并到主分支
- jwt - 使用 auth0 身份验证登录公司网站重定向回登录
- typescript - 打字稿:省略不会警告额外的道具
- mitmproxy - 使用 mitmproxy UI 编辑多部分/表单数据时出现问题
- c# - 将 ObservableCollection 绑定到 ItemsControl
- jquery - 数据表从解析的 json 更改文本颜色
- maven - Apache cxf-codegen-plugin 未在 Spring Tool Suite 中生成 Java 存根
- python - 使用 python 托盘将 json 文件(3d 绘图)导入 ARCHICAD 24
- ruby-on-rails - 如何从 Rails 缓存哈希上传照片?