/**
* 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 '';
}
