我的一个联接语句在JS折线图的CI中按天获取输出时遇到问题。我在这里做了大量的研究,但似乎什么都做不到。
这是我的型号
function get_all ( $start_date, $end_date)
{
$ret = array();
$this->db->select('DATE(created_at) AS DAY, COUNT(*) AS COUNT');
$this->db->from('table1');
$this->db->where('item_id =', 5);
$this->db->where('created_at >=', $start_date);
$this->db->where('created_at <=', $end_date);
$this->db->join('table2', 'table2.id = table1.part_id AND table2.stock_id = 1', 'left' );
$this->db->group_by('DAY(created_at)');
$query = $this->db->get();
foreach ($query->result() as $row)
{
$ret[$row->DAY] = intval($row->COUNT);
}
return $ret;
}
可以对加入进行评论,而且效果很好。我已经尝试了几种不同的修改,但无论我尝试哪种方式,数据都无法加载。
非常感谢您的真知灼见!谢谢
试试这个:
未经测试。
function get_all ( $start_date, $end_date)
{
$ret = array();
$this->db->select('table1.DATE(created_at) AS DAY, COUNT(*) AS COUNT');
$this->db->from('table1');
$this->db->join('table2', 'table2.id = table1.part_id', 'left');
$this->db->where('table1.item_id =', 5);
$this->db->where('table1.created_at >=', $start_date);
$this->db->where('table1.created_at <=', $end_date);
$this->db->where('table2.stock_id =', 1);
$this->db->group_by('table1.DAY(created_at)');
$query = $this->db->get();
foreach ($query->result() as $row)
{
$ret[$row->DAY] = intval($row->COUNT);
}
return $ret;
}
认为这个问题已经解决了,我还是让它工作。。。对于遇到类似问题的人:
function get_all ( $start_date, $end_date)
{
$ret = array();
$this->db->select('DATE(table2.created_at) AS DAY, COUNT(*) AS COUNT');
$this->db->from('table1');
$this->db->join('table2', 'table2.part_id = table1.id AND table2.created_at >= ''' . $start_date . ''' AND table2.created_at <= ''' . $end_date . ''' AND table2.item_id = 5', 'left');
$this->db->where('table1.stock_id =', 1);
$this->db->group_by('DAY(table2.created_at)');
$query = $this->db->get();
foreach ($query->result() as $row)
{
$ret[$row->DAY] = intval($row->COUNT);
}
return $ret;
}