I have created a query that causes an exception. The excpetion is avoidable if I use findBySql. But I don’t understand why. Is it a bug?
The Query that produces the exception is (one table with self inner join on 4 columns):
$subQuery = (new Query())
->select(['item_id', 'property_definition_id', 'localization_id', 'MAX(version) AS version'])
->from([self::tableName()])
->groupBy(['item_id', 'property_definition_id', 'localization_id'])
->filterWhere(['item_id' => $itemId, 'localization_id' => $languageId, 'property_definition_id' => $definitionId]);
$on = 'a.item_id = b.item_id
AND a.property_definition_id = b.property_definition_id
AND a.localization_id = b.localization_id
AND a.version = b.version';
return (new Query())
->select(['b.item_id', 'b.property_definition_id', 'b.localization_id', 'b.version', 'b.value'])
->from(['b' => self::tableName()])
->innerJoin(['a' => $subQuery], $on);
Using this query produces this exception:
[color="#000080"][font=“Courier New”]SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘item_id’ in ‘field list’
The SQL being executed was: SELECT b.item_id, b.property_definition_id, b.localization_id, b.version, b.value FROM localized_property_value b INNER JOIN (SELECT item_id, property_definition_id, localization_id, MAX(version) AS version FROM localized_property_value WHERE item_id=‘16’ GROUP BY item_id, property_definition_id, localization_id) a ON a.item_id = b.item_id AND a.property_definition_id = b.property_definition_id AND a.localization_id = b.localization_id AND a.version = b.version[/font][/color]
Now, when I use findBySql and copy&paste the sql in the exception all works as it expected:
return self::findBySql('SELECT `b`.`item_id`, `b`.`property_definition_id`, `b`.`localization_id`, `b`.`version`, `b`.`value`
FROM `localized_property_value` `b`
INNER JOIN (
SELECT `item_id`, `property_definition_id`, `localization_id`, MAX(VERSION) AS VERSION
FROM `localized_property_value`
WHERE `item_id`=16
GROUP BY `item_id`, `property_definition_id`, `localization_id`
) `a`
ON a.item_id = b.item_id
AND a.property_definition_id = b.property_definition_id
AND a.localization_id = b.localization_id
AND a.version = b.version'
);
Does it have anything to do with the inner join? Am I doing anything wrong? A bug?
Actually I tried to do build a query with sub-query but wasn’t successful with the multiple column relations. That’s why I built the query my self.
Thanks for your help.