I would like to create an Extbase query (with $query = $this->createQuery(); in a repository) with a JOIN, corresponding to something like this
SELECT job1.uid,job1.pid,job1.sys_language_uid,job1.title,job2.uid,job2.l10n_parent
FROM tx_unioljobs_domain_model_job AS job1
LEFT OUTER JOIN tx_unioljobs_domain_model_job AS job2
ON job2.l10n_parent=job1.uid
WHERE (job2.uid IS NULL AND sys_language_uid=0)
OR sys_language_uid=1;
(very simplified, there is more to the query, of course)
More detail.
I am using Extbase to create this extension because it was very convenient not to write everything from scratch. For standard list and view plugins it in very helpful.
Now a new requirement has come up. With TYPO3 QueryBuilder I would be able to easily create the required queries. But this would return the records as arrays and not automatically resolve the relations. So I am trying to solve this with Extbase persistence. (Also, the extension is already finished using Extbase persistence, I just want to add the new requirement).
The new requirement is to show all records in both languages in the list view because sometimes the records exist only in one language.
So for example, German is our default language, English is our translation (sys_language_uid=1), on the German page we show all the German records (as we would normally do), but we also show the only English, mark them as only English and let them link to the English detail page (which they naturally already do).
That was actually easy to do by selecting all records with l10n_parent = 0:
$query->getQuerySettings()->setRespectSysLanguage(false);
$constraints[] = $query->equals('l10n_parent', 0);
(and then handling the extra markup in the Fluid).
Now the only-German should also be displayed on the English page. Which is not quite as easy to do. That is why I need the join (see above).
What I could do is fetch the uids in a separate query but this seems really awkward.
1
I think you can also use a “regular” QueryBuilder query to fetch the UIDs and then use the Extbase DataMapper for hydration:
$tableName = 'tx_ext_domain_model_yourmodel';
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($tableName);
$result = $queryBuilder->select('*')->from($tableName)->join(...)->executeQuery();
$dataMapper = GeneralUtility::makeInstance(DataMapper::class);
$objects = $dataMapper->map(YourModel::class, $result->fetchAll());
The Extbase Querybuilder isn’t supposed to do custom joins, but only operate on TCA-defined joins, so I’d suggest to go that route to hydrate your Domain Models like this.
(Untested code, just a general idea)