Respect Extbase storage pid in foreign_table_where

I wrote an extension with multi language and multi domain in mind and ran into a problem with the TCA fields.

The controllers and repositories have no issue fetching results based of the persistence.storagePid constant defined for each root page but I have not found a way to achieve the same for the selectMultipleSideBySide TCA field:

'config' => [
 'type' => 'select',
 'renderType' => 'selectMultipleSideBySide',
 'MM' => 'tx_lmyext_domain_model_model1_mm',
 'foreign_table' => 'tx_myext_domain_model_model1',
 'foreign_table_where' => ' AND tx_myext_domain_model_model1.sys_language_uid IN (-1,###REC_FIELD_sys_language_uid###) ORDER BY tx_locationslist_domain_model_model1.title',
 'maxitems' => 1,
],

The persistence settings seem to have no impact on the query and I see all objects that exist in the database on the right side of the selection field domain.

When selecting Object from PID 90 it is not shown in the frontend due to the query settings of the repository but an editor would still be able to select it inside the TCA field.

Is there a way to only show items from a specific pid like `AND tx_myext_domain_model_model1.pid IN (###STORAGE-PID###) with the previously removed tag ###STORAGE-PID###?

The closest I've come so far is with `AND tx_myext_domain_model_model1.pid IN (###SITEROOT###)' but that would require all of the objects to be saved inside the root page of each domain and I'd like to save them inside a folder.

I can not define the pid directly inside the query since it changes based on the selected page from the page tree.

I've tried it with the PAGE_TSCONFIG_ID tag which seemed to work fine but I'd like the configuration for every domain to work solely with the TypoScript constant to make the configuration easier.

I've also tried using the group element but it had the downside of having to select every element by itself and still made it possible to select objects which are not in the correct storage pid.

Setting the storage pid inside the repository with initializeObject() did not change anything for the available items inside the TCA either.

Sadly, you did not mention the used TYPO3 Version.

Since TYPO3 v10.4 you could define the storage pid in the SiteConfig for each domain/rootpage as setting. and TYPO3 provides that value as TypoScript constant AND pageTSConfig constant.

Note: That means you can only define things per rootPage, and not single pages in the tree beneath a rootPage/siteConfig.

See: https://docs.typo3.org/c/typo3/cms-core/main/en-us/Changelog/10.4/Feature-91080-SiteSettingsAsTsConstantsAndInTsConfig.html

So, instead of defining the storage page value direclty in TypoScript/PageTS define it in the site configurations

For TYPO3 v10 and v11: site-identifier/config.yml

settings:
  myStoragePid: 123

For TYPO3 v12 and newer: site-identifier/settings.yml

myStoragePid: 123

Adjust your PageTS/TypoScript to use the constant to retrieve the value than:

{$myStoragePid}

With that in place, you can use a placeholder marker in your where condition for the TCA configuration

'config' => [
 'type' => 'select',
 'renderType' => 'selectMultipleSideBySide',
 'MM' => 'tx_lmyext_domain_model_model1_mm',
 'foreign_table' => 'tx_myext_domain_model_model1',
 'foreign_table_where' => ' AND tx_myext_domain_model_model1.pid = ###SITE:settings.myStoragePid### AND tx_myext_domain_model_model1.sys_language_uid IN (-1,###REC_FIELD_sys_language_uid###) ORDER BY tx_locationslist_domain_model_model1.title',
 'maxitems' => 1,
],

See: https://docs.typo3.org/m/typo3/reference-coreapi/main/en-us/ApiOverview/SiteHandling/UseSiteInTCA.html

I've tried it with the PAGE_TSCONFIG_ID tag which seemed to work fine but I'd like the configuration for every domain to work solely with the TypoScript constant to make the configuration easier.

TypoScript is soley for frontend rendering configuration. Expecting it to have an influence in the backend is a narrow assumption. Therefore, you rule out a working solition with PAGE_TSCONFIG_ID.

Another approach would be to have a the pages table extended by a field defining the storage pid and using it as a simple join - however, that does not cover page rootline traversel atm in pure sql.

There are no other solutions for this.

Note: These kind of TCA values only work in web request contexts where a proper site/page context exists. Using it for example in CLI or in "early" middlewares does not work as the proper page context has not been set by TYPO3 (and cannot).