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:
- There are course templates and seminars
- A course template always has a start and end date, it contains courses that depend on one another
- A seminar contains multiple courses that do not depend on one another
- As long as a course template starts after the selected date, it has to be displayed
- As long as a seminar contains a course that starts after the selected date, it has to be displayed
- 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:
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.
'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, ], ],
$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
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'], ], ], ], ], ],
- → Prevent form action from being followed by robots
- → Render Link within Typoscript Controller
- → How can I implement the sitemaps for a TYPO3 Multidomainsystem when i have only one root folder?
- → TYPO3 - Overriding & adding meta tags (from tx_metaseo) on detail view of custom extension
- → How can i remove the region in TYPO3 9 SEO extension from hreflang
- → generate SEO sitemap for tt_address in TYPO3 and how to beautify with slugs
- → TYPO3 and IOS: can't open the page
- → TYPO3: How do i render tt_content text elements in my own extensions?
- → TYPO3 News Sitemap Configuration - actual date in additionalWhere statement
- → Check the Database of a typo3-Website
- → How to create a record and add an image in database in TYPO3
- → TYPO3 Frontent: Form Action results Page not found
- → RealURL with GET parameters