mysql - codeigniter on 和 where 组合

Question

表user 左连接表personal_record 然后连接表activity。

---

用户 表:id ...

personal_record表:user.id, activity.id, confirm_time...

activity: id, time, delete_time...

注意:时间是指花在这个事件上的小时数，8 等等。

---

在user加入personal_record之前，部分persanoal_record行需要过滤为personal_record只有在confirm_time时才有效已设置。personal_reocrd 仅包含 activity_id，activity 其中包含事件的时间需要加入以总结(时间)一个人花费。

通过更新 activity.delete_time 列删除事件。

这是我的代码。

更新(差不多解决了):($data_begin过滤记录范围)

$result = $this->db->select('user.*, ifnull(sum(ac.time), 0) as time')
                   ->from('user')
                   ->join('(select * from personal_record where personal_record.confirm_time is not null) as pr','user.id = pr.person', 'left')
                   ->join('(select * from activity where activity.delete_time is null and activity.create_time >"'.$date_begin.'")as ac','ac.id = pr.activity', 'left')
                   ->group_by('user.id')
                   ->order_by('time', 'desc')
                   ->get()
                   ->result();

Answer 1

$this->db->select('user.*,personal_record.*,ifnull(sum(activity.time), 0) as time');
$this->db->from('user');
$this->db->join('personal_record','personal_record.person = user.id');
$this->db->join('activity','activity.id = personal_record.activity');
$this->db->where('activity.delete_time',null);
$this->db->or_where('personal_record.confirm_time !=', null);
$this->db->order_by('time', 'asc');
$this->db->group_by('user.id');
$data = $this->db->get()->result();
return $data;