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