Efficient Flexform Database Queries in TYPO3

Question

Is there an easy and / or efficient way to make database requests that take fields in flexform columns into account, e.g. for sorting, where clause, etc.

May it be through TypoScript DatabaseQueryProcessor or PHP exec_INSERT/UPDATE/DELETE/SELECTquery.

I currently don't have a good valid option how to do it, would be really happy for a solution.

UPDATE: Thanks for all of your answers, I was thinking about switching some setups from normal database columns to Flexform to allow backend users to create the form structure dynamically but I obviousely won't do this for fields that I need to search through as this needs to be parsed twice as all of you guys mentioned. Anyways the new FlexForm Processor is pretty cool, it makes reading those fields for normal data output super easy (and it brings an end to different flexform processing of each extension which I was struggling with sometimes). Thanks for your help.

Answer 1

No. Flexforms are stored as XML in a text field. So, for querying entities of the XML, this text field has to be parsed for all rows.

What's your use-case? Maybe there's another way...

Answer 2

Not really, flexforms are stored as string representation of XML and it will be difficult or impossible to order or search them by some value. The best you can do actually is fetching. You can try to search by fulltext even with some XML tags, however only thing you can do for sorting is fetching whole set of rows and sorting them in PHP (highly uneffective)

If for some reason sorting by value from FF is really important for you, only option is custom extension, with column storing atomic values. You can set this order in custom field of form or ie, by hook during save or update of the row.

Answer 3

You might want to take a look at the ExtractValue approach of MySQL in this case.

https://dev.mysql.com/doc/refman/5.7/en/xml-functions.html

If you know the exact XPATH to your XML tag, this will return the text value of that XML tag.

SELECT ExtractValue(pi_flexform, '/xpath/to/your/desired/tag') AS flexform_fieldname;

It's not very performant, since still the strings have to be parsed, but in this case it will be done on the MySQL server side with a function that might still give you some advantage over PHP based parsing.

We've got some good results with that approach while migrating content elements away from FlexForm fields and into really normalised database fields.

Answer 4

In the brand new TYPO3 11.1 there has been implemented a native FlexForm Data Processor into the TYPO3 core:

10 = TYPO3\CMS\Frontend\DataProcessing\FlexFormProcessor
10 {
    fieldName = my_flexform_field
    as = myOutputVariable
}

See the feature description for more detauls: #89509 - Data Processor to resolve FlexForm data