/**
* @param Tx_PtExtlist_Domain_QueryObject_Query $groupDataQuery
* @param array $excludeFilters
* @param Tx_PtExtlist_Domain_Configuration_Filters_FilterConfig $filterConfig
* @return string
*/
protected function buildGroupDataQuery(Tx_PtExtlist_Domain_QueryObject_Query $groupDataQuery, $excludeFilters = array(), Tx_PtExtlist_Domain_Configuration_Filters_FilterConfig $filterConfig = null)
{
$this->buildQuery();
$selectPart = 'SELECT ' . $this->queryInterpreter->getSelectPart($groupDataQuery);
$fromPart = $this->listQueryParts['FROM'];
$groupPart = count($groupDataQuery->getGroupBy()) > 0 ? ' GROUP BY ' . $this->queryInterpreter->getGroupBy($groupDataQuery) : '';
$sortingPart = count($groupDataQuery->getSortings()) > 0 ? ' ORDER BY ' . $this->queryInterpreter->getSorting($groupDataQuery) : '';
/**
* If this list is grouped. There are two cases
* 1. We want to show the rowCount. In this case we have to build the list first and then use this list as
* source to calculate the selectable rows and the count. This has the drawback, that options are not displayed, if grouped within the list.
* 2. We do not need the rowCount. In this case, we exchange the original grouping fields with the fields needed by the filter.
*/
if ($this->listQueryParts['GROUPBY'] && $filterConfig->getShowRowCount()) {
$selectPart = $this->convertTableFieldToAlias($selectPart);
$groupPart = $this->convertTableFieldToAlias($groupPart);
$sortingPart = $this->convertTableFieldToAlias($sortingPart);
$filterWherePart = $this->buildWherePart($excludeFilters);
$filterWherePart = $filterWherePart ? ' WHERE ' . $filterWherePart . " \n" : '';
// if the list has a group by clause itself, we have to use the listQuery as subQuery
$fromPart = ' FROM (' . $this->listQueryParts['SELECT'] . $this->listQueryParts['FROM'] . $filterWherePart . $this->listQueryParts['GROUPBY'] . ') AS SUBQUERY ';
unset($filterWherePart);
// we confined the subquery, so we dont need this in the group query
} else {
$filterWherePart = $this->buildWherePart($excludeFilters);
if ($filterWherePart != '') {
$filterWherePart = ' WHERE ' . $filterWherePart . " \n";
}
}
$query = implode(" \n", array($selectPart, $fromPart, $filterWherePart, $groupPart, $sortingPart));
$query = $this->processQueryWithFluid($query);
return $query;
}
/**
* return translated criteria string
* @param \Tx_PtExtlist_Domain_QueryObject_AndCriteria|\Tx_PtExtlist_Domain_QueryObject_Criteria $criteria Tx_PtExtlist_Domain_QueryObject_AndCriteria
* @return string
*/
public static function translateCriteria(Tx_PtExtlist_Domain_QueryObject_Criteria $criteria)
{
$andCriteriaString = '(' . Tx_PtExtlist_Domain_DataBackend_MySqlDataBackend_MySqlInterpreter_MySqlInterpreter::translateCriteria($criteria->getFirstCriteria());
$andCriteriaString .= ') AND (';
$andCriteriaString .= Tx_PtExtlist_Domain_DataBackend_MySqlDataBackend_MySqlInterpreter_MySqlInterpreter::translateCriteria($criteria->getSecondCriteria()) . ')';
return $andCriteriaString;
}
/**
* @param $filterBoxCollection
* @return string whereClauseSbippet
*/
public function getWhereClauseFromFilterboxes($filterBoxCollection)
{
$whereClauses = array();
foreach ($filterBoxCollection as $filterBox) {
/* @var $filterBox Tx_PtExtlist_Domain_Model_Filter_Filterbox */
foreach ($filterBox as $filter) {
/* @var $filter Tx_PtExtlist_Domain_Model_Filter_FilterInterface */
$whereClauses[] = Tx_PtExtlist_Domain_DataBackend_MySqlDataBackend_MySqlInterpreter_MySqlInterpreter::getCriterias($filter->getFilterQuery());
}
}
$whereClauseString = '';
$whereClauses = array_filter($whereClauses);
if (count($whereClauses)) {
$whereClauseString = sizeof($whereClauses) > 1 ? implode(' AND ', $whereClauses) : current($whereClauses);
}
return $whereClauseString;
}
/**
* translate NOT criteria to string
*
* @param Tx_PtExtlist_Domain_QueryObject_Criteria $criteria Tx_PtExtlist_Domain_QueryObject_NotCriteria
* @return string
*/
public static function translateCriteria(Tx_PtExtlist_Domain_QueryObject_Criteria $criteria)
{
return 'NOT (' . Tx_PtExtlist_Domain_DataBackend_MySqlDataBackend_MySqlInterpreter_MySqlInterpreter::translateCriteria($criteria->getCriteria()) . ')';
}
/**
* @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);
}
public function testGetSortings()
{
$sorting = Tx_PtExtlist_Domain_DataBackend_MySqlDataBackend_MySqlInterpreter_MySqlInterpreter::getSorting($this->queryObject);
$this->assertTrue($sorting == 'test ASC, test2 DESC', $sorting);
}
