Project

General

Profile

Actions

Feature #83353

closed

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

Added by Markus Mächler over 6 years ago. Updated 3 months ago.

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

0%

Estimated time:
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.


Related issues 1 (0 open1 closed)

Related to TYPO3 Core - Feature #82212: TYPO3 should be able to store JSON instead of XML in FlexForm fieldsRejected2017-08-26

Actions
Actions #1

Updated by Mathias Schreiber over 6 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.

Actions #2

Updated by Markus Mächler over 6 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.

Actions #3

Updated by Markus Mächler over 6 years ago

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

Actions #4

Updated by Mathias Schreiber over 6 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.

Actions #5

Updated by Jonas Eberle about 3 years ago

Here's the forge ticket of the feature that Mathias mentioned: https://forge.typo3.org/issues/82212

By the way: You can use any function of your DBMS in the dbal QueryBuilder with `->addSelectLiteral()`. Maybe that would suit you?

Actions #6

Updated by Simon Schaufelberger almost 2 years ago

  • Related to Feature #82212: TYPO3 should be able to store JSON instead of XML in FlexForm fields added
Actions #7

Updated by Christian Kuhn 3 months ago · Edited

  • Status changed from Needs Feedback to Closed

Hey. I'm not a fan of adding this to the extbase query layer. If at all, it needs proper lower level support on the doctrine / core-doctrine-implementation level first. Also, indexing is a huge issue on this, we're currently experiencing the various db issues on this level with a json based cache backend.

As such, this feature request won't make it anytime soon. I hope it's ok to close here for now and eventually come back later if lower levels implemented something.

Actions

Also available in: Atom PDF