sqlite ambiguous column name regression #1287
Description
objection v1.6.2 introduces a breaking change resulting in sqlite throwing an error saying ambiguous column name. I've reproduced this with the reproduction template as simply changing the query as follows:
const jennifer = await Person.query().debug()
.joinEager()
.findOne({ firstName: 'Jennifer' })
.eager('pets')
.orderBy('id', 'asc')
To make the code work with v1.6.2 you need to change .orderBy('id', 'asc') to .orderBy('person.id', 'asc').
v1.6.1 produces the following sql:
SELECT
`Person`.`id` AS `id`,
`Person`.`parentId` AS `parentId`,
`Person`.`firstName` AS `firstName`,
`Person`.`lastName` AS `lastName`,
`Person`.`age` AS `age`,
`Person`.`address` AS `address`,
`pets`.`id` AS `pets:id`,
`pets`.`ownerId` AS `pets:ownerId`,
`pets`.`name` AS `pets:name`,
`pets`.`species` AS `pets:species`
FROM
`Person`
LEFT JOIN
`Animal` AS `pets` ON `pets`.`ownerId` = `Person`.`id`
WHERE
`firstName` = ?
ORDER BY `id` ASC
v1.6.2 produces the following sql:
SELECT
`Person`.`id`,
`Person`.`parentId`,
`Person`.`firstName`,
`Person`.`lastName`,
`Person`.`age`,
`Person`.`address`,
`pets`.`id` AS `pets:id`,
`pets`.`ownerId` AS `pets:ownerId`,
`pets`.`name` AS `pets:name`,
`pets`.`species` AS `pets:species`
FROM
`Person`
LEFT JOIN
`Animal` AS `pets` ON `pets`.`ownerId` = `Person`.`id`
WHERE
`firstName` = ''
ORDER BY `person`.`id` ASC
Note this is only broken on sqlite, mysql does not complain about the sql produced by v1.6.2