CODEWITHSUNDEEP
typo3extbase
Sybille Peters
Sybille Peters

Reputation: 3229

Performing a non-standard JOIN in Extbase query to get all records with no translation

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).

enter image description here

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.

Upvotes: 0

Views: 31

Answers (1)

Garvin Hicking
Garvin Hicking

Reputation: 526

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)

Upvotes: 1

Related Questions