Join column is handled incorrectly when nesting is in place and nesting prefix overlaps with attribute name

[waza-ari]

Describe the bug or question Consider the following models:

class Ability(Base, UUIDMixin, TimestampMixin, SoftDeleteMixin):
    __tablename__ = "abilities"

    name: Mapped[str] = mapped_column(nullable=False)
    strength: Mapped[int] = mapped_column(nullable=False)
    heroes: Mapped[list["Hero"]] = relationship(back_populates="ability")

class Hero(Base, UUIDMixin, TimestampMixin, SoftDeleteMixin):
    __tablename__ = "heroes"

    name: Mapped[str] = mapped_column(nullable=False)
    ability_id: Mapped[int] = mapped_column(ForeignKey("abilities.id"))
    ability: Mapped["Ability"] = relationship(back_populates="heroes")

When running a nested join query like this:

heroes = await crud_hero.get_multi_joined(db, join_model=Ability, join_prefix="ability_", nest_joins=True)

The returned values are:

{
    "data": [
        {
            "name": "Diana",
            "ability": {
                "id": UUID("6e52176e-8a92-4a8d-b0b3-1fcd55acc666"),
                "name": "Superstrength",
                "strength": 10,
                "id_1": UUID("6e52176e-8a92-4a8d-b0b3-1fcd55acc666"),
            },
            "id": UUID("8212bccb-ce20-489a-a675-45772ad60eb8"),
        },
    ],
    "total_count": 2,
}

Note how the nested set contains the id twice, once as id and once as id_1 and note that the original field of Hero called ability_id is missing, which is failing when returning the data as the Pydantic model will complain about a missing field.

When setting the join_prefix to something stupid, the attribute is in place correctly but obviously that also changes the name of the nested dict:

heroes = await crud_hero.get_multi_joined(db, join_model=Ability, join_prefix="xyz_", nest_joins=True)

{
    "data": [
        {
            "name": "Diana",
            "ability_id": UUID("6e52176e-8a92-4a8d-b0b3-1fcd55acc666"),
            "id": UUID("8212bccb-ce20-489a-a675-45772ad60eb8"),
            "xyz": {
                "name": "Superstrength",
                "strength": 10,
                "id": UUID("6e52176e-8a92-4a8d-b0b3-1fcd55acc666"),
            },
        },
    ],
    "total_count": 2,
}

[igorbenav]

Nice catch, thanks! Will be fixed

[igorbenav]

This one is a bit more subtle than the other one related to _nest_join_data, so I'll make sure to fix it myself

def _nest_join_data(
    data: dict[str, Any], join_definitions: list[JoinConfig]
) -> dict[str, Any]:
    nested_data: dict = {}
    for key, value in data.items():
        nested = False
        for join in join_definitions:
            if join.join_prefix and key.startswith(join.join_prefix):
                nested_key = join.join_prefix.rstrip("_")
                nested_field = key[len(join.join_prefix) :]
                if nested_key not in nested_data:
                    nested_data[nested_key] = {}
                nested_data[nested_key][nested_field] = value
                nested = True
                break
        if not nested:
            nested_data[key] = value
    return nested_data