public function testFilters() { $this->app['db']->truncateContentType('example', 'example01'); $this->app['db']->truncateContentType('example', 'example02'); $this->app['db']->truncateContentType('example', 'example03'); /** * @var $manager ContentManager */ $manager = $this->repository->getContentManager('example01'); $record = array(); $record['properties'] = array('name' => 'New Record 1', 'source' => 'a'); $id = $manager->saveRecord($record); $this->assertEquals(1, $id); $record = array(); $record['properties'] = array('name' => 'New Record 2', 'source' => 'c', 'article' => 'c'); $id = $manager->saveRecord($record); $this->assertEquals(2, $id); $record = array(); $record['properties'] = array('name' => 'Differing Name'); $id = $manager->saveRecord($record); $this->assertEquals(3, $id); $filter = new Filter(); $filter->addCondition('name', '><', 'Record'); $filter->addCondition('name', '=', 'Differing Name'); $records = $manager->getRecords('default', 'default', 'property_name DESC, id ASC', null, 1, null, $filter); $this->assertCount(3, $records['records']); $filter = new Filter(); $filter->addCondition('name', '=', 'Differing Name'); $records = $manager->getRecords('default', 'default', 'property_name DESC, id ASC', null, 1, null, $filter); $this->assertCount(1, $records['records']); $filter = new Filter(); $filter->addCondition('name', '=', 'New Record 1'); $filter->nextConditionsBlock(); $filter->addCondition('source', '=', 'a'); $records = $manager->getRecords('default', 'default', 'property_name DESC, id ASC', null, 1, null, $filter); $this->assertCount(1, $records['records']); $filter = new Filter(); $filter->addCondition('name', '=', 'New Record 1'); $filter->nextConditionsBlock(); $filter->addCondition('source', '=', 'b'); $records = $manager->getRecords('default', 'default', 'property_name DESC, id ASC', null, 1, null, $filter); $this->assertCount(0, $records['records']); $filter = new Filter(); $filter->addCondition('source', '={}', 'article'); $records = $manager->getRecords('default', 'default', 'property_name DESC, id ASC', null, 1, null, $filter); $this->assertCount(1, $records['records']); $filter = new Filter(); $filter->addCondition('name', '=', 'New Record 2'); $filter->addCondition('source', '=', 'c'); $records = $manager->getRecords('default', 'default', 'property_name DESC, id ASC', null, 1, null, $filter); $this->assertCount(1, $records['records']); $filter = new Filter(); $filter->addCondition('name', '=', 'New Record 2'); $filter->nextConditionsBlock(); $filter->addCondition('source', '=', 'a'); $records = $manager->getRecords('default', 'default', 'property_name DESC, id ASC', null, 1, null, $filter); $this->assertCount(0, $records['records']); }
public function getRecords($viewName = 'default', $workspace = 'default', $orderBy = 'id ASC', $limit = null, $page = 1, $subset = null, Filter $filter = null, $language = 'default', $timeshift = 0) { $records = array(); $repositoryName = $this->repository->getName(); $contentTypeName = $this->contentTypeDefinition->getName(); $tableName = $repositoryName . '$' . $contentTypeName; if ($tableName != Util::generateValidIdentifier($repositoryName) . '$' . Util::generateValidIdentifier($contentTypeName)) { throw new \Exception('Invalid repository and/or content type name(s).', self::INVALID_NAMES); } $dbh = $this->repository->getDatabaseConnection(); $sqlSubset = ''; if ($subset != null) { $orderBy = 'position ASC'; $parentRecordId = 0; $includeParentRecord = 1; $depth = null; $subset = explode(',', $subset); if (isset($subset[0])) { $parentRecordId = (int) $subset[0]; } if (isset($subset[1])) { $includeParentRecord = (int) $subset[1]; } if (isset($subset[2])) { $depth = (int) $subset[2]; } if ($parentRecordId != 0) { try { $row = $this->getRecordTableRow($parentRecordId, $workspace, $language, $timeshift); if ($depth < 0) { if ($includeParentRecord == 1) { $sqlSubset = ' AND (position_left <= ' . (int) $row['position_left'] . ' AND position_right >=' . (int) $row['position_right'] . ')'; $depth = $depth - 1; } else { $sqlSubset = ' AND (position_left < ' . (int) $row['position_left'] . ' AND position_right >' . (int) $row['position_right'] . ')'; } } else { $maxlevel = (int) $row['position_level'] + $depth + 1; if ($includeParentRecord == 1) { $sqlSubset = ' AND (position_left >= ' . (int) $row['position_left'] . ' AND position_right <=' . (int) $row['position_right'] . ')'; } else { $sqlSubset = ' AND (position_left > ' . (int) $row['position_left'] . ' AND position_right <' . (int) $row['position_right'] . ')'; } } } catch (\Exception $e) { return $records; } } else { // Take all records, that do have a sorting position $maxlevel = $depth + 1; $sqlSubset = ' AND NOT position IS NULL'; } if ($depth != null && $depth > 0) { $sqlSubset .= ' AND position_level <' . $maxlevel; } } if ($filter) { $sqlFilter = $filter->getMySQLExpression(); if ($sqlFilter) { $sqlSubset .= ' AND ' . $sqlFilter['sql']; } } $timestamp = $this->repository->getTimeshiftTimestamp($timeshift); $sql = 'SELECT * FROM ' . $tableName . ' WHERE workspace = ? AND language = ? AND deleted = 0 AND validfrom_timestamp <= ? AND validuntil_timestamp > ? ' . $sqlSubset . ' ORDER BY ' . $orderBy; if ($limit != null && $subset == null) { $sql .= ' LIMIT ' . ((int) $page - 1) * (int) $limit . ',' . (int) $limit; } $stmt = $dbh->prepare($sql); $params = array(); $params[] = $workspace; $params[] = $language; $params[] = $timestamp; $params[] = $timestamp; if ($filter && $sqlFilter) { $params = array_merge($params, $sqlFilter['params']); } try { $stmt->execute($params); $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC); foreach ($rows as $row) { $records[$row['id']] = $this->getRecordDataStructureFromRow($row, $repositoryName, $contentTypeName, $viewName); } if ($subset && $depth < 0) { $records = array_slice($records, $depth); } $info = array(); $info['repository'] = $repositoryName; $info['content_type'] = $contentTypeName; $info['workspace'] = $workspace; $info['view'] = $viewName; $info['language'] = $language; $count = $this->countRecords($workspace, $filter, $language, $timeshift); $info['count'] = $count['count']; $info['lastchange'] = $count['lastchange']; return array('info' => $info, 'records' => $records); } catch (\PDOException $e) { if ($e->getCode() == 42) { throw new RepositoryException('Could not execute your orderBy command.', RepositoryException::REPOSITORY_BAD_PARAMS); } } }