/**
* @test
* @dataProvider parseGroupByDataProvider
* @param string $input
* @param array $expectedResult
*/
public function parseGroupByTest(string $input, array $expectedResult)
{
$this->assertSame($expectedResult, QueryHelper::parseGroupBy($input));
}
/**
* Will select all records from the "category table", $table, and return them in an array.
*
* @param string $table The name of the category table to select from.
* @param int $pid The page from where to select the category records.
* @param string $whereClause Optional additional WHERE clauses put in the end of the query. DO NOT PUT IN GROUP BY, ORDER BY or LIMIT!
* @param string $groupBy Optional GROUP BY field(s), if none, supply blank string.
* @param string $orderBy Optional ORDER BY field(s), if none, supply blank string.
* @param string $limit Optional LIMIT value ([begin,]max), if none, supply blank string.
* @return array The array with the category records in.
*/
public function pi_getCategoryTableContents($table, $pid, $whereClause = '', $groupBy = '', $orderBy = '', $limit = '')
{
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
$queryBuilder->setRestrictions(GeneralUtility::makeInstance(FrontendRestrictionContainer::class));
$queryBuilder->select('*')->from($table)->where($queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter($pid, \PDO::PARAM_INT)), QueryHelper::stripLogicalOperatorPrefix($whereClause));
if (!empty($orderBy)) {
foreach (QueryHelper::parseOrderBy($orderBy) as $fieldNameAndSorting) {
list($fieldName, $sorting) = $fieldNameAndSorting;
$queryBuilder->addOrderBy($fieldName, $sorting);
}
}
if (!empty($groupBy)) {
$queryBuilder->groupBy(...QueryHelper::parseGroupBy($groupBy));
}
if (!empty($limit)) {
$limitValues = GeneralUtility::intExplode(',', $limit, true);
if (count($limitValues) === 1) {
$queryBuilder->setMaxResults($limitValues[0]);
} else {
$queryBuilder->setFirstResult($limitValues[0])->setMaxResults($limitValues[1]);
}
}
$result = $queryBuilder->execute();
$outArr = [];
while ($row = $result->fetch()) {
$outArr[$row['uid']] = $row;
}
return $outArr;
}
/**
* Get select query
*
* @param string $qString
* @return bool|\mysqli_result|object
*/
public function getSelectQuery($qString = '')
{
$backendUserAuthentication = $this->getBackendUserAuthentication();
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($this->table);
if ($this->getModule()->MOD_SETTINGS['show_deleted']) {
$queryBuilder->getRestrictions()->removeAll();
} else {
$queryBuilder->getRestrictions()->removeAll()->add(GeneralUtility::makeInstance(DeletedRestriction::class));
}
$fieldList = GeneralUtility::trimExplode(',', $this->extFieldLists['queryFields'] . ',pid' . ($GLOBALS['TCA'][$this->table]['ctrl']['delete'] ? ',' . $GLOBALS['TCA'][$this->table]['ctrl']['delete'] : ''));
$queryBuilder->select(...$fieldList)->from($this->table);
if ($this->extFieldLists['queryGroup']) {
$queryBuilder->groupBy(...QueryHelper::parseGroupBy($this->extFieldLists['queryGroup']));
}
if ($this->extFieldLists['queryOrder']) {
foreach (QueryHelper::parseOrderBy($this->extFieldLists['queryOrder_SQL']) as $orderPair) {
list($fieldName, $order) = $orderPair;
$queryBuilder->addOrderBy($fieldName, $order);
}
}
if ($this->extFieldLists['queryLimit']) {
$queryBuilder->setMaxResults((int) $this->extFieldLists['queryLimit']);
}
if (!$backendUserAuthentication->isAdmin() && $GLOBALS['TYPO3_CONF_VARS']['BE']['lockBeUserToDBmounts']) {
$webMounts = $backendUserAuthentication->returnWebmounts();
$perms_clause = $backendUserAuthentication->getPagePermsClause(1);
$webMountPageTree = '';
$webMountPageTreePrefix = '';
foreach ($webMounts as $webMount) {
if ($webMountPageTree) {
$webMountPageTreePrefix = ',';
}
$webMountPageTree .= $webMountPageTreePrefix . $this->getTreeList($webMount, 999, $begin = 0, $perms_clause);
}
if ($this->table === 'pages') {
$queryBuilder->where(QueryHelper::stripLogicalOperatorPrefix($perms_clause), $queryBuilder->expr()->in('uid', $queryBuilder->createNamedParameter(GeneralUtility::intExplode(',', $webMountPageTree), Connection::PARAM_INT_ARRAY)));
} else {
$queryBuilder->where($queryBuilder->expr()->in('pid', $queryBuilder->createNamedParameter(GeneralUtility::intExplode(',', $webMountPageTree), Connection::PARAM_INT_ARRAY)));
}
}
if (!$qString) {
$qString = $this->getQuery($this->queryConfig);
}
$queryBuilder->andWhere(QueryHelper::stripLogicalOperatorPrefix($qString));
return $queryBuilder->getSQL();
}
/**
* Helper function for getQuery(), creating the WHERE clause of the SELECT query
*
* @param string $table The table name
* @param array $conf The TypoScript configuration properties
* @return array Associative array containing the prepared data for WHERE, ORDER BY and GROUP BY fragments
* @throws \InvalidArgumentException
* @see getQuery()
*/
protected function getQueryConstraints(string $table, array $conf) : array
{
// Init:
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
$expressionBuilder = $queryBuilder->expr();
$tsfe = $this->getTypoScriptFrontendController();
$constraints = [];
$pid_uid_flag = 0;
$enableFieldsIgnore = [];
$queryParts = ['where' => null, 'groupBy' => null, 'orderBy' => null];
$considerMovePlaceholders = $tsfe->sys_page->versioningPreview && $table !== 'pages' && !empty($GLOBALS['TCA'][$table]['ctrl']['versioningWS']);
if (trim($conf['uidInList'])) {
$listArr = GeneralUtility::intExplode(',', str_replace('this', $tsfe->contentPid, $conf['uidInList']));
// If move placeholder shall be considered, select via t3ver_move_id
if ($considerMovePlaceholders) {
$constraints[] = (string) $expressionBuilder->orX($expressionBuilder->in($table . '.uid', $listArr), $expressionBuilder->andX($expressionBuilder->eq($table . '.t3ver_state', (int) (string) VersionState::cast(VersionState::MOVE_PLACEHOLDER)), $expressionBuilder->in($table . '.t3ver_move_id', $listArr)));
} else {
$constraints[] = (string) $expressionBuilder->in($table . '.uid', $listArr);
}
$pid_uid_flag++;
}
// Static_* tables are allowed to be fetched from root page
if (strpos($table, 'static_') === 0) {
$pid_uid_flag++;
}
if (trim($conf['pidInList'])) {
$listArr = GeneralUtility::intExplode(',', str_replace('this', $tsfe->contentPid, $conf['pidInList']));
// Removes all pages which are not visible for the user!
$listArr = $this->checkPidArray($listArr);
if (GeneralUtility::inList($conf['pidInList'], 'root')) {
$listArr[] = 0;
}
if (GeneralUtility::inList($conf['pidInList'], '-1')) {
$listArr[] = -1;
$enableFieldsIgnore['pid'] = true;
}
if (!empty($listArr)) {
$constraints[] = $expressionBuilder->in($table . '.pid', array_map('intval', $listArr));
$pid_uid_flag++;
} else {
// If not uid and not pid then uid is set to 0 - which results in nothing!!
$pid_uid_flag = 0;
}
}
// If not uid and not pid then uid is set to 0 - which results in nothing!!
if (!$pid_uid_flag) {
$constraints[] = $expressionBuilder->eq($table . '.uid', 0);
}
$where = isset($conf['where.']) ? trim($this->stdWrap($conf['where'], $conf['where.'])) : trim($conf['where']);
if ($where) {
$constraints[] = QueryHelper::stripLogicalOperatorPrefix($where);
}
// Check if the table is translatable, and set the language field by default from the TCA information
$languageField = '';
if (!empty($conf['languageField']) || !isset($conf['languageField'])) {
if (isset($conf['languageField']) && !empty($GLOBALS['TCA'][$table]['columns'][$conf['languageField']])) {
$languageField = $conf['languageField'];
} elseif (!empty($GLOBALS['TCA'][$table]['ctrl']['languageField']) && !empty($GLOBALS['TCA'][$table]['ctrl']['transOrigPointerField'])) {
$languageField = $table . '.' . $GLOBALS['TCA'][$table]['ctrl']['languageField'];
}
}
if (!empty($languageField)) {
// The sys_language record UID of the content of the page
$sys_language_content = (int) $tsfe->sys_language_content;
if ($tsfe->sys_language_contentOL && !empty($GLOBALS['TCA'][$table]['ctrl']['transOrigPointerField'])) {
// Sys language content is set to zero/-1 - and it is expected that whatever routine processes the output will
// OVERLAY the records with localized versions!
$languageQuery = $expressionBuilder->in($languageField, [0, -1]);
// Use this option to include records that don't have a default translation
// (originalpointerfield is 0 and the language field contains the requested language)
$includeRecordsWithoutDefaultTranslation = isset($conf['includeRecordsWithoutDefaultTranslation.']) ? $this->stdWrap($conf['includeRecordsWithoutDefaultTranslation'], $conf['includeRecordsWithoutDefaultTranslation.']) : $conf['includeRecordsWithoutDefaultTranslation'];
if (trim($includeRecordsWithoutDefaultTranslation) !== '') {
$languageQuery = $expressionBuilder->orX($languageQuery, $expressionBuilder->andX($expressionBuilder->eq($GLOBALS['TCA'][$table]['ctrl']['transOrigPointerField'], 0), $expressionBuilder->eq($languageField, $sys_language_content)));
}
} else {
$languageQuery = $expressionBuilder->eq($languageField, $sys_language_content);
}
$constraints[] = $languageQuery;
}
// Enablefields
if ($table === 'pages') {
$constraints[] = QueryHelper::stripLogicalOperatorPrefix($tsfe->sys_page->where_hid_del);
$constraints[] = QueryHelper::stripLogicalOperatorPrefix($tsfe->sys_page->where_groupAccess);
} else {
$constraints[] = QueryHelper::stripLogicalOperatorPrefix($this->enableFields($table, false, $enableFieldsIgnore));
}
// MAKE WHERE:
if (count($constraints) !== 0) {
$queryParts['where'] = $expressionBuilder->andX(...$constraints);
}
// GROUP BY
if (trim($conf['groupBy'])) {
$groupBy = isset($conf['groupBy.']) ? trim($this->stdWrap($conf['groupBy'], $conf['groupBy.'])) : trim($conf['groupBy']);
$queryParts['groupBy'] = QueryHelper::parseGroupBy($groupBy);
}
// ORDER BY
if (trim($conf['orderBy'])) {
$orderByString = isset($conf['orderBy.']) ? trim($this->stdWrap($conf['orderBy'], $conf['orderBy.'])) : trim($conf['orderBy']);
$queryParts['orderBy'] = QueryHelper::parseOrderBy($orderByString);
}
// Return result:
return $queryParts;
}
/**
* Selects records based on matching a field (ei. other than UID) with a value
*
* @param string $theTable The table name to search, eg. "pages" or "tt_content
* @param string $theField The fieldname to match, eg. "uid" or "alias
* @param string $theValue The value that fieldname must match, eg. "123" or "frontpage
* @param string $whereClause Optional additional WHERE clauses put in the end of the query. DO NOT PUT IN GROUP BY, ORDER BY or LIMIT!
* @param string $groupBy Optional GROUP BY field(s). If none, supply blank string.
* @param string $orderBy Optional ORDER BY field(s). If none, supply blank string.
* @param string $limit Optional LIMIT value ([begin,]max). If none, supply blank string.
* @return mixed Returns array (the record) if found, otherwise nothing (void)
*/
public function getRecordsByField($theTable, $theField, $theValue, $whereClause = '', $groupBy = '', $orderBy = '', $limit = '')
{
if (is_array($GLOBALS['TCA'][$theTable])) {
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($theTable);
$queryBuilder->getRestrictions()->removeAll()->add(GeneralUtility::makeInstance(DeletedRestriction::class));
$queryBuilder->select('*')->from($theTable)->where($queryBuilder->expr()->eq($theField, $queryBuilder->createNamedParameter($theValue)));
if ($whereClause !== '') {
$queryBuilder->andWhere(QueryHelper::stripLogicalOperatorPrefix($whereClause));
}
if ($groupBy !== '') {
$queryBuilder->groupBy(QueryHelper::parseGroupBy($groupBy));
}
if ($orderBy !== '') {
foreach (QueryHelper::parseOrderBy($orderBy) as $orderPair) {
list($fieldName, $order) = $orderPair;
$queryBuilder->addOrderBy($fieldName, $order);
}
}
if ($limit !== '') {
if (strpos($limit, ',')) {
$limitOffsetAndMax = GeneralUtility::intExplode(',', $limit);
$queryBuilder->setFirstResult((int) $limitOffsetAndMax[0]);
$queryBuilder->setMaxResults((int) $limitOffsetAndMax[1]);
} else {
$queryBuilder->setMaxResults((int) $limit);
}
}
$rows = $queryBuilder->execute()->fetchAll();
if (!empty($rows)) {
return $rows;
}
}
return null;
}
