Feature #83353

Add extbase db operand for using ExtractValue() in queries, allowing parsing xml data like e.g. pi_flexform

Added by Markus Mächler almost 2 years ago. Updated almost 2 years ago.

Status:
Needs Feedback
Priority:
Should have
Category:
Extbase
Target version:
-
Start date:
2017-12-16
Due date:
% Done:

0%

PHP Version:
Tags:
Complexity:
easy
Sprint Focus:

Description

I suggest to add a new operand that allows using the MySQL ExtractValue() function that allows retrieving a value specified by an xpath of the xml-content .
See https://dev.mysql.com/doc/refman/5.6/en/xml-functions.html#function_extractvalue

This can be particularly useful when working with flexform data, e.g. also when creating content elements with gridelements.

Example usage e.g. in SampleRepository.php:


namespace BKW\BkwTiles\Domain\Repository;

use VND\MyExtension\Domain\Db\ExtractValue;

class SampleRepository extends \TYPO3\CMS\Extbase\Persistence\Repository {
   /**
     * Returns all something elements on a page, filtered by title
     * @param string $someTitle title to filter for
     * @return array|\TYPO3\CMS\Extbase\Persistence\QueryResultInterface
     * @throws \TYPO3\CMS\Extbase\Persistence\Exception\InvalidQueryException
     */
    public function findFiltered($someTitle) {
      $query->logicalAnd([
        $query->equals('ctype', 'something'),
        $query->equalsExtractValue('//data/sheet[@index="text"]/language[1]/field[@index="title"]/value', 'pi_flexform', $someTitle)
      ]);
    }
}

So far, we implemented this by extending the Typo3DbQueryParser with the following code:

<?php

namespace VND\MyExtension\Xclass\Db;

use VND\MyExtension\Domain\Db\ExtractValueInterface;
use TYPO3\CMS\Extbase\Persistence\Generic\Qom\DynamicOperandInterface;
use TYPO3\CMS\Extbase\Persistence\Generic\Qom\JoinInterface;
use TYPO3\CMS\Extbase\Persistence\Generic\Qom\SelectorInterface;
use TYPO3\CMS\Extbase\Persistence\Generic\Qom\SourceInterface;

class Typo3DbQueryParser extends \TYPO3\CMS\Extbase\Persistence\Generic\Storage\Typo3DbQueryParser {
    /**
     * @param DynamicOperandInterface $operand
     * @param SourceInterface $source The source
     * @param array &$sql The query parts
     * @return string
     * @throws \InvalidArgumentException
     */
    protected function parseOperand(DynamicOperandInterface $operand, SourceInterface $source, array &$sql)
    {
        if ($operand instanceof ExtractValueInterface) {
            if ($source instanceof SelectorInterface) {
                // @todo Only necessary to differ from  Join
                $className = $source->getNodeTypeName();
                $tableName = $this->dataMapper->convertClassNameToTableName($className);
            } elseif ($source instanceof JoinInterface) {
                $tableName = $source->getJoinCondition()->getSelector1Name();
            }
            $constraintSQL = 'ExtractValue(' . parent::parseOperand($operand, $source, $sql) . ', '.
                $this->databaseHandle->fullQuoteStr($operand->getXpath(), isset($tableName) ? $tableName : '').')';
        } else {
            return parent::parseOperand($operand, $source, $sql);
        }
        return $constraintSQL;
    }
}

where the line containing the ExtractValue() part is new, the rest is copied from the original Typo3DbQueryParser

a new operand ExtractValue extending the existing PropertyValue is defined as follows:
ExtractValueInterface.php

<?php

namespace VND\MyExtension\Domain\Db;

use TYPO3\CMS\Extbase\Persistence\Generic\Qom\PropertyValueInterface;

interface ExtractValueInterface extends PropertyValueInterface {
    /**
     * Gets the xpath
     *
     * @return string the property name; non-null
     */
    public function getXpath();
}

ExtractValue.php
<?php

namespace VND\MyExtension\Domain\Db;

use TYPO3\CMS\Extbase\Persistence\Generic\Qom\PropertyValue;

class ExtractValue extends PropertyValue implements ExtractValueInterface {
    /**
     * @var string
     */
    protected $xpath;

    /**
     * Constructs this ExtractValue instance
     *
     * @param string $propertyName
     * @param string $selectorName
     * @param string $xpath
     */
    public function __construct($xpath, $propertyName, $selectorName)
    {
        parent::__construct($propertyName, $selectorName);
        $this->xpath = $xpath;
    }

    /**
     * Gets the xpath
     *
     * @return string xpath; non-null
     */
    public function getXpath()
    {
        return $this->xpath;
    }
}

and in Query.php you would have something like

<?php
namespace VND\MyExtension\Xclass\Db;

/**
 * The Query class used to run queries against the database
 *
 * @api
 */
