2

Table user left join table personal_record then join table activity.

user table:id ...

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

activity: id, time, delete_time...

note: time means hours spent on this activity, 8 etc.

Before user join personal_record, some of persanoal_record rows need to be filtered as personal_record only valid when confirm_time is set.personal_reocrd only contains activity_id, activity which contains activities' time need to join to sum (time) a person spend.

As a activity is deleted by updating activity.delete_time column.

Here is my code.

Update(Almost solved): ($data_begin filter the range of records)

$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();
teik
  • 508
  • 4
  • 26
  • Remove where condition and add it into on condition like this ->join('activity','activity.id = personal_record.activity and activity.delete_time=NULL ', 'left', false) @teek – JYoThI Jun 16 '17 at 04:23
  • You should look into this question, maybe it help. [example](https://stackoverflow.com/questions/11056303/combining-mysql-and-or-queries-in-codeigniter) – always-a-learner Jun 17 '17 at 18:13

1 Answers1

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;
Anurag Biradar
  • 94
  • 1
  • 7