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