class Query extends \TYPO3\CMS\Extbase\Persistence\Generic\Query {
   /**
     * Returns an equals criterion used for matching objects against a query
     *
     * @param string $xpath xpath to query for
     * @param string $propertyName The name of the property to compare against
     * @param mixed $operand The value to compare with
     * @return \TYPO3\CMS\Extbase\Persistence\Generic\Qom\ComparisonInterface
     * @api
     */
    public function equalsExtractValue($xpath, $propertyName, $operand)
    {
        $comparison = $this->qomFactory->comparison(
                $this->qomFactory->extractValue($xpath, $propertyName, $this->getSelectorName()),
                QueryInterface::OPERATOR_EQUAL_TO,
                $operand
            );

        return $comparison;
    }

and finally in the QueryObjectModelFactory.php you have

namespace VND\MyExtension\Xclass\Db;

class QueryObjectModelFactory extends \TYPO3\CMS\Extbase\Persistence\Generic\Qom\QueryObjectModelFactory {
    /**
     * Evaluates to the value (or values, if multi-valued) of a property in the specified or default selector.
     *
     * @param string $xpath xpath
     * @param string $propertyName the property name; non-null
     * @param string $selectorName the selector name; non-null
     * @return \VND\MyExtension\Domain\Db\ExtractValueInterface the operand; non-null
     * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception\RepositoryException if the operation otherwise fails
     */
    public function extractValue($xpath, $propertyName, $selectorName = '')
    {
        return $this->objectManager->get(\VND\MyExtension\Domain\Db\ExtractValue::class, $xpath, $propertyName, $selectorName);
    }
}

Note for developers that want to use this feature in TYPO3 7 or 8:
If you don't want to xclass the QueryObjectModelFactory or Query classes, you can also just xclass the Typo3DbQueryParser as explained above and use the ExtractValue object in your repository directly:


namespace BKW\BkwTiles\Domain\Repository;

use VND\MyExtension\Domain\Db\ExtractValue;

class SampleRepository extends \TYPO3\CMS\Extbase\Persistence\Repository {

  /**
     * @var \TYPO3\CMS\Extbase\Persistence\Generic\Qom\QueryObjectModelFactory
     */
    protected $qomFactory;

    /**
     * @param \TYPO3\CMS\Extbase\Persistence\Generic\Qom\QueryObjectModelFactory $qomFactory
     */
    public function injectQomFactory(\TYPO3\CMS\Extbase\Persistence\Generic\Qom\QueryObjectModelFactory $qomFactory)
    {
        $this->qomFactory = $qomFactory;
    }

  /**
     * Evaluates to the xpath of an xml-value (or values, if multi-valued) of a property in the specified or default selector.
     *
     * @param string $propertyName the property name; non-null
     * @param string $selectorName the selector name; non-null
     * @return \TYPO3\CMS\Extbase\Persistence\Generic\Qom\PropertyValueInterface the operand; non-null
     * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception\RepositoryException if the operation otherwise fails
     */
    public function extractValue($xpath, $propertyName, $selectorName = '')
    {
        return $this->objectManager->get(ExtractValue::class, $xpath, $propertyName, $selectorName);
    }

      /**
     * Returns an equals criterion used for matching objects against a query
     *
     * @param string $xpath xpath to query for
     * @param string $propertyName The name of the property to compare against
     * @param mixed $operand The value to compare with
     * @return \TYPO3\CMS\Extbase\Persistence\Generic\Qom\ComparisonInterface
     * @api
     */
    public function equalsExtractValue($xpath, $propertyName, $operand)
    {
        $comparison = $this->qomFactory->comparison(
                $this->qomFactory->extractValue($xpath, $propertyName, $this->getSelectorName()),
                QueryInterface::OPERATOR_EQUAL_TO,
                $operand
            );

        return $comparison;
    }

   /**
     * Returns all something elements on a page, filtered by title
     * @param string $someTitle title to filter for
     * @return array|\TYPO3\CMS\Extbase\Persistence\QueryResultInterface
     * @throws \TYPO3\CMS\Extbase\Persistence\Exception\InvalidQueryException
     */
    public function findFiltered($someTitle) {
      $query->logicalAnd([
        $query->equals('ctype', 'something'),
        $this->equalsExtractValue('//data/sheet[@index="text"]/language[1]/field[@index="title"]/value', 'pi_flexform', $someTitle)
      ]);
    }
}

So, what do you guy think about this feature / approach? It would be nice to see this in the TYPO3 extbase core. It helps handling flexforms a lot and encourages people to use extbase for database abstraction and handling content elements.

History

#1 Updated by Mathias Schreiber almost 2 years ago

  • Status changed from New to Needs Feedback
  • Assignee set to Mathias Schreiber

Hi Markus,

how would this work on Postgres and MS SQL Server?

Apart from that we try to discourage storing content in XML blobs, but that's a side-topic.
Easier access to configuration (what flexforms are intended to be used for) is a good thing.

#2 Updated by Markus Mächler almost 2 years ago

@Mathias Schreiber
with some adaptions of the syntax this can also work for PostgreSQL and MS SQL Server.

PostgreSQL: https://www.postgresql.org/docs/9.1/static/functions-xml.html#FUNCTIONS-XML-PROCESSING
this should work out of the box by using the xpath('/my/xpath', my_field) function instead of ExtractValue().

SQL-Server: https://docs.microsoft.com/en-us/sql/t-sql/xml/value-method-xml-data-type
this would require either typecasting while querying or requiring the corresponding column to be of type xml.
in the latter case it would be as easy as using my_field.value('/my/xpath', varchar) function instead of ExtractValue(). also, the result type of the xpath value needs to be given explicitly.

| Apart from that we try to discourage storing content in XML blobs, but that's a side-topic.

I very much agree with you. However, as flexform xml data is reality, database operations with extbase should be improved to support xml data.

#3 Updated by Markus Mächler almost 2 years ago

@Matthias Schreiber can you evaluate whether the solution proposed is suitable for a pull request?

#4 Updated by Mathias Schreiber almost 2 years ago

Could you get in touch with Claus Due on TYPO3s Slack Team?
He has a PR ready that stores flexform data as JSON.
Since these two topics are pretty close to each other I suggest to work onwards from there and get the data retrieval "interfaced" so people can fetch both JSON and XML in a convenient manner.

Also available in: Atom PDF