[Frage] Extbase query setOrdering mit "FIELD(tableRow, 'tableValue1', 'tableValue2') ASC

  • xraysoft xraysoft
    T3PO
    0 x
    14 Beiträge
    0 Hilfreiche Beiträge
    19. 10. 2015, 17:46

    Hallo Forum,

    ich suche nun schon einige Zeit eine Möglichkeit ein MySQL ORDER BY in eine Extbase query zu bekommen.
    Gleich vorneweg ich möchte hier nicht mit der query Funktion statement() arbeiten.

    Die SQL-Abfrage soll dabei eigentlich folgendes machen:
    SELECT ...
    FROM ...
    WHERE ...
    ORDER BY FIELD(tableRow, 'tableValue1', 'tableValue2', 'tableValue3', ...) ASC

    Und als Extbase query:
    $query = $this->createQuery();
    $query->matching(
    $query->logicalAnd(
    // Mein Where
    )
    );
    $query->setOrderings(????);
    $query->setOffset($offset);
    $query->setLimit((int)$settings['pagelimit']);
    $result = $query->execute();

    Hat irgendjemand eine Idee wie ich das ORDER BY in setOrdering abbilden kann?


  • 1
  • aemka aemka
    Jedi-Ratsmitglied
    0 x
    562 Beiträge
    9 Hilfreiche Beiträge
    19. 10. 2015, 23:03

    Gar nicht mit Extbase so wie es ist. Du kannst dir höchstens die Arbeit machen, alle nötigen Klassen von Extbase zu beerben und dadurch so zu verändern, dass das möglich wird.
    http://blog.schreibersebastian.de/2011/10/wo-sind-die-xclasses-bei-extbase-und-fluid/
    Oder du nimmst statement().

    http://www.aemka.de/


  • 0 x
    20. 10. 2015, 16:10

    Our 4th evaluation centers on copy9 because this is becoming among the most well-known spyware trackers available on the market. Because it is well-liked however doesn’t imply that it is definitely worth the money. A lot of people make the purchase then be sorry in the future. This spyware for mobile devices provides the very same fundamental selection of functions you would locate on any other kind of portable tracker but we have seen way too many records from it not getting practical. When you have way too many poor testimonials going in it’s hard to ignore them. The main complaint according to the copy9 reviews lies with the operation and support of this spy tracker.

  • xraysoft xraysoft
    T3PO
    0 x
    14 Beiträge
    0 Hilfreiche Beiträge
    22. 10. 2015, 13:37

    Danke aemka, Deine Antwort hat geholfen!

    folgendes habe ich gemacht:
    1. In der ext_localconf.php der Extension folgendes hinzufügen:

    1. $GLOBALS['TYPO3_CONF_VARS']['SYS']['Objects']['TYPO3\\CMS\\Extbase\\Persistence\\Generic\\Storage\\Typo3DbQueryParser'] = array(
    2. 'className' => '###VENDOR###\\###EXTENSIONNAME###\\Xclass\\Typo3DbQueryParser',
    3. );

    2. Im Ordner Classes einen Ordner Xclass anlegen.
    3. Im Ordner Xclass die Datei Typo3DbQueryParser.php anlegen.

    1. <?php
    2. namespace VENDOR\EXTENSIONNAME\Xclass;
    3.  
    4. class Typo3DbQueryParser extends \TYPO3\CMS\Extbase\Persistence\Generic\Storage\Typo3DbQueryParser {
    5. /**
    6. * Constants representing the direction when ordering result sets.
    7. */
    8. const ORDER_FIELD_ASCENDING = 'ASC_FIELD';
    9. const ORDER_FIELD_DESCENDING = 'DESC_FIELD';
    10.  
    11. /**
    12. * Parses the query and returns the SQL statement parts.
    13. *
    14. * @param QueryInterface $query The query
    15. * @return array The SQL statement parts
    16. */
    17. public function parseQuery($query) {
    18. $sql = array();
    19. $sql['keywords'] = array();
    20. $sql['tables'] = array();
    21. $sql['unions'] = array();
    22. $sql['fields'] = array();
    23. $sql['where'] = array();
    24. $sql['additionalWhereClause'] = array();
    25. $sql['orderings'] = array();
    26. $sql['limit'] = ((int)$query->getLimit() ?: NULL);
    27. $sql['offset'] = ((int)$query->getOffset() ?: NULL);
    28. $source = $query->getSource();
    29. $this->parseSource($source, $sql);
    30. $this->parseConstraint($query->getConstraint(), $source, $sql);
    31. $this->parseOrderings($query->getOrderings(), $source, $sql);
    32.  
    33. $tableNames = array_unique(array_keys($sql['tables'] + $sql['unions']));
    34. foreach ($tableNames as $tableName) {
    35. if (is_string($tableName) && !empty($tableName)) {
    36. $this->addAdditionalWhereClause($query->getQuerySettings(), $tableName, $sql);
    37. }
    38. }
    39.  
    40. return $sql;
    41. }
    42.  
    43. /**
    44. * Transforms orderings into SQL.
    45. *
    46. * @param array $orderings An array of orderings (TYPO3\CMS\Extbase\Persistence\Generic\Qom\Ordering)
    47. * @param TYPO3\CMS\Extbase\Persistence\Generic\Qom\SourceInterface $source The source
    48. * @param array &$sql The query parts
    49. * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception\UnsupportedOrderException
    50. * @return void
    51. */
    52. protected function parseOrderings(array $orderings, \TYPO3\CMS\Extbase\Persistence\Generic\Qom\SourceInterface $source, array &$sql) {
    53. foreach ($orderings as $propertyName => $order) {
    54. switch ($order) {
    55. case \TYPO3\CMS\Extbase\Persistence\Generic\Qom\QueryObjectModelConstantsInterface::JCR_ORDER_ASCENDING:
    56.  
    57. case \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_ASCENDING:
    58. $order = 'ASC';
    59. $field = false;
    60. break;
    61. case \TYPO3\CMS\Extbase\Persistence\Generic\Qom\QueryObjectModelConstantsInterface::JCR_ORDER_DESCENDING:
    62.  
    63. case \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_DESCENDING:
    64. $order = 'DESC';
    65. $field = false;
    66. break;
    67.  
    68. case Typo3DbQueryParser::ORDER_FIELD_ASCENDING:
    69. $order = 'ASC';
    70. $field = true;
    71. break;
    72.  
    73. case Typo3DbQueryParser::ORDER_FIELD_DESCENDING:
    74. $order = 'DESC';
    75. $field = true;
    76. break;
    77.  
    78. default:
    79. throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception\UnsupportedOrderException('Unsupported order encountered.', 1242816074);
    80. }
    81. $className = '';
    82. $tableName = '';
    83. if ($source instanceof TYPO3\CMS\Extbase\Persistence\Generic\Qom\SelectorInterface) {
    84. $className = $source->getNodeTypeName();
    85. $tableName = $this->dataMapper->convertClassNameToTableName($className);
    86. while (strpos($propertyName, '.') !== FALSE) {
    87. $this->addUnionStatement($className, $tableName, $propertyName, $sql);
    88. }
    89. } elseif ($source instanceof TYPO3\CMS\Extbase\Persistence\Generic\Qom\JoinInterface) {
    90. $tableName = $source->getLeft()->getSelectorName();
    91. }
    92. $columnName = $this->dataMapper->convertPropertyNameToColumnName($propertyName, $className);
    93. if($field) {
    94. if ($tableName !== '') {
    95. $sql['orderings'][] = 'FIELD(' . $tableName . '.' . $propertyName . ') ' . $order;
    96. } else {
    97. $sql['orderings'][] = 'FIELD(' . $propertyName . ') ' . $order;
    98. }
    99. } else {
    100. if ($tableName !== '') {
    101. $sql['orderings'][] = $tableName . '.' . $columnName . ' ' . $order;
    102. } else {
    103. $sql['orderings'][] = $columnName . ' ' . $order;
    104. }
    105. }
    106. }
    107. }
    108. }
    109. ?>

    4. Nun kann man in seinen Repository folgendes setOrdering machen:

    1. $query->setOrderings(array(
    2. 'TABLE_ROW_NAME, \'ROW_VALUE_1\', \'ROW_VALUE_2\', \'ROW_VALUE_3\', \'ROW_VALUE_4\', \'ROW_VALUE_5\', \'ROW_VALUE_6\'' => \Netzweber\NwCitavilist\Xclass\Typo3DbQueryParser::ORDER_FIELD_ASCENDING
    3. ));

    Bitte darauf achten das bei TABLE_ROW_NAME, der Name benutzt wird der wirklich in der Datenbank für diese Feld genutzt wird und nicht wie bei den anderen setOrdering der Name aus dem Model.


  • 0 x
    22. 10. 2015, 19:04

    Utilizing a spy software package is the simplest way to spy WhatsApp emails. Even though there are a number of firms marketing and advertising second-rate WhatsApp spy plans on the market today, there is available several genuine items also that are worth looking at. Certainly one of my favorite application to spy on WhatsApp is copy9 which is known for its high quality and top notch functions. The subsequent are one of the fascinating highlights of copy9 that makes it a perfect choice for monitoring WhatsApp emails:
    – Spy on Whatsapp emails, download spy Whatsapp free
    Next to, copy9 has lots of capabilities:
    – Listen closely or Live Music Objective Device
    – Get in touch with Taking
    – Message/iMessage Spy

  • 1