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