I'm getting a error of SQL because Builder is removing my alias: My code:
$builder = new Builder;
$builder->addFrom('Wifire\Models\Establishment', 'e')
->columns('date(ue.created_at) as created_at, u.sex, COUNT(ue.user_id) as quant')
->innerJoin('Wifire\Models\UserEstablishment', 'e.id = ue.establishment_id', 'ue')
->innerJoin('Wifire\Models\User', 'u.id = ue.user_id', 'u');
if (isset($params['establishment_id']) && $params['establishment_id']) {
$builder->andWhere('e.establishment_id = :establishment_id:', array('establishment_id' => $params['establishment_id']));
}
if (isset($params['initialPeriod']) && $params['initialPeriod']) {
$builder->andWhere('ue.created_at >= :initialPeriod: AND ue.created_at <= :finalPeriod:',
array('initialPeriod' => $params['initialPeriod'] . " 00:00:00", 'finalPeriod' => $params['finalPeriod'] . " 23:59:59"));
}
if (isset($params['city_id']) && $params['city_id']) {
$builder->andWhere('e.city_id = :city_id:', array('city_id' => $params['city_id']));
}
$builder->groupBy(array('1', '2'));
SQL generated:
SELECT date(`ue`.`created_at`), `u`.`sex`, COUNT(`ue`.`user_id`) FROM `establishments` AS `e` INNER JOIN `user_establishments` AS `ue` ON `e`.`id` = `ue`.`establishment_id` INNER JOIN `users` AS `u` ON `u`.`id` = `ue`.`user_id` WHERE `created_at` >= :initialPeriod AND `created_at` <= :finalPeriod GROUP BY 1, 2
How my alias "ue" was removed, I have a error "1052 Column 'created_at' in where clause is ambiguous" cause Establishment and UserEstablishments has the column created_at.