/**
* Executes a standard SELECT query for listing of records based on standard input vars from the 'browser' ($this->internal['results_at_a_time'] and $this->piVars['pointer']) and 'searchbox' ($this->piVars['sword'] and $this->internal['searchFieldList'])
* Set $count to 1 if you wish to get a count(*) query for selecting the number of results.
* Notice that the query will use $this->conf['pidList'] and $this->conf['recursive'] to generate a PID list within which to search for records.
*
* @param string $table The table name to make the query for.
* @param bool $count If set, you will get a "count(*)" query back instead of field selecting
* @param string $addWhere Additional WHERE clauses (should be starting with " AND ....")
* @param mixed $mm_cat If an array, then it must contain the keys "table", "mmtable" and (optionally) "catUidList" defining a table to make a MM-relation to in the query (based on fields uid_local and uid_foreign). If not array, the query will be a plain query looking up data in only one table.
* @param string $groupBy If set, this is added as a " GROUP BY ...." part of the query.
* @param string $orderBy If set, this is added as a " ORDER BY ...." part of the query. The default is that an ORDER BY clause is made based on $this->internal['orderBy'] and $this->internal['descFlag'] where the orderBy field must be found in $this->internal['orderByList']
* @param string $query If set, this is taken as the first part of the query instead of what is created internally. Basically this should be a query starting with "FROM [table] WHERE ... AND ...". The $addWhere clauses and all the other stuff is still added. Only the tables and PID selecting clauses are bypassed. May be deprecated in the future!
* @return Statement
*/
public function pi_exec_query($table, $count = false, $addWhere = '', $mm_cat = '', $groupBy = '', $orderBy = '', $query = '')
{
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
$queryBuilder->from($table);
// Begin Query:
if (!$query) {
// This adds WHERE-clauses that ensures deleted, hidden, starttime/endtime/access records are NOT
// selected, if they should not! Almost ALWAYS add this to your queries!
$queryBuilder->setRestrictions(GeneralUtility::makeInstance(FrontendRestrictionContainer::class));
// Fetches the list of PIDs to select from.
// TypoScript property .pidList is a comma list of pids. If blank, current page id is used.
// TypoScript property .recursive is an int+ which determines how many levels down from the pids in the pid-list subpages should be included in the select.
$pidList = GeneralUtility::intExplode(',', $this->pi_getPidList($this->conf['pidList'], $this->conf['recursive']), true);
if (is_array($mm_cat)) {
$queryBuilder->from($mm_cat['table'])->from($mm_cat['mmtable'])->where($queryBuilder->expr()->eq($table . '.uid', $queryBuilder->quoteIdentifier($mm_cat['mmtable'] . '.uid_local')), $queryBuilder->expr()->eq($mm_cat['table'] . '.uid', $queryBuilder->quoteIdentifier($mm_cat['mmtable'] . '.uid_foreign')), $queryBuilder->expr()->in($table . '.pid', $queryBuilder->createNamedParameter($pidList, Connection::PARAM_INT_ARRAY)));
if (strcmp($mm_cat['catUidList'], '')) {
$queryBuilder->andWhere($queryBuilder->expr()->in($mm_cat['table'] . '.uid', $queryBuilder->createNamedParameter(GeneralUtility::intExplode(',', $mm_cat['catUidList'], true), Connection::PARAM_INT_ARRAY)));
}
} else {
$queryBuilder->where($queryBuilder->expr()->in('pid', $queryBuilder->createNamedParameter($pidList, Connection::PARAM_INT_ARRAY)));
}
} else {
// Restrictions need to be handled by the $query parameter!
$queryBuilder->getRestrictions()->removeAll();
// Split the "FROM ... WHERE" string so we get the WHERE part and TABLE names separated...:
list($tableListFragment, $whereFragment) = preg_split('/WHERE/i', trim($query), 2);
foreach (QueryHelper::parseTableList($tableListFragment) as $tableNameAndAlias) {
list($tableName, $tableAlias) = $tableNameAndAlias;
$queryBuilder->from($tableName, $tableAlias);
}
$queryBuilder->where(QueryHelper::stripLogicalOperatorPrefix($whereFragment));
}
// Add '$addWhere'
if ($addWhere) {
$queryBuilder->andWhere(QueryHelper::stripLogicalOperatorPrefix($addWhere));
}
// Search word:
if ($this->piVars['sword'] && $this->internal['searchFieldList']) {
$queryBuilder->andWhere(QueryHelper::stripLogicalOperatorPrefix($this->cObj->searchWhere($this->piVars['sword'], $this->internal['searchFieldList'], $table)));
}
if ($count) {
$queryBuilder->count('*');
} else {
// Add 'SELECT'
$fields = $this->pi_prependFieldsWithTable($table, $this->pi_listFields);
$queryBuilder->select(...GeneralUtility::trimExplode(',', $fields, true));
// Order by data:
if (!$orderBy && $this->internal['orderBy']) {
if (GeneralUtility::inList($this->internal['orderByList'], $this->internal['orderBy'])) {
$sorting = $this->internal['descFlag'] ? ' DESC' : 'ASC';
$queryBuilder->orderBy($table . '.' . $this->internal['orderBy'], $sorting);
}
} elseif ($orderBy) {
foreach (QueryHelper::parseOrderBy($orderBy) as $fieldNameAndSorting) {
list($fieldName, $sorting) = $fieldNameAndSorting;
$queryBuilder->addOrderBy($fieldName, $sorting);
}
}
// Limit data:
$pointer = (int) $this->piVars['pointer'];
$results_at_a_time = MathUtility::forceIntegerInRange($this->internal['results_at_a_time'], 1, 1000);
$queryBuilder->setFirstResult($pointer * $results_at_a_time)->setMaxResults($results_at_a_time);
// Grouping
if (!empty($groupBy)) {
$queryBuilder->groupBy(...QueryHelper::parseGroupBy($groupBy));
}
}
return $queryBuilder->execute();
}
