public function setUp() { $this->queryObject = new Query(); $this->queryObject->setQuery(new AndNode([new AndNode([new EqNode('q', null), new NeNode('q', null), new LeNode('q', 'r'), new GeNode('q', 'u')]), new OrNode([new LtNode('q', 't'), new GtNode('q', 'y'), new InNode('q', ['a', 's', 'd', 'f', 'g'])])])); $this->queryObject->setSelect(new XSelectNode(['q', new AggregateFunctionNode('max', 'q'), new AggregateFunctionNode('min', 'q'), new AggregateFunctionNode('count', 'q')])); $this->queryObject->setSort(new SortNode(['q' => -1, 'w' => 1, 'e' => 1])); $this->queryObject->setLimit(new LimitNode(20, 30)); $this->rqlString = "and(and(eq(q,null()),ne(q,null()),le(q,r),ge(q,u)),or(lt(q,t),gt(q,y),in(q,(a,s,d,f,g))))"; $this->rqlString .= "&limit(20,30)"; $this->rqlString .= "&sort(-q,+w,e)"; $this->rqlString .= "&select(q,max(q),min(q),count(q))"; }
/** * @return void */ public function testOnKernelResponseProcessed() { $query = new Query(); $query->setSelect(new SelectNode(['a', 'b'])); $request = new Request(); $request->attributes->set('rqlQuery', $query); $response = new Response(); $response->setContent('[{"a":1},{"b":2}]'); $this->handleValidEvent($request, $response, [__METHOD__, __METHOD__]); $this->assertEquals(json_encode([__METHOD__, __METHOD__]), $response->getContent()); }
public function provider_query() { $emptyQuery = new Query(); $queryWithAndNode = new Query(); $queryWithAndNode->setQuery(new AndNode([new LtNode('price', 23), new NeNode('icon', 'icon1.jpg')])); $queryWithSelect = new Query(); $queryWithSelect->setSelect(new SelectNode(['price', 'icon'])); $queryWithSort = new Query(); $queryWithSort->setSort(new SortNode(['price' => -1, 'icon' => +1])); $queryWithSelectProps = new Query(); $queryWithSelectProps->setSelect(new SelectNode([StoreCatalog::PROP_LINKED_URL_TABLE_NAME])); return array(array($emptyQuery, array(['title' => 'Plate41-mainicon', 'price' => '21', 'icon' => 'icon1.jpg'], ['title' => 'Plate42-mainicon', 'price' => '22', 'icon' => 'icon2.jpg'], ['title' => 'Plate43-mainicon', 'price' => '23', 'icon' => 'icon3.jpg']), array(['title' => 'Plate41-mainicon', 'price' => '21', 'icon' => 'icon1.jpg'], ['title' => 'Plate42-mainicon', 'price' => '22', 'icon' => 'icon2.jpg'], ['title' => 'Plate43-mainicon', 'price' => '23', 'icon' => 'icon3.jpg'])), array($queryWithAndNode, array(['title' => 'Plate41-mainicon', 'price' => '21', 'icon' => 'icon1.jpg'], ['title' => 'Plate42-mainicon', 'price' => '22', 'icon' => 'icon2.jpg'], ['title' => 'Plate43-mainicon', 'price' => '23', 'icon' => 'icon3.jpg']), array(['title' => 'Plate42-mainicon', 'price' => '22', 'icon' => 'icon2.jpg'])), array($queryWithSelect, array(['title' => 'Plate41-mainicon', 'price' => '21', 'icon' => 'icon1.jpg'], ['title' => 'Plate42-mainicon', 'price' => '22', 'icon' => 'icon2.jpg'], ['title' => 'Plate43-mainicon', 'price' => '23', 'icon' => 'icon3.jpg']), array(['price' => '21', 'icon' => 'icon1.jpg'], ['price' => '22', 'icon' => 'icon2.jpg'], ['price' => '23', 'icon' => 'icon3.jpg'])), array($queryWithSort, array(['title' => 'Plate43-mainicon', 'price' => '23', 'icon' => 'icon6.jpg'], ['title' => 'Plate41-mainicon', 'price' => '21', 'icon' => 'icon1.jpg'], ['title' => 'Plate42-mainicon', 'price' => '22', 'icon' => 'icon2.jpg'], ['title' => 'Plate43-mainicon', 'price' => '22', 'icon' => 'icon3.jpg'], ['title' => 'Plate43-mainicon', 'price' => '23', 'icon' => 'icon5.jpg']), array(['title' => 'Plate43-mainicon', 'price' => '23', 'icon' => 'icon5.jpg'], ['title' => 'Plate43-mainicon', 'price' => '23', 'icon' => 'icon6.jpg'], ['title' => 'Plate42-mainicon', 'price' => '22', 'icon' => 'icon2.jpg'], ['title' => 'Plate43-mainicon', 'price' => '22', 'icon' => 'icon3.jpg'], ['title' => 'Plate41-mainicon', 'price' => '21', 'icon' => 'icon1.jpg'])), array($queryWithSelectProps, array(['title' => 'Plate41-mainicon', 'price' => '21', 'icon' => 'icon1.jpg', StoreCatalog::PROP_LINKED_URL_TABLE_NAME => [['url' => 'http://google.com', 'alt' => 'Pot1'], ['url' => 'http://google.com1', 'alt' => 'Pot2']]], ['title' => 'Plate42-mainicon', 'price' => '22', 'icon' => 'icon2.jpg', StoreCatalog::PROP_LINKED_URL_TABLE_NAME => [['url' => 'http://google.com2', 'alt' => 'Pot3'], ['url' => 'http://google.com3', 'alt' => 'Pot4']]]), array(['title' => 'Plate41-mainicon', 'price' => '21', 'icon' => 'icon1.jpg', StoreCatalog::PROP_LINKED_URL_TABLE_NAME => [['url' => 'http://google.com', 'alt' => 'Pot1'], ['url' => 'http://google.com1', 'alt' => 'Pot2']]], ['title' => 'Plate42-mainicon', 'price' => '22', 'icon' => 'icon2.jpg', StoreCatalog::PROP_LINKED_URL_TABLE_NAME => [['url' => 'http://google.com2', 'alt' => 'Pot3'], ['url' => 'http://google.com3', 'alt' => 'Pot4']]]))); }
public function testSelectAggregateFunction_Combo_True() { $this->_initObject(); $query = new Query(); $aggregateCount = new AggregateFunctionNode('count', 'id'); $aggregateMaxId = new AggregateFunctionNode('max', 'id'); $aggregateMinId = new AggregateFunctionNode('min', 'id'); $query->setLimit(new Node\LimitNode(2, 1)); $query->setQuery(new ScalarOperator\EqNode('fString', 'val2')); $query->setSelect(new XSelectNode([$aggregateCount, $aggregateMaxId, $aggregateMinId, "anotherId"])); $resp = $this->object->query($query); $this->assertEquals(1, count($resp)); $this->assertEquals(2, $resp[0]['id->count']); //$this->assertEquals(2, $resp[0]['count(id)']); $this->assertEquals(4, $resp[0]['id->max']); //$this->assertEquals(4, $resp[0]['max(id)']); $this->assertEquals(3, $resp[0]['id->min']); //$this->assertEquals(3, $resp[0]['min(id)']); $this->assertEquals(40, $resp[0]['anotherId']); //$this->assertEquals(40, $resp[0]['anotherId']); }
/** * Return array of keys or empty array * * @return array array of keys or empty array */ protected function getKeys() { $identifier = $this->getIdentifier(); $query = new Query(); $selectNode = new Node\SelectNode([$identifier]); $query->setSelect($selectNode); $queryResult = $this->query($query); $keysArray = []; foreach ($queryResult as $row) { $keysArray[] = $row[$identifier]; } return $keysArray; }
/** * * @see Iterator * @return array */ public function next() { $identifier = $this->dataStore->getIdentifier(); $query = new Query(); $selectNode = new Node\SelectNode([$identifier]); $query->setSelect($selectNode); $sortNode = new Node\SortNode([$identifier => 1]); $query->setSort($sortNode); $limitNode = new Node\LimitNode(1, 0); $query->setLimit($limitNode); $gtNode = new ScalarOperator\GtNode($identifier, $this->index); $query->setQuery($gtNode); $queryArray = $this->dataStore->query($query); $this->index = $queryArray === [] ? null : $queryArray[0][$identifier]; }
public function testQuery_limitOffsetCombo() { $this->_initObject(); $query = new Query(); $eqNode1 = new ScalarOperator\EqNode('fString', 'val2'); $query->setQuery($eqNode1); $sortNode = new Node\SortNode(['id' => '1']); $query->setSort($sortNode); $selectNode = new Node\SelectNode(['fFloat']); $query->setSelect($selectNode); $limitNode = new Node\LimitNode(2, 1); $query->setLimit($limitNode); $queryArray = $this->object->query($query); $this->assertEquals(array('fFloat' => $this->_itemsArrayDelault[3 - 1]['fFloat']), $queryArray[1 - 1]); $this->assertEquals(array('fFloat' => $this->_itemsArrayDelault[4 - 1]['fFloat']), $queryArray[2 - 1]); $this->assertEquals(2, count($queryArray)); }
/** * @param SelectNode $select * @return $this */ public function addSelect(SelectNode $select) { $this->query->setSelect($select); return $this; }
public function test__query_with_select_sys_entities_aggregate() { $this->__init(); $time = (new DateTime())->format("Y-m-d") . " 00:00:00"; for ($i = 1; $i < 10; $i++) { $data = ['title' => 'title_' . $i, 'price' => 100 * $i]; $this->object->create($data); } $query = new Query(); $query->setSelect(new SelectNode([new AggregateFunctionNode('max', 'sys_entities.add_date')])); $result = $this->object->query($query); $this->assertEquals(true, $result[0]['sys_entities.add_date->max'] >= $time); }
public function test_query_category_products_SelectProduct_Category() { $this->initCategoryProducts(); $query = new Query(); $query->setSelect(new SelectNode(['product.', 'category.'])); $result = $this->object->query($query); $result = $this->unsetResultFiled($result, [41, 42, 50, 53, 54]); $this->assertEquals([["id" => "41", 'category_id' => '41', 'product_id' => '11', 'product.title' => "Edelweiss", "product.price" => "200", "category.title" => "Miscellaneous"], ["id" => "42", 'category_id' => '42', 'product_id' => '12', 'product.title' => "Rose", "product.price" => "50", "category.title" => "Flower"], ["id" => "50", 'category_id' => '43', 'product_id' => '15', 'product.title' => "King Rose 1", "product.price" => "200", "category.title" => "Roses"], ["id" => "53", 'category_id' => '44', 'product_id' => '16', 'product.title' => "King Rose 2", "product.price" => "250", "category.title" => "King Rose"], ["id" => "54", 'category_id' => '45', 'product_id' => '13', 'product.title' => "Queen Rose 1", "product.price" => "150", "category.title" => "Queen Rose"]], $result); }
/** * @param ServerRequestInterface $request * @param ResponseInterface $response * @return ResponseInterface * @throws \zaboy\rest\RestException * @internal param callable|null $next */ public function methodGetWithoutId(ServerRequestInterface $request, ResponseInterface $response) { /** @var Query $rqlQueryObject */ $rqlQueryObject = $request->getAttribute('Rql-Query-Object'); $rqlLimitNode = $rqlQueryObject->getLimit(); $headerLimit = $request->getAttribute('Limit'); if (!is_null($rqlLimitNode)) { if (isset($headerLimit)) { $limit = $rqlLimitNode->getLimit() > $headerLimit['limit'] ? $headerLimit['limit'] : $rqlLimitNode->getLimit(); if (isset($headerLimit['offset'])) { $offset = $headerLimit['offset']; $rqlOffset = $rqlLimitNode->getOffset(); if (!is_null($rqlOffset)) { $offset += $rqlOffset; } $newLimitNode = new LimitNode($limit, $offset); } else { $newLimitNode = new LimitNode($limit); } $rqlQueryObject->setLimit($newLimitNode); } } else { if ($headerLimit) { $limit = (int) $headerLimit['limit']; if (isset($headerLimit['offset'])) { $offset = (int) $headerLimit['offset']; $newLimitNode = new LimitNode($limit, $offset); } else { $newLimitNode = new LimitNode($limit); } $rqlQueryObject->setLimit($newLimitNode); } } $rowCountQuery = new Query(); $aggregate = new AggregateFunctionNode('count', $this->dataStore->getIdentifier()); $rowCountQuery->setSelect(new XSelectNode([$aggregate])); if ($rqlQueryObject->getQuery()) { $rowCountQuery->setQuery($rqlQueryObject->getQuery()); } if ($rqlLimitNode) { $rowCountQuery->setLimit($rqlLimitNode); } //TODO: count aggregate fn can't work with limit and offset. Bug!!! $rowset = $this->dataStore->query($rqlQueryObject); $this->request = $request->withAttribute('Response-Body', $rowset); $rowCountQuery = new Query(); $rowCountQuery->setSelect(new XSelectNode([new AggregateFunctionNode('count', $this->dataStore->getIdentifier())])); $rowCount = $this->dataStore->query($rowCountQuery); if (isset($rowCount[0][$this->dataStore->getIdentifier() . '->count'])) { //throw new RestException('Can not make Content-Range header in response'); $limitObject = $rqlQueryObject->getLimit(); $offset = !$limitObject ? 0 : $limitObject->getOffset(); $contentRange = 'items ' . $offset . '-' . ($offset + count($rowset) - 1) . '/' . $rowCount[0][$this->dataStore->getIdentifier() . '->count']; $response = $response->withHeader('Content-Range', $contentRange); } $response = $response->withStatus(200); return $response; }
protected function setSelectColumns(Select $selectSQL, Query $query) { $select = $query->getSelect(); $selectField = !$select ? [] : $select->getFields(); $fields = []; if (!empty($selectField)) { $bounds = []; $hawAggregate = false; $hawBound = false; foreach ($selectField as $field) { $match = []; if ($field instanceof AggregateFunctionNode) { $hawAggregate = true; //todo: create aggregate } else { if (preg_match('/([\\w]+)\\./', $field, $match)) { $subMatch = []; $name = $match[1]; $boundQuery = new Query(); if (preg_match('/([\\w]+)\\.\\#([\\w]+)?/', $field, $subMatch)) { $withOut = $this->dbTable->table; if (isset($subMatch[2])) { $withOut = $subMatch[2]; } $boundQuery->setSelect(new SelectNode(['#' . $withOut])); } else { if (preg_match('/[\\w]+\\.([\\w\\.\\#]+)/', $field, $subMatch)) { $boundQuery->setSelect(new SelectNode([$subMatch[1]])); } else { $boundQuery->setSelect(new SelectNode()); } } $bounds[] = [$name => $boundQuery]; } else { if (preg_match('/^#([\\w]+)?$/', $field, $match)) { $withOut = ''; if ($match[1]) { $withOut = $match[1]; } foreach ($this->getBoundsTableName() as $bound) { if ($bound != $withOut) { $boundQuery = new Query(); $boundQuery->setSelect(new SelectNode()); $bounds[] = [$bound => $boundQuery]; } } } else { $fields[] = $field; } } } if ($hawAggregate && $hawBound) { throw new DataStoreException('Cannot use aggregate function with bounds'); } } if (!empty($bounds)) { $fields['.bounds.'] = $bounds; } } $selectSQL->columns(empty($fields) ? [Select::SQL_STAR] : $fields); return $selectSQL; }
protected function updateProps($props, $propsData, &$itemInserted) { $identifier = $this->getIdentifier(); if (!empty($itemInserted)) { /** * @var string $key * @var Prop $prop */ foreach ($props as $key => $prop) { $propQuery = new Query(); $propQuery->setQuery(new EqNode($prop->getLinkedColumn($this->getEntityName(), $key), $itemInserted[$identifier])); $propQuery->setSelect(new SelectNode([$prop->getIdentifier()])); $allEntityProp = $prop->query($propQuery); foreach ($allEntityProp as $entityPropItem) { $find = false; foreach ($propsData[$key] as &$propDataItem) { if (isset($propDataItem[$prop->getIdentifier()]) && $entityPropItem[$prop->getIdentifier()] === $propDataItem[$prop->getIdentifier()]) { $find = true; $diff = array_diff_assoc($entityPropItem, $propDataItem); if (empty($diff) || count($propDataItem) == 1) { unset($propDataItem); } break; } } if (!$find) { $prop->delete($entityPropItem[$prop->getIdentifier()]); } } $prop->updateWithEntity($propsData[$key], $itemInserted[$identifier], $this->getEntityName(), $key); $propQuery->setSelect(new SelectNode()); $allEntityProp = $prop->query($propQuery); $itemInserted[$key] = $allEntityProp; } } }