Problem with Builder Query

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.



4.4k

I copied your query builder code exactly and I got the following query generated:

 SELECT date(ue.created_at) as created_at, u.sex, COUNT(ue.user_id) as quant FROM [Wifire\Models\Establishment] AS [e] INNER JOIN [Wifire\Models\UserEstablishment] AS [ue] ON e.id = ue.establishment_id INNER JOIN [Wifire\Models\User] AS [u] ON u.id = ue.user_id WHERE ((e.establishment_id = :establishment_id:) AND (ue.created_at >= :initialPeriod: AND ue.created_at <= :finalPeriod:)) AND (e.city_id = :city_id:) GROUP BY 1, 2

Is that the query you were expecting it to build?



2.5k

Whats is your version? 2.0 or 13.4?



2.5k
edited Apr '15

Testing here i saw that the problem is generated when i use this alias "date(ue.created_at) as created_at", if I change this "create_at" to other thing, it works.



4.4k

Ah ok. Glad you found the issue and solution :-)