Помогите построить запрос на основе criteria

Общие вопросы по использованию второй версии фреймворка. Если не знаете как что-то сделать и это про Yii 2, вам сюда.
Ответить
skinysmn
Сообщения: 2
Зарегистрирован: 2018.01.25, 17:21

Помогите построить запрос на основе criteria

Сообщение skinysmn »

Есть сложный запрос к базе осуществленный через criteria в YII1
сломал готову как посторить аналогичный средствами ActiveRecord

public function calculateRating($quizId = null, $timeFrom = null, $timeTill = null)
{
//echo '>>> '.$quizId;

$criteria = new CDbCriteria();
$criteria->join =
'LEFT JOIN `QuizAnswer` AS `answers` ON `answers`.`userId` = `t`.`userId`
LEFT JOIN `QuizQuestion` AS `rightQuestions` ON
`rightQuestions`.`questionId` = `answers`.`questionId` AND `rightQuestions`.`winnerVariantId` = `answers`.`variantId`
'.(!empty($quizId)?(' AND `rightQuestions`.`quizId` = '.$quizId.' '):'').'
'.(!empty($timeFrom)?(' AND `rightQuestions`.`timeCreated` >= "'.$timeFrom.'" '):'').'
'.(!empty($timeTill)?(' AND `rightQuestions`.`timeCreated` <= "'.$timeTill.'" '):'').'
LEFT JOIN (
SELECT `threesDates`.`userId` AS userId, SUM(`threesDates`.`threes`) AS threes FROM ( #TODO: change sum to count not to to $threes / 3
SELECT COUNT(DISTINCT `QuizQuestion`.`questionId`) `threes`, `User`.`userId`
FROM `User`
LEFT JOIN `QuizAnswer` ON `QuizAnswer`.`userId` = `User`.`userId`
LEFT JOIN `QuizQuestion` ON
`QuizQuestion`.`questionId` = `QuizAnswer`.`questionId` AND `QuizQuestion`.`winnerVariantId` = `QuizAnswer`.`variantId`
'.(!empty($quizId)?(' AND `QuizQuestion`.`quizId` = '.$quizId.' '):'').'
'.(!empty($timeFrom)?(' AND `QuizQuestion`.`timeCreated` >= "'.$timeFrom.'" '):'').'
'.(!empty($timeTill)?(' AND `QuizQuestion`.`timeCreated` <= "'.$timeTill.'" '):'').'
GROUP BY DATE(`QuizQuestion`.`timeCreated`), `User`.`userId`
HAVING `threes` >= 3
) as `threesDates` GROUP BY `threesDates`.`userId`
) AS `threesUsers` ON `threesUsers`.`userId` = `t`.`userId`';

$criteria->select = array(
'`t`.*',
'COUNT(DISTINCT `rightQuestions`.`questionId`) AS points',
'`threesUsers`.`threes` AS threes'
);

$criteria->group = '`t`.`userId`';

$this->getDbCriteria()->mergeWith($criteria);

return $this;
}
Ответить