Unwanted query triggered when using a composite key on a self-referencing entity

[alexandrin-rus]

Hello,

I am encountering a strange issue and cannot understand why this is happening.

I have an entity that is using a composed primary key on the instance and rid fields.

I also have parent as a self referencing relation to the same entity.

class Subscription
{
    /**
     * @ORM\Column(name="id", type="uuid", unique=true)
     * @JMS\Type("string")
     */
    protected $id;

    /**
     * @ORM\Id
     * @ORM\Column(name="instance_id", type="integer")
     * @JMS\Type("int")
     */
    protected $instance;

    /**
     * @ORM\Id
     * @ORM\Column(name="rid", type="bigint")
     * @ORM\SequenceGenerator(sequenceName="subscriptions_id_seq", initialValue=1, allocationSize=1)
     * @JMS\Type("int")
     */
    protected $rid;

    /**
     * @ORM\ManyToOne(targetEntity="Subscription")
     * @ORM\JoinColumns({
     *     @ORM\JoinColumn(name="parent_int_id", referencedColumnName="rid"),
     *     @ORM\JoinColumn(name="instance_id", referencedColumnName="instance_id")
     * })
     */
    protected $parent;

    ...
}

The problem I am facing:

For the following data:

id	rid	parent_int_id	instance_id
11000000-0000-0000-0000-0000000f37d2	147605	890467	1
11000000-0000-0000-0000-0000000e065f	890467	null	1

$subscription = $subscriptionService->findOneBy([
    'rid' => 147605
]);

When retrieving a subscription using findOneBy() I see 3 queries being triggered by Doctrine:

This is the initial query, works as expected:

SELECT t0.id AS id_1, t0.instance_id AS instance_id_2, t0.rid AS rid_3, ...
FROM subscriptions t0 WHERE t0.rid = 147605 LIMIT 1;

This is the query for the parent field, also working as expected:

SELECT t0.id AS id_1, t0.instance_id AS instance_id_2, t0.rid AS rid_3, ... 
FROM subscriptions t0 WHERE t0.rid = '890467' AND t0.instance_id = 1 LIMIT 1;

This query is unexpected and cannot find a way to disable it or understand why it is triggered:

SELECT t0.id AS id_1, t0.instance_id AS instance_id_2, t0.rid AS rid_3, ...
FROM subscriptions t0 WHERE t0.instance_id = 1

As you can see, the 3rd query tries to select all rows for that instance_id which is a problem as our table is very large (millions of rows) and it would cause our database to crash. The retrieved object is built correctly and the parent property is populated as expected, but I would expect that query to not exist at all.

Removing the instance as a composite key stops this behaviour, however we need it to work in this format, so it's not an option.

Any idea what might be causing this additional query and how to get rid of it, so it will work as intended?

[derrabus]

@ORM\JoinColumn(name="instance_id", referencedColumnName="instance_id")

The foreign key column and the referenced column are the same column. That does not make sense at all.

[alexandrin-rus]

In this case as the relationship is referencing the same table I think it's correct. Basically my understanding is that this would tell Doctrine, that when it retrieves the parent for an object it should take the value from parent_int_id and instance_id and use it in the WHERE clause. This behaviour can also be seen as working exactly like that in the 2nd query triggered:

SELECT t0.id AS id_1, t0.instance_id AS instance_id_2, t0.rid AS rid_3, ... 
FROM subscriptions t0 WHERE t0.rid = '890467' AND t0.instance_id = 1 LIMIT 1;

The issue is that it also triggeres this 3rd unwanted query:

SELECT t0.id AS id_1, t0.instance_id AS instance_id_2, t0.rid AS rid_3, ...
FROM subscriptions t0 WHERE t0.instance_id = 1

Edited the original post - I've mistakenly pasted the 1st query twice initially.

Thank you for reading.

[derrabus]

In this case as the relationship is referencing the same table I think it's correct.

No. Foreign key column and referenced column have to be different columns.

The issue is that it also triggeres this 3rd unwanted query:

I understood that and you're more than welcome to find out where that query is coming from. But since your association mapping is invalid, my recommendation would be to fix that first.