How to update one table using another table in CakePHP 3

Question

I have two tables like status_report and holiday. I want to update status_report table using holiday table. I want to generate below SQL using CakePHP.

UPDATE AttendanceReports s, holiday h
SET s.Status = h.description
WHERE s.work_date =  h.work_date and
      h.work_date between '2015-05-05' and '2015-12-31';

How to write this query in CakePHP? (I'm using CakePHP 3.x)

I have tried the following but I don't know how to set status field using another table's date.

$this->loadModel('Usermgmt.AttendanceReports');
$dateStart = date('Y-m-01') ;
$dateEnd = date('Y-m-t');
$q = $this->AttendanceReports->query()
     ->update('AttendanceReports s, Holidays h')
     ->set(['s.status = h.description'])
     ->where(['s.work_date =  h.work_date','h.work_date BETWEEN :start AND :end'])
     ->bind(':start', $dateStart)
     ->bind(':end', $dateEnd)
     ->execute();

this will generate right output like this

UPDATE AttendanceReports s, Holidays h 
SET s.status = h.description 
WHERE (s.work_date = h.work_date AND 
       h.work_date BETWEEN '2016-01-01' AND '2016-01-31')

But it gives error like below:

Error: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'ollo_hrm.attendancereports' doesn't exist

$offday = $this->loadModel('StatusReports'); $dateStart=''; $dateStart =''; $query_offday = $offday->query(); $query_offday->update() ->set(['status' => $status]) ->where(['work_date BETWEEN :start AND :end']) ->bind(':start', $dateStart) ->bind(':end', $dateStart) ->execute(); i am trying this one but i dont know how to set status field using another table data. — Faisal
do you actually have a table named AttendanceReports? Or it is attendance_reports — arilia
yeah table name is attendance_reports. now its working. Thank you so much @arilia — Faisal

arilia arilia · Accepted Answer · 2016-01-05T07:24:49

I can't try the code at the moment but something like this should work

$q = $this->StatusReports->query()
    ->update('status_report s, holiday h')
    ->set(['s.Status = h.Ocassion'])
    ->where(function ($exp, $q) {
            return $exp->add('s.Workdate =  h.holiday_date')
            ->between('h.holiday_date','2015-05-05', '2015-12-31');
        }
    );

How to update one table using another table in CakePHP 3

1 Answers