PHQL replaces field names in where

[sergeyklay]

From @FaimMedia on January 6, 2016 15:37

I've created the following query using the query builder:

$query = $this
    ->modelsManager
    ->createBuilder()
    ->columns([
        'match.name name',
        'zone.zone zone',
        'surcharge.id',
        'surcharge.amount',
        'surcharge.percent',
        'match.code',
        'match.text',
        'carrier.code carrier_code'
    ])
    ->addFrom('Tariff\\Surcharge', 'surcharge')
    ->leftJoin('\\Company', 'surcharge.company_id = company.id', 'company')
    ->leftJoin('\\Zone\\Zone', 'surcharge.zone_id = zone.id', 'zone')
    ->leftJoin('\\Zone\\Info', 'zi.zone_id = zone.id', 'zi')
    ->leftJoin('\\Carrier', 'surcharge.carrier_id = carrier.id', 'carrier')
    ->leftJoin(
        'Tariff\\SurchargeMatch',
        'surcharge.tariff_surcharge_match_id = match.id',
        'match'
    )
    ->where('match.name LIKE :name:', ['name' => 'test%'])
    ->groupBy('surcharge.id')
    ->getQuery()
    ->execute();

Probably because I have specified name as an alias for match.name PHQL thinks it can just use name in the WHERE clause and removed the table prefix, which results in executing this query:

    SELECT `match`.`name` AS `name`,
           `zone`.`zone` AS `zone`,
           `surcharge`.`id` AS `id`,
           `surcharge`.`amount` AS `amount`,
           `surcharge`.`percent` AS `percent`,
           `match`.`code` AS `code`,
           `match`.`text` AS `text`,
           `carrier`.`code` AS `carrier_code`
    FROM `tariff_surcharge` AS `surcharge`
    LEFT JOIN `company` AS `company`
        ON `surcharge`.`company_id` = `company`.`id`
    LEFT JOIN `zone` AS `zone`
        ON `surcharge`.`zone_id` = `zone`.`id`
    LEFT JOIN `zone_info` AS `zi`
        ON `zi`.`zone_id` = `zone`.`id`
    LEFT JOIN `carrier` AS `carrier`
        ON `surcharge`.`carrier_id` = `carrier`.`id`
    LEFT JOIN `tariff_surcharge_match` AS `match`
        ON `surcharge`.`tariff_surcharge_match_id` = `match`.`id`
    WHERE `name` LIKE '%test'
    GROUP BY `surcharge`.`id`

Note the where clause. This shouldn't be a problem if you have non-ambiguous column names, but I do, resulting in an

Column 'name' in where clause is ambiguous

error.

I could just rename the alias to an unique name and it doesn't occur anymore. However I'm questioning if this is by design?

Gr. Tim

Copied from original issue: phalcon-orphanage/docs#765

[sergeyklay]

Without alias 'match.name name' it works fine?

[sergeyklay]

From @FaimMedia on January 7, 2016 11:10

Indeed, if I don't use an alias, but just use match.name PHQL automatically translates this field to the alias name and doesn't replace the where clause anymore.

[sergeyklay]

From @adamdama on October 18, 2016 9:51

I have just run into this issue as well. Surely if I specify the aliased in the where they should not be rewritten?

I am trying to use a JOIN to populate my model from two tables so changing the name of the alias I select will break the population.

[longfeiRen]

I also encountered the same problem， PHQL replaces field sourceno in where .
ERROR:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'sourceno' in where clause is ambiguous

PHQL:

SELECT
	warehouse.quantity AS [ quantity ], 
	warehouse.updatetime AS [ createtime ],
	warehouse.operationuserid AS [ operationuserid ], 
	warehouse.type AS [ type ], 
	warehouse.sourceno AS [ sourceno ], 
	warehouse.remainnum AS [ remainnum ], 
	merchandise. NAME AS [ NAME ], 
	merchandise.temperaturezone AS [ temperaturezone ], 
	merchandise.unit AS [ unit ], 
	warehouse.qit AS [ qit ], 
	delivery.companyid AS [ companyid ], 
	delivery.deliverystatus AS [ deliverystatus ]
FROM
	[ Warehouse ] AS [ warehouse ]
INNER JOIN [ Merchandise ] AS [ merchandise ] ON merchandise.merchandiseid = warehouse.merchandiseid
LEFT JOIN [ Delivery ] AS [ delivery ] ON delivery.sourceno = warehouse.sourceno
WHERE
	warehouse.merchandiseid = : merchandiseid :
AND warehouse.sourceno = : sourceno :
ORDER BY
	updatetime DESC
LIMIT : APL0 : OFFSET : APL1 :

sqlStatement:

SELECT
	`warehouse`.`quantity` AS `quantity` ,
	`warehouse`.`updatetime` AS `createtime` ,
	`warehouse`.`operationuserid` AS `operationuserid` ,
	`warehouse`.`type` AS `type` ,
	`warehouse`.`sourceno` AS `sourceno` ,
	`warehouse`.`remainnum` AS `remainnum` ,
	`merchandise`.`name` AS `name` ,
	`merchandise`.`temperaturezone` AS `temperaturezone` ,
	`merchandise`.`unit` AS `unit` ,
	`warehouse`.`qit` AS `qit` ,
	`delivery`.`companyid` AS `companyid` ,
	`delivery`.`deliverystatus` AS `deliverystatus`
FROM
	`warehouse` AS `warehouse`
INNER JOIN `merchandise` AS `merchandise` ON `merchandise`.`merchandiseid` = `warehouse`.`merchandiseid`
LEFT JOIN `delivery` AS `delivery` ON `delivery`.`sourceno` = `warehouse`.`sourceno`
WHERE
	`warehouse`.`merchandiseid` = : merchandiseid
AND `sourceno` = : sourceno
ORDER BY
	`warehouse`.`updatetime` DESC
LIMIT : APL0 OFFSET : APL1

[sergeyklay]

Fixed in the 3.2.x branch. Feel free to open a new issue if the problem appears again. Thank you for contributing.