Требуется помощь, сам запрос:
[sql]
SELECT DISTINCT
agencies.id,
agencies.name as nameAgency,
CONCAT(’/site.ru/office/agencies/’,agencies.id) AS urlAgency,
IF( agencies.enabled=1,“Нет”,null) as ‘blockedAgency’,
IF( suppliersVisaSupport.support=2,“Да”,null) as ‘supportVisa’,
count(DISTINCT IF( t.status=4,t.orderId,null)) as ‘numberOrdersCancelled’,
count(DISTINCT IF( t.status=3,t.orderId,null)) as ‘numberOrdersRejected’,
count(DISTINCT IF( t.status=8,t.orderId,null)) as ‘numberOrdersConfirmed’,
count(DISTINCT IF( t.status=6,t.orderId,null)) as ‘numberOrdersResponse’,
count(DISTINCT t.orderId) as ‘numberOrdersTotal’,
count(DISTINCT IF( t.status=4,t.orderId,null)) / count(DISTINCT t.orderId) * 100 as ‘procentCancelled’
FROM bookings t
INNER JOIN agencies ON t.agencyId = agencies.id
INNER JOIN suppliersVisaSupport ON suppliersVisaSupport.supplierId = t.supplierId
INNER JOIN dicStatuses ON t.status = dicStatuses.id
WHERE
agencies.enabled = 1 AND
suppliersVisaSupport.support = 2 AND
FROM_UNIXTIME(t.createTime) BETWEEN ‘2016-02-01’ and ‘2016-02-02’
GROUP BY agencies.name
HAVING numberOrdersTotal > 0[/sql]
То что получилось у меня:
[sql] // создаем экземпляр класса CDbCriteria
$criteria = new CDbCriteria;
// выбираемые все столбцы из всех таблиц, участвующих в запросе
$criteria->select = '
agencies.id,,
agencies.name as nameAgency,
CONCAT(\'/site.ru/office/agencies/\',agencies.id) AS urlAgency,
IF( agencies.enabled=1,"Нет",null) as \'blockedAgency\',
IF( suppliersVisaSupport.support=2,"Да",null) as \'supportVisa\',
count(DISTINCT IF( t.status=4,t.orderId,null)) as \'numberOrdersCancelled\',
count(DISTINCT IF( t.status=3,t.orderId,null)) as \'numberOrdersRejected\',
count(DISTINCT IF( t.status=8,t.orderId,null)) as \'numberOrdersConfirmed\',
count(DISTINCT IF( t.status=6,t.orderId,null)) as \'numberOrdersResponse\',
count(DISTINCT t.orderId) as \'numberOrdersTotal\',
count(DISTINCT IF( t.status=4,t.orderId,null)) / count(DISTINCT t.orderId) * 100 as \'procentCancelled\'
';
// выбираем только неповторяющиеся строки данных
$criteria->distinct = true;
$criteria->join = 'INNER JOIN agencies ON t.agencyId = agencies.id '
. 'INNER JOIN suppliersVisaSupport ON suppliersVisaSupport.supplierId = t.supplierId '
. 'INNER JOIN dicStatuses ON t.status = dicStatuses.id';
$criteria->condition = 'agencies.enabled = 1 AND
suppliersVisaSupport.support = 2 AND
FROM_UNIXTIME(t.createTime) BETWEEN \'2016-02-01\' and \'2016-02-02\'
';
// группируем
$criteria->group = 'agencies.name';
// условие для GROUP BY
$criteria->having = 'numberOrdersTotal > 0';
// сортировки результатов запроса
$criteria->order = 'procentCancelled DESC, agencies.name ASC';
$criteria->limit = 1;[/sql]
Но я так понимаю, что неверно записал, т.к. yii собирает запрос следующим образом:
[sql]
SELECT COUNT(*) FROM (SELECT DISTINCT
agencies.id,
agencies.name as nameAgency,
CONCAT(’/site.ru/office/agencies/’,agencies.id) AS urlAgency,
IF( agencies.enabled=1,“Нет”,null) as ‘blockedAgency’,
IF( suppliersVisaSupport.support=2,“Да”,null) as ‘supportVisa’,
count(DISTINCT IF( t.status=4,t.orderId,null)) as ‘numberOrdersCancelled’,
count(DISTINCT IF( t.status=3,t.orderId,null)) as ‘numberOrdersRejected’,
count(DISTINCT IF( t.status=8,t.orderId,null)) as ‘numberOrdersConfirmed’,
count(DISTINCT IF( t.status=6,t.orderId,null)) as ‘numberOrdersResponse’,
count(DISTINCT t.orderId) as ‘numberOrdersTotal’,
count(DISTINCT IF( t.status=4,t.orderId,null)) / count(DISTINCT t.orderId) * 100 as ‘procentCancelled’
FROM bookings t INNER JOIN agencies ON t.agencyId = agencies.id INNER JOIN suppliersVisaSupport ON suppliersVisaSupport.supplierId = t.supplierId INNER JOIN dicStatuses ON t.status = dicStatuses.id WHERE agencies.enabled = 1 AND
suppliersVisaSupport.support = 2 AND
FROM_UNIXTIME(t.createTime) BETWEEN ‘2016-02-01’ and ‘2016-02-02’
GROUP BY agencies.name HAVING numberOrdersTotal > 0) sq [/sql]