Using RealURL v2 with a JOIN statement (TYPO3)

Question

I need to join two tables for configuration of fixedPostVars in realurl. All solutions I found are for older version < 2 of realurl.

Following SQL statement returns wanted result

SELECT 
CONCAT(DATE_FORMAT(FROM_UNIXTIME(tx_myext_domain_model_event.start_date), \'%d-%m-%Y\'),"-",tx_myext_domain_model_event.title,"-",tx_myext_domain_model_event.city) 
FROM 
    tx_myext_domain_model_eventannouncement
INNER JOIN
    tx_myext_domain_model_event
ON
    tx_myext_domain_model_eventannouncement.event = tx_myext_domain_model_event.uid
WHERE
    tx_myext_domain_model_eventannouncement.uid = 2
;

Is there a way to integrate this SQL in the realurl configuration? I tried this but URLs result in https://example.com/.../detail/2/

'GETvar' => 'tx_myext_eventannouncement[eventannouncement]',
'lookUpTable' => [
    'table' => 'tx_myext_domain_model_eventannouncement',
    'id_field' => 'tx_myext_domain_model_eventannouncement.uid',
    'alias_field' => 'INNER JOIN tx_myext_domain_model_event ON tx_myext_domain_model_eventannouncement.event = tx_myext_domain_model_event.uid CONCAT(DATE_FORMAT(FROM_UNIXTIME(tx_myext_domain_model_event.start_date), \' % d -%m -%Y\'),"-",tx_myext_domain_model_event.title,"-",tx_myext_domain_model_event.city)',
    'addWhereClause' => ' AND tx_myext_domain_model_eventannouncement.deleted=0 AND tx_myext_domain_model_eventannouncement.hidden=0',
    'useUniqueCache' => true,
    'useUniqueCache_conf' => [
        'strtolower' => true,
        'spaceCharacter' => '-',
    ],
    'enable404forInvalidAlias' => true,
],

score 1 · Answer 1 · answered Jan 15 '18 at 05:53

You have two options to allow RealURL to access your field through this join.

First to use a subquery intead of joins.

(SELECT DISTINCT
CONCAT(
DATE_FORMAT(FROM_UNIXTIME(tx_myext_domain_model_event.start_date), \'%d-%m-%Y\'),
"-",
tx_myext_domain_model_event.title,
"-",
tx_myext_domain_model_event.city) 
FROM tx_myext_domain_model_event
INNER JOIN
    tx_myext_domain_model_event
ON
    tx_myext_domain_model_eventannouncement.event = tx_myext_domain_model_event.uid) 
as alias_field

(Please note, this code snippet is an idea only, I had not the opportunity to test it for you.)

The second is to create a view:

CREATE VIEW tx_myext_event_view AS 
    SELECT tx_myext_domain_model_eventannouncement.*,
CONCAT(DATE_FORMAT(FROM_UNIXTIME(tx_myext_domain_model_event.start_date), \'%d-%m-%Y\'),"-",tx_myext_domain_model_event.title,"-",tx_myext_domain_model_event.city) as alias_field
FROM 
    tx_myext_domain_model_eventannouncement
INNER JOIN
    tx_myext_domain_model_event
ON
    tx_myext_domain_model_eventannouncement.event = tx_myext_domain_model_event.uid

Now you can set the RealURL config like:

'GETvar' => 'tx_myext_eventannouncement[eventannouncement]',
'lookUpTable' => [
    'table' => 'tx_myext_event_view',
    'id_field' => 'tx_myext_event_view.uid',
    'alias_field' => 'tx_myext_event_view.alias_field',
    'useUniqueCache' => true,
    'useUniqueCache_conf' => [
        'strtolower' => true,
        'spaceCharacter' => '-',
    ],
    'enable404forInvalidAlias' => true,
],

I would try with the subquery because to maintain a view in an extension is tricky. However it could be a better solution for an even more complex logic or if you have multiple definitions getting information from the same base with different conditions.

Thank you for your idea. I tried the query in alias_field and several other variants but unfortunately none of them works. — Heinz Schilling, Jan 15 '18 at 20:06

Using RealURL v2 with a JOIN statement (TYPO3)

1 Answers1