php - 限定 MONTHNAME 中的列

Question

我遇到了一个我似乎无法理解的小问题。

我正在我的数据库中搜索一个用户一个月内收到的评论总数，然后绘制成图表。

我遇到的问题是在我的 SQL 语句中使用 MONTHNAME 如果我使用

$this->db->select('MONTHNAME(`created_on`), COUNT(`comment.id`)');

如果我尝试用列前面的表名限定它，我会发现 created_on 字段不明确

public function getTotalCommentsPerMonth() {

    $this->db->select('MONTHNAME(`photo_comment.created_on`), COUNT(`comment.id`)');
    $this->db->from('photo_comment');        
    $this->db->join('photo', 'photo.id = photo_comment.photo_id');
    $this->db->where('photo.userId', $this->auth->userid());
    return $this->db->get()->result_array();
}

我得到 Unknown column 'photo_comment.created_on' in 'field list' Are you not qualify a column when using MONTHNAME?

谢谢你的帮助

更新:photo_comment 和照片表的概述

## photo_comment
comment_id
photo_id
user_id
created_on
comment

## photo
id
title
file_name
upload_path
userId
description
created_on

Answer 1

如果您在查询中添加反引号，您需要为事件记录的 select() 函数将其设置为 FALSE，它将限制事件记录添加反引号，而且您正在使用 COUNT( comment.id) 并且在您的查询中没有名为 comment 的表我想您需要从 photo_comment 中计算 id，我使用了短别名表格，这样它将更具可读性

public function getTotalCommentsPerMonth() {

    $this->db->select('MONTHNAME(pc.`created_on`) AS `month_name`, 
                      COUNT(pc.`comment_id`)  AS `comment_count`',FALSE);
    $this->db->from('photo_comment pc');        
    $this->db->join('photo p', 'p.id = pc.photo_id');
    $this->db->where('p.userId', $this->auth->userid());
    return $this->db->get()->result_array();
}

Note: Using Aggregate Fucntions without grouping them will result in a single row ,if you need the comment count for all photos of user you should group by them pc.photo_id in active record you can do so

public function getTotalCommentsPerMonth() {

    $this->db->select('MONTHNAME(pc.`created_on`) AS `month_name`, 
                      COUNT(pc.`comment_id`)  AS `comment_count`',FALSE);
    $this->db->from('photo_comment pc');        
    $this->db->join('photo p', 'p.id = pc.photo_id');
    $this->db->where('p.userId', $this->auth->userid());
    $this->db->group_by("pc.photo_id");
    return $this->db->get()->result_array();
}