CODEWITHSUNDEEP
typo3extbase
CDRO
CDRO

Reputation: 139

Extbase proper relation discovery

I've come accross a weird issue with extbasewhile working on some semi-complicated logic for filtering of courses for a LMS tools that we work on.

The logic is as follows:

  1. There are course templates and seminars
  2. A course template always has a start and end date, it contains courses that depend on one another
  3. A seminar contains multiple courses that do not depend on one another
  4. As long as a course template starts after the selected date, it has to be displayed
  5. As long as a seminar contains a course that starts after the selected date, it has to be displayed
  6. There are other filters that do not matter here and that do not play into this issue

In order to solve this request, I resorted to the power of extbase being able to simply create a subquery by using something like $query->greaterThanOrEqual('template_children.start_date', $date) (see below for concrete example). This now generates the below result:

Resulting SQL:

    SELECT `tx_xxx_domain_model_courseprogrammetemplate`.* 
    FROM   `tx_xxx_domain_model_courseprogrammetemplate` 
           `tx_xxx_domain_model_courseprogrammetemplate` 
           LEFT JOIN `tx_xxx_domain_model_courseprogrammetemplate` 
                     `tx_xxx_domain_model_courseprogrammetemplate0` 
                  ON Find_in_set( 
                     `tx_xxx_domain_model_courseprogrammetemplate0`.`uid`, 
    `tx_xxx_domain_model_courseprogrammetemplate`.`template_children`)

The relations are built by an important and there are no values written to the field template_children on this side of the relation, thus no result is found.

AFAIK, this should work without having to populate this field with anything else than maybe an amount of children (and I'm not sure if this is even necessary anymore).

Here's my TCA configuration and the PHP code handling the logic.

TCA:

    'template_children' => [
        'exclude' => true,
        'label' => 'LLL:EXT:xxx/Resources/Private/Language/locallang_db.xlf:tx_xxx_domain_model_courseprogrammetemplate.template_children',
        'config' => [
            'items' => [
                ['', 0]
            ],
            'type' => 'select',
            'renderType' => 'selectSingleBox',
            'foreign_table' => 'tx_xxx_domain_model_courseprogrammetemplate',
            'foreign_table_where' => 'AND tx_xxx_domain_model_courseprogrammetemplate.template = ###REC_FIELD_uid### AND tx_xxx_domain_model_courseprogrammetemplate.sys_language_uid = 0',
            'readOnly' => 1,
            'size' => 5,
            'maxitems' => 100,
            'autoSizeMax' => 20,
        ],
    ],

Extbase:

    $constraints[] =
        $query->logicalAnd(
            [
                $query->logicalOr(
                    [
                        // If the learning form is a course, the start and end date should be in between the period
                        $query->logicalAnd(
                            [
                                $query->greaterThanOrEqual('start_date', $demand->getStartDate()->format('Y-m-d H:i:s')),
                                $query->logicalNot($query->equals('learning_form', 'Seminar'))
                            ]
                        ),
                        // If the learning form is seminar, we only want to display it, if there is at least one course that starts in this period
                        $query->logicalAnd(
                            [
                                $query->logicalOr(
                                    [
                                        $query->greaterThanOrEqual('templateChildren.start_date', $demand->getStartDate()->format('Y-m-d H:i:s')),
                                    ]
                                ),
                                $query->equals('learning_form', 'Seminar')
                            ]
                        )
                    ]
                )
            ]
        );

I tried switching the TCA field type to inline but this didn't change the behaviour.

Another way to do this would be to get all objects that relate to each seminar that match the filter, but that would mean creating some thousands of separate queries while filter :-/

Thanks for your support.

PS: I found this article, but it does not describe, how to configure the TCA accordingly, so that it works: TYPO3 Extbase: Filtering a 1:N relation Also sadly the documentation doesn't say much about what to configure how in TCA for this to work: https://docs.typo3.org/m/typo3/book-extbasefluid/master/en-us/6-Persistence/3-implement-individual-database-queries.html

Upvotes: 0

Views: 64

Answers (1)

CDRO
CDRO

Reputation: 139

I ended up finding the solution to my problem: you have to use inline as a type so that extbase has a chance to know how to resolve the relation:

    'template_children' => [
        'exclude' => true,
        'label' => 'LLL:EXT:xxx/Resources/Private/Language/locallang_db.xlf:tx_xxx_domain_model_courseprogrammetemplate.template_children',
        'config' => [
            'items' => [
                ['', 0]
            ],
            'type' => 'inline',
            'foreign_table' => 'tx_xxx_domain_model_courseprogrammetemplate',
            'foreign_field' => 'template',
                'appearance' => [
                    'collapseAll' => 1,
                    'levelLinksPosition' => 'top',
                    'showSynchronizationLink' => 1,
                    'showPossibleLocalizationRecords' => 1,
                    'showAllLocalizationLink' => 1
                ],
                'overrideChildTca' => [
                    'ctrl' => [
                        'types' => [
                            '1' => ['showitem' => 'sys_language_uid, l10n_parent, l10n_diffsource, hidden, title'],
                        ],
                    ],
                ],
        ],
    ],

Upvotes: 1

Related Questions