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
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:
The entry only exists in the site with id
3, the relation totargetId594is the real one that's visible in the backend. The entry doesn't exist in any other sites, but the additional rows withtargetId693are 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:
$siteis the site with ID3.jobsStoresis the field with ID6.$storeis the entry with ID693.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 thejobStoresfield, it looks seem to take thesiteIdinto account:Steps to reproduce
It's complicated, see above.
Expected behavior
Shouldn't the query always take the
sitespecified 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
relationstable (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