Multiple `#[Eager]` `#[BelongsTo]` relations to same table produce duplicate JOINs with wrong aliases

[radoslav-grencik]

Tempest version

3.7

PHP version

8.5

Operating system

Linux, Windows

Description

When a model has multiple #[Eager] #[BelongsTo] relations referencing the same table, the query builder generates invalid SQL with duplicate JOINs using the same alias.

Steps to Reproduce

Create a model with multiple #[BelongsTo] relations to the same table, both marked with #[Eager], then query for instances:

// Model definition
#[Table('roles')]
class RoleBrowse
{
    public PrimaryKey $id;

    public string $code;

    #[Eager]
    #[BelongsTo('roles.created_by', 'users.id')]
    public ?UserLookup $created_by = null;

    #[Eager]
    #[BelongsTo('roles.updated_by', 'users.id')]
    public ?UserLookup $updated_by = null;
}

// Query
$roles = query(RoleBrowse::class)->select()->whereIn('code', array_column(SystemRole::cases(), 'value'))->all();

Expected Output

SELECT roles.id AS `roles.id`, ...,
    created_by_user.id AS `created_by.id`, created_by_user.name AS `created_by.name`,
    updated_by_user.id AS `updated_by.id`, updated_by_user.name AS `updated_by.name`
FROM `roles`
LEFT JOIN users AS created_by_user ON roles.created_by = created_by_user.id
LEFT JOIN users AS updated_by_user ON roles.updated_by = updated_by_user.id
WHERE `roles`.`code` IN ('admin','player')

Actual Output

SELECT roles.id AS `roles.id`, ...,
    users.id AS `created_by.id`, users.name AS `created_by.name`,
    users.id AS `updated_by.id`, users.name AS `updated_by.name`
FROM `roles`
LEFT JOIN users ON roles.created_by = users.id  -- Same alias "users" used twice
LEFT JOIN users ON roles.updated_by = users.id  -- Duplicate JOIN without unique alias
WHERE `roles`.`code` IN ('admin','player')

[brendt]

@laylatichy is this something you're interested in looking at? No worries if you can't or won't, I just wanted to check before I dive into it :)

[laylatichy]

@brendt will do