/** * Creates and returns a SELECT query for records from $table and with conditions based on the configuration in the $conf array * Implements the "select" function in TypoScript * * @param string $table See ->exec_getQuery() * @param array $conf See ->exec_getQuery() * @param bool $returnQueryArray If set, the function will return the query not as a string but array with the various parts. RECOMMENDED! * @return mixed A SELECT query if $returnQueryArray is FALSE, otherwise the SELECT query in an array as parts. * @throws \RuntimeException * @throws \InvalidArgumentException * @access private * @see CONTENT(), numRows() */ public function getQuery($table, $conf, $returnQueryArray = false) { // Resolve stdWrap in these properties first $properties = ['pidInList', 'uidInList', 'languageField', 'selectFields', 'max', 'begin', 'groupBy', 'orderBy', 'join', 'leftjoin', 'rightjoin', 'recursive', 'where']; foreach ($properties as $property) { $conf[$property] = trim(isset($conf[$property . '.']) ? $this->stdWrap($conf[$property], $conf[$property . '.']) : $conf[$property]); if ($conf[$property] === '') { unset($conf[$property]); } if (isset($conf[$property . '.'])) { // stdWrapping already done, so remove the sub-array unset($conf[$property . '.']); } } // Handle PDO-style named parameter markers first $queryMarkers = $this->getQueryMarkers($table, $conf); // Replace the markers in the non-stdWrap properties foreach ($queryMarkers as $marker => $markerValue) { $properties = ['uidInList', 'selectFields', 'where', 'max', 'begin', 'groupBy', 'orderBy', 'join', 'leftjoin', 'rightjoin']; foreach ($properties as $property) { if ($conf[$property]) { $conf[$property] = str_replace('###' . $marker . '###', $markerValue, $conf[$property]); } } } // Construct WHERE clause: // Handle recursive function for the pidInList if (isset($conf['recursive'])) { $conf['recursive'] = (int) $conf['recursive']; if ($conf['recursive'] > 0) { $pidList = GeneralUtility::trimExplode(',', $conf['pidInList'], true); array_walk($pidList, function (&$storagePid) { if ($storagePid === 'this') { $storagePid = $this->getTypoScriptFrontendController()->id; } if ($storagePid > 0) { $storagePid = -$storagePid; } }); $expandedPidList = []; foreach ($pidList as $value) { // Implementation of getTreeList allows to pass the id negative to include // it into the result otherwise only childpages are returned $expandedPidList = array_merge(GeneralUtility::intExplode(',', $this->getTreeList($value, $conf['recursive'])), $expandedPidList); } $conf['pidInList'] = implode(',', $expandedPidList); } } if ((string) $conf['pidInList'] === '') { $conf['pidInList'] = 'this'; } $queryParts = $this->getQueryConstraints($table, $conf); $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table); // @todo Check against getQueryConstraints, can probably use FrontendRestrictions // @todo here and remove enableFields there. $queryBuilder->getRestrictions()->removeAll(); $queryBuilder->select('*')->from($table); if ($queryParts['where']) { $queryBuilder->where($queryParts['where']); } if ($queryParts['groupBy']) { $queryBuilder->groupBy(...$queryParts['groupBy']); } if (is_array($queryParts['orderBy'])) { foreach ($queryParts['orderBy'] as $orderBy) { $queryBuilder->addOrderBy(...$orderBy); } } // Fields: if ($conf['selectFields']) { $queryBuilder->selectLiteral($this->sanitizeSelectPart($conf['selectFields'], $table)); } // Setting LIMIT: $error = false; if ($conf['max'] || $conf['begin']) { // Finding the total number of records, if used: if (strpos(strtolower($conf['begin'] . $conf['max']), 'total') !== false) { $countQueryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table); $countQueryBuilder->getRestrictions()->removeAll(); $countQueryBuilder->count('*')->from($table)->where($queryParts['where']); if ($queryParts['groupBy']) { $countQueryBuilder->groupBy(...$queryParts['groupBy']); } try { $count = $countQueryBuilder->execute()->fetchColumn(0); $conf['max'] = str_ireplace('total', $count, $conf['max']); $conf['begin'] = str_ireplace('total', $count, $conf['begin']); } catch (DBALException $e) { $this->getTimeTracker()->setTSlogMessage($e->getPrevious()->getMessage()); $error = true; } } if (!$error) { $conf['begin'] = MathUtility::forceIntegerInRange(ceil($this->calc($conf['begin'])), 0); $conf['max'] = MathUtility::forceIntegerInRange(ceil($this->calc($conf['max'])), 0); if ($conf['begin'] > 0) { $queryBuilder->setFirstResult($conf['begin']); } $queryBuilder->setMaxResults($conf['max'] ?: 100000); } } if (!$error) { // Setting up tablejoins: if ($conf['join']) { $joinParts = QueryHelper::parseJoin($conf['join']); $queryBuilder->join($table, $joinParts['tableName'], $joinParts['tableAlias'], $joinParts['joinCondition']); } elseif ($conf['leftjoin']) { $joinParts = QueryHelper::parseJoin($conf['leftjoin']); $queryBuilder->leftJoin($table, $joinParts['tableName'], $joinParts['tableAlias'], $joinParts['joinCondition']); } elseif ($conf['rightjoin']) { $joinParts = QueryHelper::parseJoin($conf['rightjoin']); $queryBuilder->rightJoin($table, $joinParts['tableName'], $joinParts['tableAlias'], $joinParts['joinCondition']); } // Convert the QueryBuilder object into a SQL statement. $query = $queryBuilder->getSQL(); // Replace the markers in the queryParts to handle stdWrap enabled properties foreach ($queryMarkers as $marker => $markerValue) { // @todo Ugly hack that needs to be cleaned up, with the current architecture // @todo for exec_Query / getQuery it's the best we can do. $query = str_replace('###' . $marker . '###', $markerValue, $query); foreach ($queryParts as $queryPartKey => &$queryPartValue) { $queryPartValue = str_replace('###' . $marker . '###', $markerValue, $queryPartValue); } unset($queryPartValue); } return $returnQueryArray ? $this->getQueryArray($queryBuilder) : $query; } return ''; }