Как добавлять OFFSET? Возможно он должен автоматически добавляться?
Основная страница без параметров пагинации
Код: Выделить всё
http://3.com/web/index.php?r=doctor%2Fview&id=1
Код: Выделить всё
SQL Request
SELECT DISTINCT (patient.id) as patient_id,
capitation,
first_name,
last_name,
status,
denial_reason,
date
FROM Patient
LEFT JOIN `payment`
ON `payment`.`patient_id` = `patient`.`id`
WHERE `patient`.`doctor_id`= '1'
AND date BETWEEN '2019-02-01' AND '2019-02-28' LIMIT 2
date=2019-Feb - параметр для SQL запроса
Код: Выделить всё
http://3.com/web/index.php?r=doctor%2Fview&id=1&date=2019-Feb&page=2&per-page=2
Код: Выделить всё
SELECT DISTINCT (patient.id) as patient_id,
capitation,
first_name,
last_name,
status,
denial_reason,
date
FROM Patient
LEFT JOIN `payment`
ON `payment`.`patient_id` = `patient`.`id`
WHERE `patient`.`doctor_id`= '1'
AND date BETWEEN '2019-02-01' AND '2019-02-28' LIMIT 2
Код: Выделить всё
http://3.com/web/index.php?r=doctor%2Fview&id=1&date=2019-Feb&page=1&per-page=2
Код: Выделить всё
SELECT DISTINCT (patient.id) as patient_id,
capitation,
first_name,
last_name,
status,
denial_reason,
date
FROM Patient
LEFT JOIN `payment`
ON `payment`.`patient_id` = `patient`.`id`
WHERE `patient`.`doctor_id`= '1'
AND date BETWEEN '2019-02-01' AND '2019-02-28' LIMIT 2
Код: Выделить всё
$count = Yii::$app->db->createCommand('
SELECT COUNT(*)
FROM Patient, Payment
WHERE Patient.doctor_id=:doctor_id
AND `payment`.`patient_id` = `patient`.`id`
AND date BETWEEN :start AND :end
',
[':doctor_id' => 1, ':start' => $form->getBeginDate(), ':end' => $form->getEndDate()])->queryScalar();
$provider = new SqlDataProvider([
'sql' => 'SELECT DISTINCT (patient.id) as patient_id,
capitation,
first_name,
last_name,
status,
denial_reason,
date
FROM Patient
LEFT JOIN `payment`
ON `payment`.`patient_id` = `patient`.`id`
WHERE `patient`.`doctor_id`= :doctor_id
AND date BETWEEN :start AND :end ',
'params' => [':doctor_id' => $id, ':start' => $form->getBeginDate(), ':end' => $form->getEndDate(),],
'totalCount' => $count,
'pagination' => [
'pageSize' => 2,
'params'=>[
'id' => $id,
'date' => $form->getMonth(),
],
],
]);