🐛 HasOne. select by relation property generates unnecessary INNER JOIN

[gam6itko]

No duplicates 🥲.

• I have searched for a similar issue in our bug tracker and didn't find any solutions.

What happened?

It seems that I found a bug in forming a request with HasOne.

There are 2 entities: User, Passport.
Not all users may have a passport.

#[Cycle\Entity(table: 'user')]
class User
{
    #[Cycle\Column(type: 'primary', unsigned: true)]
    public ?int $id = null;

    #[Cycle\Column(type: 'string')]
    public string $username;

    #[Cycle\Relation\HasOne(target: Passport::class, nullable: true, outerKey: 'user_id')]
    public ?Passport $passport = null;
}

#[Cycle\Entity(table: 'passport')]
class Passport
{
    #[Cycle\Column(type: 'primary', unsigned: true)]
    public ?int $id = null;

    #[Cycle\Column(type: 'string')]
    public string $number;

    #[Cycle\Relation\BelongsTo(target: User::class, innerKey: 'user_id', indexCreate: false)]
    public User $user;
}

I want to find all user without passport information.

$result = $orm
    ->getRepository(User::class)
    ->select()
    ->load('passport')
    ->where('passport.id', null)
    ->fetchAll();

We have 0 rows in $result.

If we look at the request logs, we see 2 JOINs at once: INNER and LEFT.

SELECT `user`.`id` AS `c0`, `user`.`username` AS `c1`, `l_user_passport`.`id` AS `c2`, `l_user_passport`.`number` AS `c3`, `l_user_passport`.`user_id` AS `c4`
FROM `user` AS `user` 
INNER JOIN `passport` AS `user_passport`
    ON `user_passport`.`user_id` = `user`.`id` 
LEFT JOIN `passport` AS `l_user_passport`
    ON `l_user_passport`.`user_id` = `user`.`id`  
WHERE `user_passport`.`id` IS NULL 

That INNER is unnecessary here.

Version

ORM 2.8.1
PHP 8.2

You can see this test case here

[gam6itko]

here the workaround

        $result = $orm
            ->getRepository(User::class)
            ->select()
            ->with('passport', ['method' => JoinableLoader::LEFT_JOIN])
            ->where('passport.id', null)
            ->fetchAll();

we have SQL like

[MySQLDriver] SELECT `user`.`id` AS `c0`, `user`.`username` AS `c1`
FROM `user` AS `user` 
LEFT JOIN `passport` AS `user_passport`
    ON `user_passport`.`user_id` = `user`.`id`  
WHERE `user_passport`.`id` IS NULL 

[wolfy-j]

Hi,

This is not a bug. By default, the load method is detached from any with method calls that include where calls. This is done in order to allow you to filter and select data separately. I imagine you want to load all users and all of their orders, but only when user has orders > X.

[gam6itko]

this works to

 $result = $orm
            ->getRepository(User::class)
            ->select()
            ->with('passport', [
                'where' => ['id' => null],
                'method' => JoinableLoader::LEFT_JOIN,
            ]);