/** @test */
public function buildProxyQueryWithSimpleQuery()
{
$proxyFilter = $this->buildAccessibleProxyFilter();
$realQuery = new Tx_PtExtlist_Domain_QueryObject_Query();
$realQuery->addCriteria(new Tx_PtExtlist_Domain_QueryObject_SimpleCriteria('realField', '100', '>'));
$proxyQuery = $proxyFilter->_call('buildProxyQuery', $realQuery);
/* @var $proxyQuery Tx_PtExtlist_Domain_QueryObject_Query */
$this->assertTrue(is_a($proxyQuery, 'Tx_PtExtlist_Domain_QueryObject_Query'));
$firstCriteria = current($proxyQuery->getCriterias());
/* @var $firstCriteria Tx_PtExtlist_Domain_QueryObject_SimpleCriteria */
$this->assertTrue(is_a($firstCriteria, 'Tx_PtExtlist_Domain_QueryObject_SimpleCriteria'));
$this->assertEquals($firstCriteria->getField(), 'tableName1.fieldName1');
$this->assertEquals($firstCriteria->getOperator(), '>');
$this->assertEquals($firstCriteria->getValue(), '100');
}
/**
* Renders a link for given header and sortingFields
*
* @param Tx_PtExtlist_Domain_Model_List_Header_HeaderColumn $header
* @param array $fieldAndDirection List of fields and direction for which we want to generate sorting link {field: fieldName, direction: sortingDirection}
* @param string $action Rendered link for sorting action
* @param int $pageUid target page. See TypoLink destination
* @param int $pageType type of the target page. See typolink.parameter
* @param bool $noCache set this to disable caching for the target page. You should not need this.
* @param bool $noCacheHash set this to supress the cHash query parameter created by TypoLink. You should not need this.
* @param string $section the anchor to be added to the URI
* @param string $format The requested format, e.g. ".html
* @param bool $linkAccessRestrictedPages If set, links pointing to access restricted pages will still link to the page even though the page cannot be accessed.
* @param array $additionalParams additional query parameters that won't be prefixed like $arguments (overrule $arguments)
* @param bool $absolute If set, the URI of the rendered link is absolute
* @param bool $addQueryString If set, the current query parameters will be kept in the URI
* @param array $argumentsToBeExcludedFromQueryString arguments to be removed from the URI. Only active if $addQueryString = TRUE
* @param string $addQueryStringMethod Set which parameters will be kept. Only active if $addQueryString = TRUE
* @return string Rendered link
*/
public function render(Tx_PtExtlist_Domain_Model_List_Header_HeaderColumn $header, array $fieldAndDirection, $action = 'sort', $pageUid = null, $pageType = 0, $noCache = false, $noCacheHash = false, $section = '', $format = '', $linkAccessRestrictedPages = false, array $additionalParams = array(), $absolute = false, $addQueryString = false, array $argumentsToBeExcludedFromQueryString = array(), $addQueryStringMethod = null)
{
$sortingFieldParams = array();
$sortingDirection = Tx_PtExtlist_Domain_QueryObject_Query::invertSortingState($fieldAndDirection['currentDirection']);
$sortingFieldParams[] = $fieldAndDirection['field'] . ':' . $sortingDirection;
# echo "current direction for field " . $fieldAndDirection['field'] . " = " . $fieldAndDirection['currentDirection'] . " link direction = " . $sortingDirection;
$sortingFieldParam = implode(';', $sortingFieldParams);
$gpArrayViewHelper = new Tx_PtExtlist_ViewHelpers_Namespace_GPArrayViewHelper();
$argumentArray = $gpArrayViewHelper->buildObjectValueArray($header, 'sortingFields', $sortingFieldParam);
$this->sessionPersistenceManagerBuilder->getInstance()->addSessionRelatedArguments($argumentArray);
return parent::render($action, $argumentArray, null, null, null, $pageUid, $pageType, $noCache, $noCacheHash, $section, $format, $linkAccessRestrictedPages, $additionalParams, $absolute, $addQueryString, $argumentsToBeExcludedFromQueryString, $addQueryStringMethod);
}
/**
* Build the group data query to retrieve the group data
*
* @param array Tx_PtExtlist_Domain_Configuration_Data_Fields_FieldConfig $fields
* @return \Tx_PtExtlist_Domain_QueryObject_Query
*/
protected function buildGroupDataQuery($fields)
{
$groupDataQuery = new Tx_PtExtlist_Domain_QueryObject_Query();
foreach ($fields as $selectField) {
$groupDataQuery->addField(Tx_PtExtlist_Utility_DbUtils::getAliasedSelectPartByFieldConfig($selectField));
}
if ($this->additionalTables != '') {
$groupDataQuery->addFrom($this->additionalTables);
}
$groupDataQuery->addSorting('elementCount', Tx_PtExtlist_Domain_QueryObject_Query::SORTINGSTATE_DESC);
if ($this->elementCountField !== NULL) {
$groupDataQuery->addField(sprintf('%s as elementCount', $this->elementCountField->getTableFieldCombined()));
} else {
// TODO only works with SQL!
$groupDataQuery->addField(sprintf('count("%s") as elementCount', $this->filterField->getTableFieldCombined()));
}
$groupDataQuery->addGroupBy($this->filterConfig->getFieldIdentifier()->getItemByIndex(0)->getIdentifier());
return $groupDataQuery;
}
/**
* Build the group data query to retrieve the group data
*
* @param array Tx_PtExtlist_Domain_Configuration_Data_Fields_FieldConfig $fields
* @return string
*/
protected function buildGroupDataQuery($fields)
{
$groupDataQuery = new Tx_PtExtlist_Domain_QueryObject_Query();
$displayField = $this->displayFields->getItemByIndex(0);
if ($this->additionalTables != '') {
$groupDataQuery->addFrom($this->additionalTables);
}
//TODO only works with SQL!
$groupDataQuery->addField(sprintf('UPPER(LEFT(%1$s,1)) as firstLetter', $displayField->getTableFieldCombined()));
$groupDataQuery->addSorting('firstLetter', Tx_PtExtlist_Domain_QueryObject_Query::SORTINGSTATE_ASC);
if ($this->showRowCount) {
// TODO only works with SQL!
$groupDataQuery->addField(sprintf('count("%s") as rowCount', $this->filterField->getTableFieldCombined()));
}
$groupDataQuery->addGroupBy('firstLetter');
return $groupDataQuery;
}
/**
* Renders a link for given header
*
* @param Tx_PtExtlist_Domain_Model_List_Header_HeaderColumn $header
* @param string $action Rendered link for sorting action
* @param int $pageUid
* @param int $pageType
* @param bool $noCache
* @param bool $noCacheHash
* @param string $section
* @param string $format
* @param bool $linkAccessRestrictedPages
* @param array $additionalParams
* @param bool $absolute
* @param bool $addQueryString
* @param array $argumentsToBeExcludedFromQueryString
* @param string $addQueryStringMethod Set which parameters will be kept. Only active if $addQueryString = TRUE
* @return string
*/
public function render(Tx_PtExtlist_Domain_Model_List_Header_HeaderColumn $header, $action = 'sort', $pageUid = NULL, $pageType = 0, $noCache = FALSE, $noCacheHash = FALSE, $section = '', $format = '', $linkAccessRestrictedPages = FALSE, array $additionalParams = array(), $absolute = FALSE, $addQueryString = FALSE, array $argumentsToBeExcludedFromQueryString = array(), $addQueryStringMethod = NULL)
{
$sortingFieldParams = array();
// We generate sorting parameters for every sorting field configured for this column
foreach ($header->getColumnConfig()->getSortingConfig() as $sortingFieldConfig) {
/* @var $sortingFieldConfig Tx_PtExtlist_Domain_Configuration_Columns_SortingConfig */
$newSortingDirection = $header->getSortingDirectionForField($sortingFieldConfig->getField()) != 0 ? Tx_PtExtlist_Domain_QueryObject_Query::invertSortingState($header->getSortingDirectionForField($sortingFieldConfig->getField())) : $sortingFieldConfig->getDirection();
$sortingFieldParams[] = $sortingFieldConfig->getField() . ':' . $newSortingDirection;
}
$sortingFieldParam = implode(';', $sortingFieldParams);
// We set sortingDirectionParameter for children of this viewHelper
$this->templateVariableContainer->add('sortingDirection', $this->getSortingDirectionForHeader($header));
#echo "Sorting field param for " . $header->getColumnConfig()->getColumnIdentifier() . " = " . $sortingFieldParam . "<br>";
#echo "Sorting direction for " . $header->getColumnConfig()->getColumnIdentifier() . " = ". $this->getSortingDirectionForHeader($header) . "<br>";
$gpArrayViewHelper = new Tx_PtExtlist_ViewHelpers_Namespace_GPArrayViewHelper();
$argumentArray = $gpArrayViewHelper->buildObjectValueArray($header, 'sortingFields', $sortingFieldParam);
$this->sessionPersistenceManagerBuilder->getInstance()->addSessionRelatedArguments($argumentArray);
$output = parent::render($action, $argumentArray, NULL, NULL, NULL, $pageUid, $pageType, $noCache, $noCacheHash, $section, $format, $linkAccessRestrictedPages, $additionalParams, $absolute, $addQueryString, $argumentsToBeExcludedFromQueryString, $addQueryStringMethod);
$this->templateVariableContainer->remove('sortingDirection');
return $output;
}
/**
* Returns query object that reflects sorting of this sorting state
*
* @return Tx_PtExtlist_Domain_QueryObject_Query
*/
public function getSortingQuery()
{
$sortingQuery = new Tx_PtExtlist_Domain_QueryObject_Query();
$sortingQuery->addSorting($this->field->getIdentifier(), $this->direction);
return $sortingQuery;
}
/**
* Build the group data query to retrieve the group data
*
* @param array Tx_PtExtlist_Domain_Configuration_Data_Fields_FieldConfig $fields
* @return Tx_PtExtlist_Domain_QueryObject_Query
*/
protected function buildGroupDataQuery($fields)
{
$groupDataQuery = new Tx_PtExtlist_Domain_QueryObject_Query();
foreach ($fields as $selectField) {
$groupDataQuery->addField(Tx_PtExtlist_Utility_DbUtils::getAliasedSelectPartByFieldConfig($selectField));
}
if ($this->additionalTables != '') {
$groupDataQuery->addFrom($this->additionalTables);
}
foreach ($this->displayFields as $displayField) {
/* @var $displayField Tx_PtExtlist_Domain_Configuration_Data_Fields_FieldConfig */
$groupDataQuery->addSorting($displayField->getIdentifier(), Tx_PtExtlist_Domain_QueryObject_Query::SORTINGSTATE_ASC);
}
if ($this->showRowCount) {
// TODO only works with SQL!
$groupDataQuery->addField(sprintf('count("%s") as rowCount', $this->filterField->getTableFieldCombined()));
}
$groupFields = array();
foreach ($this->getFieldsRequiredToBeSelected() as $field) {
/* @var $field Tx_PtExtlist_Domain_Configuration_Data_Fields_FieldConfig */
$groupFields[] = $field->getIdentifier();
}
$groupDataQuery->addGroupBy(implode(',', $groupFields));
return $groupDataQuery;
}
/**
* Build the group data query to retrieve the group data
*
* @return Tx_PtExtlist_Domain_QueryObject_Query
*/
protected function buildQuery()
{
$query = new Tx_PtExtlist_Domain_QueryObject_Query();
foreach ($this->dateFieldConfigs as $key => $selectField) {
if ($selectField['start'] == $selectField['end']) {
$aliasedSelectPart = Tx_PtExtlist_Utility_DbUtils::getSelectPartByFieldConfig($selectField['start']) . ' AS ' . $selectField['start']->getIdentifier();
$query->addField($aliasedSelectPart);
} else {
$aliasedSelectPartStart = Tx_PtExtlist_Utility_DbUtils::getSelectPartByFieldConfig($selectField['start']) . ' AS ' . $this->buildFieldAlias($key, 'start');
$aliasedSelectPartEnd = Tx_PtExtlist_Utility_DbUtils::getSelectPartByFieldConfig($selectField['end']) . ' AS ' . $this->buildFieldAlias($key, 'end');
$query->addField($aliasedSelectPartStart);
$query->addField($aliasedSelectPartEnd);
}
}
return $query;
}
protected function getHeaderColumnBySortingFieldAndDirection($field, $direction)
{
$sortingQuery = new Tx_PtExtlist_Domain_QueryObject_Query();
$sortingQuery->addSorting($field, $direction);
$headerMock = $this->getMock('Tx_PtExtlist_Domain_Model_List_Header_HeaderColumn', array('getSortingQuery'));
$headerMock->expects($this->once())->method('getSortingQuery')->will($this->returnValue($sortingQuery));
return $headerMock;
}
/**
* @test
* @dataProvider filterValueDataProvider
*
* @param $value
* @param $andToken
* @param $orToken
* @param $expected
* @param string $expectedSQL
*/
public function buildFilterCriteria($value, $andToken, $orToken, $expected, $expectedSQL = '')
{
$additionalSettings = array('andToken' => $andToken, 'orToken' => $orToken);
$filter = $this->getStringFilterInstance($additionalSettings);
$filter->_set('filterValue', $value);
$filter->_call('initFilterByTsConfig');
$fieldConfig = new Tx_PtExtlist_Domain_Configuration_Data_Fields_FieldConfig($this->configurationBuilderMock, 'testField', array('table' => 'table1', 'field' => 'field1'));
$actualCriteria = $filter->_call('buildFilterCriteria', $fieldConfig);
$this->assertInstanceOf('Tx_PtExtlist_Domain_QueryObject_Criteria', $actualCriteria);
$query = new Tx_PtExtlist_Domain_QueryObject_Query();
$query->addCriteria($actualCriteria);
$actualSQL = Tx_PtExtlist_Domain_DataBackend_MySqlDataBackend_MySqlInterpreter_MySqlInterpreter::getCriterias($query);
$this->assertEquals($expectedSQL, $actualSQL);
}
/**
* Set the fieldIdentifier of the proxy filter as fieldIdentifier in the filterQuery
*
* @param Tx_PtExtlist_Domain_QueryObject_Query $filterQuery
* @throws Exception if filter criteria is not a simple criteria
* @return Tx_PtExtlist_Domain_QueryObject_Query $proxyQuery
*/
protected function buildProxyQuery(Tx_PtExtlist_Domain_QueryObject_Query $filterQuery)
{
$proxyQuery = new Tx_PtExtlist_Domain_QueryObject_Query();
$criterias = $filterQuery->getCriterias();
foreach ($criterias as $criteria) {
/* @var $criteria Tx_PtExtlist_Domain_QueryObject_SimpleCriteria */
if (get_class($criteria) != 'Tx_PtExtlist_Domain_QueryObject_SimpleCriteria') {
throw new Exception('Only simple criterias are supported at the moment in proxy filters.', 1302864386);
}
$proxyQuery->addCriteria(new Tx_PtExtlist_Domain_QueryObject_SimpleCriteria($this->filterConfig->getFieldIdentifier()->getItemByIndex(0)->getTableFieldCombined(), $criteria->getValue(), $criteria->getOperator()));
}
return $proxyQuery;
}
public function setup()
{
$this->queryObject = new Tx_PtExtlist_Domain_QueryObject_Query();
// select
$this->queryObject->addField('name');
$this->queryObject->addField('age');
// from
$this->queryObject->addFrom('Users u');
$this->queryObject->addFrom('Groups g');
// where
$this->queryObject->addCriteria(Tx_PtExtlist_Domain_QueryObject_Criteria::equals('name', 'Michael'));
// group by
$this->queryObject->addGroupBy('name');
$this->queryObject->addGroupBy('company');
// limit
$this->queryObject->setLimit('10:10');
// sortings
$this->queryObject->addSorting('test');
$this->queryObject->addSorting('test2', Tx_PtExtlist_Domain_QueryObject_Query::SORTINGSTATE_DESC);
}
public function testGetSortings()
{
$query = new Tx_PtExtlist_Domain_QueryObject_Query();
$query->addSorting('test1', Tx_PtExtlist_Domain_QueryObject_Query::SORTINGSTATE_ASC);
$query->addSorting('test2', Tx_PtExtlist_Domain_QueryObject_Query::SORTINGSTATE_DESC);
$sortings = $query->getSortings();
$this->assertTrue($sortings['test1'] == Tx_PtExtlist_Domain_QueryObject_Query::SORTINGSTATE_ASC);
$this->assertTrue($sortings['test2'] == Tx_PtExtlist_Domain_QueryObject_Query::SORTINGSTATE_DESC);
}
/**
* Returns a query object with sortings for this sorting state collection
*
* @return Tx_PtExtlist_Domain_QueryObject_Query
*/
public function getSortingsQuery()
{
$sortingsQuery = new Tx_PtExtlist_Domain_QueryObject_Query();
foreach ($this->itemsArr as $sortingState) {
/* @var $sortingState Tx_PtExtlist_Domain_Model_Sorting_SortingState */
$sortingsQuery->addSorting($sortingState->getField()->getIdentifier(), $sortingState->getDirection());
}
return $sortingsQuery;
}
/**
* Build filter query for current filter state
*
*/
protected function buildFilterQuery()
{
if ($this->filterConfig->getDisableFilterQuery()) {
return;
}
$criteria = null;
if ($this->isActive) {
$criteria = $this->buildFilterCriteriaForAllFields();
}
if ($criteria) {
$this->filterQuery->unsetCriterias();
if ($this->invert) {
$this->filterQuery->addCriteria(Tx_PtExtlist_Domain_QueryObject_Criteria::notOp($criteria));
} else {
$this->filterQuery->addCriteria($criteria);
}
}
}
/**
* Builds extlist query object excluding criterias from filters given by parameter
*
* @param array $excludeFilters Array of <filterbox>.<filter> identifiers to be excluded from query
* @return Tx_PtExtlist_Domain_QueryObject_Query
*/
protected function buildGenericQueryExcludingFilters(array $excludeFilters = array())
{
$query = new Tx_PtExtlist_Domain_QueryObject_Query();
foreach ($this->filterboxCollection as $filterbox) {
/* @var $filterbox Tx_PtExtlist_Domain_Model_Filter_Filterbox */
foreach ($filterbox as $filter) {
/* @var $filter Tx_PtExtlist_Domain_Model_Filter_FilterInterface */
if (!is_array($excludeFilters[$filterbox->getfilterboxIdentifier()]) || !in_array($filter->getFilterIdentifier(), $excludeFilters[$filterbox->getfilterboxIdentifier()])) {
$criterias = $filter->getFilterQuery()->getCriterias();
foreach ($criterias as $criteria) {
$query->addCriteria($criteria);
}
}
}
}
return $query;
}
/**
* Sets sortings on an extbase query by given query object. Returns manipulated extbase query.
*
* @param Tx_PtExtlist_Domain_QueryObject_Query $query Query object to get sortings from
* @param \TYPO3\CMS\Extbase\Persistence\Generic\Query $extbaseQuery Query object to set sortings on
* @return \TYPO3\CMS\Extbase\Persistence\Generic\Query Manipulated ExtBase query object
*/
public static function setSortingOnExtBaseQueryByQueryObject(Tx_PtExtlist_Domain_QueryObject_Query $query, \TYPO3\CMS\Extbase\Persistence\Generic\Query $extbaseQuery)
{
$sortings = $query->getSortings();
$extBaseSortings = array();
foreach ($sortings as $field => $direction) {
/* sorting is array('field' => 'Direction: 1 | -1') */
$extBaseDirection = $direction == Tx_PtExtlist_Domain_QueryObject_Query::SORTINGSTATE_ASC ? \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_ASCENDING : \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_DESCENDING;
$extBaseSortings[$field] = $extBaseDirection;
}
$extbaseQuery->setOrderings($extBaseSortings);
return $extbaseQuery;
}
/**
* Returns translated group by fields with out the 'GROUP BY'
*
* @param Tx_PtExtlist_Domain_QueryObject_Query $query
* @return string group by part
*/
public static function getGroupBy(Tx_PtExtlist_Domain_QueryObject_Query $query)
{
$groupByString = implode(', ', $query->getGroupBy());
return $groupByString;
}
