[4.x]: Relations query doesn't take the site into account

[MoritzLost]

What happened?

Description

I have an issue where relations of a propagated entry are affecting results in other sites.

I have a Craft installation with 10 sites and a section set to manual propagation. One entry is propagated to only one site. However, for some reason, there is a row for this entry in the relations table for every site:

mysql> select * from kr_relations where sourceId=692 order by sourceSiteId;
+------+---------+----------+--------------+----------+-----------+---------------------+---------------------+--------------------------------------+
| id   | fieldId | sourceId | sourceSiteId | targetId | sortOrder | dateCreated         | dateUpdated         | uid                                  |
+------+---------+----------+--------------+----------+-----------+---------------------+---------------------+--------------------------------------+
| 2203 |       6 |      692 |            1 |      693 |         1 | 2023-07-25 12:49:54 | 2023-07-25 12:49:54 | b0934d29-55b0-4699-b157-66b856e2ffa5 |
| 2206 |       6 |      692 |            2 |      693 |         1 | 2023-07-25 12:49:54 | 2023-07-25 12:49:54 | 9d943e28-4553-48d5-9f1d-c78de331b9f8 |
| 7264 |       6 |      692 |            3 |      594 |         1 | 2024-02-09 17:29:01 | 2024-02-09 17:29:01 | 0899aa09-8e84-4ad5-abb8-7737a0e52dc2 |
| 2204 |       6 |      692 |            4 |      693 |         1 | 2023-07-25 12:49:54 | 2023-07-25 12:49:54 | f92eef3c-a7e9-4ad6-9184-6046b43821ae |
| 2200 |       6 |      692 |            5 |      693 |         1 | 2023-07-25 12:49:54 | 2023-07-25 12:49:54 | 9a1c1b34-bd4e-4765-bae5-51818d4b92f3 |
| 2205 |       6 |      692 |            6 |      693 |         1 | 2023-07-25 12:49:54 | 2023-07-25 12:49:54 | 706a331f-2bb9-42a5-ab46-444b741b39f7 |
| 2198 |       6 |      692 |            7 |      693 |         1 | 2023-07-25 12:49:54 | 2023-07-25 12:49:54 | 799e413e-c291-426b-b630-4574304d0002 |
| 2202 |       6 |      692 |            8 |      693 |         1 | 2023-07-25 12:49:54 | 2023-07-25 12:49:54 | 70d67aaa-378b-40fb-82c0-7a53ef93ca36 |
| 2201 |       6 |      692 |            9 |      693 |         1 | 2023-07-25 12:49:54 | 2023-07-25 12:49:54 | 9bd520e4-26a0-4e61-90d2-790e940377ac |
| 2199 |       6 |      692 |           10 |      693 |         1 | 2023-07-25 12:49:54 | 2023-07-25 12:49:54 | 456eaa23-caf4-4594-b90f-8f3e37f39485 |
+------+---------+----------+--------------+----------+-----------+---------------------+---------------------+--------------------------------------+

The entry only exists in the site with id 3, the relation to targetId 594 is the real one that's visible in the backend. The entry doesn't exist in any other sites, but the additional rows with targetId 693 are still there. Not sure why, possibly due to #14347 or #13956. In any case, this can apparently happen in some scenarios.

Now I'm querying based on relations for that entry:

$jobs = Entry::find()
    ->section('jobs')
    ->site($site)
    ->jobStores($store)
    ->all();

• $site is the site with ID 3.
• jobsStores is the field with ID 6.
• $store is the entry with ID 693.

Now, the problem is that this query is returning the entry with ID 692, even though those aren't related in this site. I've checked the generated SQL query for the jobStores field, it looks seem to take the siteId into account:

WHERE (`targets1`.`targetId`=4426) AND (`targets1`.`fieldId`=6))) AND (((`elements`.`enabled`=TRUE) AND (`elements_sites`.`enabled`=TRUE)) AND (`entries`.`postDate` <= '2024-02-09 17:47:59') AND ((`entries`.`expiryDate` IS NULL) OR (`entries`.`expiryDate` > '2024-02-09 17:47:59'))) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)

Steps to reproduce

It's complicated, see above.

Expected behavior

Shouldn't the query always take the site specified in the query into account? Not sure if this has any unintended side effects, for example if relations aren't stored on a per-site basis.

If every case where relations remain in the relations table (like #14347 and #13956) was fixed, this problem might disappear. Though I'm sure there are other scenarios. I don't believe the entry in question was propagated to all sites and then deleted again, so the 10 rows in the database listed above must have been created in some other way.

As a workaround, maybe Craft can provide a console command to clean up those orphaned relations?

I'm not confident I fully understand the problem leading to the incorrect results here, correct me if I'm wrong …

Craft CMS version

4.7.2.1

PHP version

8.2

Operating system and version

No response

Database type and version

No response

Image driver and version

No response

Installed plugins and versions

[i-just]

Hi, thanks for reporting!

It's actually related to this one: #14258 and there's already a PR for it. If you have a sec, it would be great if you could check if that PR fixes things for you.
You can do that by changing your craftcms/cms requirement in composer.json to dev-develop#cec95d8e7426d94d1eee327fa6dc4d922851df0b as 4.7.2.1

[jakobhaerter]

Thanks @i-just! Craft 4.7.3 seems to have fixed the issue.

[i-just]

Thanks for confirming!