Incorrect Filtering Behavior in Multisite Setup for Related Entries with "Select Across Sites" Disabled

[MitschF]

Bug Description

In a multisite setup, the "Entries" field does not respect the Select Across Sites setting. When Select Across Sites is disabled, related entries from other sites are still displayed incorrectly. Specifically, authors are shown with articles from other sites, even when those articles are not present on the current site. This causes the articles:exists="true" filter to return inaccurate results.

 {{ collection:authors paginate="60" articles:exists="true" as="authors" }}
        {{ partial:authors/list }}
 {{ /collection:authors }}

Screenshot 1:

Screenshot 2:

---

Steps to Reproduce

1. Create an Authors collection (With Propagate true) and an Articles collection in a multisite setup.
2. Add an "Entries" field to the Authors collection, linking it to the Articles collection. Ensure the "Select Across Sites" setting is disabled.
3. Add an article to one site and relate it to an author. Ensure the same article does not exist on another site.
4. View the authors entry on a site where the article should not be present.
   Result: The article is visible on the authors page, even if it shouldn't

---

Expected Behavior

Authors without articles on the current site should not be displayed when articles:exists="true" is used, especially when "Select Across Sites" is disabled.

---

Actual Behavior

Authors with articles on other sites are still displayed, ignoring the Select Across Sites restriction and the current site context.

---

Logs

No relevant logs available.

---

Environment

Environment
Application Name: OJC Web
Laravel Version: 11.35.1
PHP Version: 8.3.14
Composer Version: 2.8.3
Environment: local
Debug Mode: ENABLED
URL: localhost
Maintenance Mode: OFF
Timezone: UTC
Locale: de

Cache
Config: NOT CACHED
Events: NOT CACHED
Routes: NOT CACHED
Views: CACHED

Drivers
Broadcasting: log
Cache: file
Database: mysql
Logs: stack / single
Mail: sendmail
Queue: sync
Session: file

Statamic
Addons: 11
Sites: 4 (OJC (ojc.de), DIJG (dijg.de), Salzkorn (ojc-salzkorn.de), hOffen (hoffen-magazin.de))
Stache Watcher: Enabled
Static Caching: Disabled
Version: 5.42.1 PRO

Statamic Addons
aryehraber/statamic-logbook: 3.3.0
daun/statamic-placeholders: 3.0.1
eminos/statamic-iconify: 1.3.0
michaelmannucci/shades: 1.0.0
mitydigital/feedamic: 2.5.0
mitydigital/statamic-sticky-notes: 1.0.2
pecotamic/sitemap: 1.4.7
stillat/relationships: 2.2.1
swiftmade/statamic-clear-assets: 3.1.0
takepart-media/statamic-oreos: 1.3.0
webographen/statamic-admin-l

---

Installation

Fresh statamic/statamic site via CLI.

---

Additional Details

No additional context provided.

[duncanmcclean]

Related to #3528.

[MitschF]

I wrote a workaround to get the pagination to work properly at least.

On a larger scale this might not be super performant but for me and my case it works pretty well.
(And it's way better than frontend-filtering like I did before -> Now the pagination works as intended)

<?php

namespace App\Scopes;

use Statamic\Query\Scopes\Scope;
use Statamic\Entries\Entry;
use Statamic\Facades\Site;

class AuthorWithArticlesScope extends Scope
{
	/**
	 * Apply the scope.
	 *
	 * @param \Statamic\Query\Builder $query
	 * @param array $values
	 * @return void
	 */
	public function apply($query, $values)
	{
		$currentLocale = Site::current()->handle();

		// Fetch all authors
		$authors = Entry::query()
			->where('collection', 'authors')
			->get();

		// Filter authors who have at least one article in the current locale
		$filteredAuthors = $authors->filter(function ($author) use ($currentLocale) {
			$articleIds = $author->get('articles');

			if (!is_iterable($articleIds) || empty($articleIds)) {
				return false;
			}

			// Fetch articles that belong to this author and are in the current locale
			$articles = Entry::query()
				->whereIn('id', $articleIds)
				->where('locale', $currentLocale)
				->get();

			return $articles->isNotEmpty(); // Keep the author if they have articles in the current locale
		});

		// Modify the query to only include filtered author IDs
		$query->whereIn('id', $filteredAuthors->pluck('id')->toArray());
	}
}

[MitschF]

And my workaround for the sitemap with the mitydigital/sitemapamic package is using a computed value like so:

		Collection::computed('authors', 'meta_include_in_xml_sitemap', function (Entry $entry) {
			$article_ids = $entry->get('articles');

			if (!is_iterable($article_ids) || empty($article_ids)) {
				return false;
			}

			// Fetch all articles in a single query
			$articles = Entry::query()
				->whereIn('id', $article_ids)
				->where('locale', Site::current()->handle())
				->get();

			return $articles->isNotEmpty(); // Returns true if at least one article matches
		});

If anyone needs a workaround for now