Object get serialized with the wrong id when using join

[thedomeffm]

Package version

21.6.1

with postgres:16.6-alpine

Describe the bug

I have a Skills Table and a SkillsTranslation Table.

When joining the SkillTranslations and returning them back (paginated) the Skill get the wrong id (the id from the SkillTranslation, not the Skill).

const queryBuilder = Skill.query()
    .join(SkillTranslation.table, (skillTranslationsLeftJoinQuery) => {
        skillTranslationsLeftJoinQuery
            .on(`${Skill.table}.id`, '=', `${SkillTranslation.table}.skill_id`)
            .andOnVal(`${SkillTranslation.table}.language_id`, '=', userSetting.languageId);
    })
   .orderByRaw(`similarity(${SkillTranslation.table}.name, ?) DESC`, [trimmedTerm])
   .paginate(pagination.page, pagination.perPage)

I guess this bug can happen on all columns/properties and is not directly related to the id.

This is happening because the real sql query that get generated does not set correct aliases for columns that are expected.

The query:

select *
from "skill_skills"
         inner join "skill_skill_translations" on "skill_skills"."id" = "skill_skill_translations"."skill_id" and
                                                  "skill_skill_translations"."language_id" = ?
order by similarity(skill_skill_translations.name, ?) DESC

From here lucid takes the "id" from the "skill_skill_translations.id" and not "skill_skill.id". I guess it is happening because the columns get no alias.

I am coming from PHP and Doctrine and I've always wondered me why Doctrine set explicit aliases for all select columns.
This is how doctrine roughly would do it:

select skill_skills.id as skill_id,
       skill_skills.name as skill_name 
-- ...

Now running into this problem I understand why the Team behind doctrine has decided to do it. So the ORM always exactly know which "id" column is the correct one.

---

Temporary solution

set aliases manually to fix lucid not taking the wrong "id" column:

const queryBuilder = Skill.query()
    .select({
        id: `${Skill.table}.id`,
        name: `${SkillTranslation.table}.name`,
        createdAt: `${Skill.table}.created_at`,
        updatedAt: `${Skill.table}.updated_at`,
        translatedName: `${SkillTranslation.table}.name`,
    })
   .orderByRaw(`similarity(${SkillTranslation.table}.name, ?) DESC`, [trimmedTerm])
   .paginate(pagination.page, pagination.perPage)

So the query looks now like this:

select "skill_skills"."id"               as "id",
       "skill_skill_translations"."name" as "name",
       "skill_skills"."created_at"       as "createdAt",
       "skill_skills"."updated_at"       as "updatedAt",
       "skill_skill_translations"."name" as "translatedName"
from "skill_skills"
         inner join "skill_skill_translations" on "skill_skills"."id" = "skill_skill_translations"."skill_id" and
                                                  "skill_skill_translations"."language_id" = ?
order by similarity(skill_skill_translations.name, ?) DESC

Reproduction repo

No response

[thetutlage]

Yeah, this seems to be a legit issue that needs to be handled with a bit more care and has to be reflected in the entire codebase of Lucid.