CI3.x 活动记录 - 如何使用别名字段链接查询

Question

这是我正在尝试构建的查询，它有效：

         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;
 }

Answer 1

解决方案是：

 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;
   }