public function fetchFeed($params = null)
{
$sql = new Sql($this->adapter);
$select = $sql->select()->from($this->table);
$params['orderColumn'] = 'id';
$params['orderDirection'] = 'DESC';
$tableSchemaArray = TableSchema::getSchemaArray($this->table);
$hasActiveColumn = $this->schemaHasActiveColumn($tableSchemaArray);
$params = $this->applyDefaultEntriesSelectParams($params);
$columns = ['id', 'identifier', 'action', 'table_name', 'row_id', 'user', 'datetime', 'type', 'data'];
$select->columns($columns);
// ->order('id DESC');
$select->where->nest->isNull('parent_id')->OR->equalTo('type', 'FILES')->unnest;
$select = $this->applyParamsToTableEntriesSelect($params, $select, $tableSchemaArray, $hasActiveColumn);
//die($this->dumpSql($select));
$rowset = $this->selectWith($select);
$rowset = $rowset->toArray();
$rowset = $this->convertDates($rowset, $tableSchemaArray);
// @TODO: Returns date in ISO 8601 Ex: 2016-06-06T17:18:20Z
// see: https://en.wikipedia.org/wiki/ISO_8601
// foreach ($rowset as &$row) {
// $row['datetime'] .= ' UTC';
// }
$countTotalWhere = new Where();
$countTotalWhere->isNull('parent_id')->OR->equalTo('type', 'FILES');
$activityTotal = $this->countTotal($countTotalWhere);
return ['total' => $activityTotal, 'rows' => $rowset];
}
public function fetchFeed($params = null)
{
$sql = new Sql($this->adapter);
$select = $sql->select()->from($this->table);
$params['orderColumn'] = 'id';
$params['orderDirection'] = 'DESC';
$tableSchemaArray = TableSchema::getSchemaArray($this->table);
$hasActiveColumn = $this->schemaHasActiveColumn($tableSchemaArray);
$params = $this->applyDefaultEntriesSelectParams($params);
$columns = array('id', 'identifier', 'action', 'table_name', 'row_id', 'user', 'datetime', 'type', 'data');
$select->columns($columns);
// ->order('id DESC');
$select->where->nest->isNull('parent_id')->OR->equalTo('type', 'FILES')->unnest;
$select = $this->applyParamsToTableEntriesSelect($params, $select, $tableSchemaArray, $hasActiveColumn);
//die($this->dumpSql($select));
$rowset = $this->selectWith($select);
$rowset = $rowset->toArray();
foreach ($rowset as &$row) {
$row['datetime'] .= ' UTC';
}
$countTotalWhere = new Where();
$countTotalWhere->isNull('parent_id')->OR->equalTo('type', 'FILES');
$activityTotal = $this->countTotal($countTotalWhere);
return array('total' => $activityTotal, 'rows' => $rowset);
}
public function toArrayWithImmediateRelationships(RelationalTableGateway $TableGateway)
{
if ($this->table !== $TableGateway->getTable()) {
throw new \InvalidArgumentException('The table of the gateway parameter must match this row\'s table.');
}
$entry = $this->toArray();
$schemaArray = TableSchema::getSchemaArray($this->table);
$aliasColumns = $TableGateway->filterSchemaAliasFields($schemaArray);
// Many-to-One
list($entry) = $TableGateway->loadManyToOneRelationships($schemaArray, [$entry]);
// One-to-Many, Many-to-Many
$entry = $TableGateway->loadToManyRelationships($entry, $aliasColumns);
return $entry;
}
/**
* Fetch related, foreign rows for one record's OneToMany relationships.
*
* @param string $table
* @param string $column_name
* @param string $column_equals
*/
public function loadOneToManyRelationships($table, $column_name, $column_equals)
{
if (!TableSchema::canGroupViewTable($table)) {
return false;
}
// Run query
$select = new Select($table);
$select->where->equalTo($column_name, $column_equals);
// Only select the fields not on the currently authenticated user group's read field blacklist
$columns = TableSchema::getAllNonAliasTableColumnNames($table);
$select->columns($columns);
$TableGateway = new RelationalTableGateway($this->acl, $table, $this->adapter);
$rowset = $TableGateway->selectWith($select);
$results = $rowset->toArray();
$schemaArray = TableSchema::getSchemaArray($table);
$results = $this->loadManyToOneRelationships($schemaArray, $results);
return array('rows' => $results);
}
// This `type` variable is used on the client-side
// Not need on server side.
// @TODO: We should probably stop using it on the client-side
unset($requestPayload['type']);
// Add table name to dataset. @TODO more clarification would be useful
// Also This would return an Error because of $row not always would be an array.
foreach ($requestPayload as &$row) {
if (is_array($row)) {
$row['table_name'] = $table;
}
}
if ($app->request()->isPut()) {
$TableGateway = new TableGateway($acl, 'directus_columns', $ZendDb);
$TableGateway->updateCollection($requestPayload);
}
$response = TableSchema::getSchemaArray($table, $params);
JsonView::render($response);
})->via('GET', 'PUT');
$app->post("/{$v}/tables/:table/columns/:column/?", function ($table, $column) use($ZendDb, $acl, $requestPayload, $app) {
$TableGateway = new TableGateway($acl, 'directus_columns', $ZendDb);
$data = $requestPayload;
// @TODO: check whether this condition is still needed
if (isset($data['type'])) {
$data['data_type'] = $data['type'];
$data['relationship_type'] = $data['type'];
unset($data['type']);
}
//$data['column_name'] = $data['junction_key_left'];
$data['column_name'] = $column;
$data['table_name'] = $table;
$row = $TableGateway->findOneByArray(array('table_name' => $table, 'column_name' => $column));
/**
* Fetch related, foreign rows for one record's ManyToMany relationships.
*
* @param string $table_name
* @param string $foreign_table
* @param string $junction_table
* @param string $junction_key_left
* @param string $junction_key_right
* @param string $column_equals
* @param string $parentField
* @param int $level
*
* @return array Foreign rowset
*/
public function loadManyToManyRelationships($table_name, $foreign_table, $junction_table, $junction_key_left, $junction_key_right, $column_equals, $parentField = null, $level = 0)
{
$foreign_table_pk = TableSchema::getTablePrimaryKey($foreign_table);
$foreign_join_column = $foreign_table . '.' . $foreign_table_pk;
$junction_join_column = $junction_table . '.' . $junction_key_right;
$junction_comparison_column = $junction_table . '.' . $junction_key_left;
// =============================================================================
// HOTFIX: prevent infinite circle loop
// =============================================================================
if ($parentField && $this->hasToManyCallStack($parentField, $foreign_table)) {
return $column_equals;
}
if ($parentField !== null) {
$this->addToManyCallStack($level, $parentField, $foreign_table);
}
$junction_table_pk = TableSchema::getTablePrimaryKey($junction_table);
$junction_id_column = $junction_table . '.' . $junction_table_pk;
// Less likely name collision:
$junction_id_column_alias = 'directus_junction_id_column_518d31856e131';
$junction_sort_column_alias = 'directus_junction_sort_column_518d318e3f0f5';
$junctionSelectColumns = [$junction_id_column_alias => $junction_table_pk];
$sql = new Sql($this->adapter);
$select = $sql->select();
// If the Junction Table has a Sort column, do eet.
// @todo is this the most efficient way?
// @hint TableSchema#getUniqueColumnName
$junctionColumns = TableSchema::getAllNonAliasTableColumnNames($junction_table);
if (in_array('sort', $junctionColumns)) {
$junctionSelectColumns[$junction_sort_column_alias] = 'sort';
$select->order($junction_sort_column_alias);
}
$select->from($foreign_table)->join($junction_table, $foreign_join_column . '=' . $junction_join_column, $junctionSelectColumns)->where([$junction_comparison_column => $column_equals])->order($junction_id_column . ' ASC');
// Only select the fields not on the currently authenticated user group's read field blacklist
$columns = TableSchema::getAllNonAliasTableColumnNames($foreign_table);
$select->columns($columns);
$ForeignTable = new RelationalTableGateway($this->acl, $foreign_table, $this->adapter);
$results = $ForeignTable->selectWith($select);
$results = $results->toArray();
$foreign_data = [];
$columns = TableSchema::getAllNonAliasTableColumns($foreign_table);
foreach ($results as $row) {
$row = $recordData = SchemaManager::parseRecordValuesByType($row, $columns);
$junction_table_id = (int) $row[$junction_id_column_alias];
unset($row[$junction_id_column_alias]);
$entry = [$junction_table_pk => $junction_table_id];
if (in_array('sort', $junctionColumns)) {
// @TODO: check why is this a string instead of an integer.
$entry['sort'] = (int) $row[$junction_sort_column_alias];
unset($row[$junction_sort_column_alias]);
}
$schemaArray = TableSchema::getSchemaArray($foreign_table);
$alias_fields = $this->filterSchemaAliasFields($schemaArray);
// (fmrly $alias_schema)
$row = $this->loadToManyRelationships($row, $alias_fields, $parentField, $level + 1);
$entry['data'] = $row;
$foreign_data[] = $entry;
}
return ['rows' => $foreign_data];
}
public function applyParamsToTableEntriesSelect(array $params, Select $select, array $schema, $hasActiveColumn = false)
{
$tableName = $this->getTable();
if (isset($params['group_by'])) {
$select->group($tableName . '.' . $params['group_by']);
} else {
$select->group($tableName . '.' . $this->primaryKeyFieldName);
}
//If this is a relational order, than it is an array.
if (is_array($params['orderBy'])) {
$select->join(array('jsort' => $params['orderBy']['junction_table']), 'jsort.' . $params['orderBy']['jkeyRight'] . ' = ' . $tableName . '.' . $this->primaryKeyFieldName, array(), $select::JOIN_LEFT);
$select->join(array('rsort' => $params['orderBy']['related_table']), 'rsort.id = jsort.' . $params['orderBy']['jkeyLeft'], array(), $select::JOIN_LEFT);
$select->order('rsort.title', $params['orderDirection']);
} else {
$select->order(implode(' ', array($params['orderBy'], $params['orderDirection'])));
}
if (isset($params['perPage']) && isset($params['currentPage'])) {
$select->limit($params['perPage'])->offset($params['currentPage'] * $params['perPage']);
}
// Are we sorting on a relationship?
foreach ($schema as $column) {
if ($column['column_name'] != $params['orderBy']) {
continue;
}
// Must have defined table_related
if (!isset($column['relationship']) || !is_array($column['relationship']) || !isset($column['relationship']['table_related'])) {
break;
}
// Must have defined visible_column
if (!isset($column['options']) || !is_array($column['options']) || !isset($column['options']['visible_column'])) {
break;
}
$relatedTable = $column['relationship']['table_related'];
$visibleColumn = $column['options']['visible_column'];
$keyLeft = $params['table_name'] . "." . $params['orderBy'];
// @todo it's wrong to assume PKs are "id" but this is currently endemic to directus6
$keyRight = $relatedTable . ".id";
$joinedSortColumn = $relatedTable . "." . $visibleColumn;
$select->reset(Select::ORDER)->join($relatedTable, "{$keyLeft} = {$keyRight}", array(), Select::JOIN_LEFT)->order("{$joinedSortColumn} " . $params['orderDirection']);
break;
}
// Note: be sure to explicitly check for null, because the value may be
// '0' or 0, which is meaningful.
if (null !== $params[STATUS_COLUMN_NAME] && $hasActiveColumn) {
$haystack = is_array($params[STATUS_COLUMN_NAME]) ? $params[STATUS_COLUMN_NAME] : explode(",", $params[STATUS_COLUMN_NAME]);
if (!isset($params['table_name']) || empty($params['table_name'])) {
$tableName = $this->getTable();
} else {
$tableName = $params['table_name'];
}
$select->where->in($tableName . '.' . STATUS_COLUMN_NAME, $haystack);
}
// Select only ids from the ids if provided
if (array_key_exists('ids', $params)) {
$entriesIds = array_filter(explode(',', $params['ids']), 'is_numeric');
if (count($entriesIds) > 0) {
$select->where->in($this->getTable() . '.' . $this->primaryKeyFieldName, $entriesIds);
}
}
// Where
$select->where->nest->expression('-1 = ?', $params[$this->primaryKeyFieldName])->or->equalTo($tableName . '.' . $this->primaryKeyFieldName, $params[$this->primaryKeyFieldName])->unnest;
// very very rudimentary ability to supply where conditions to fetch...
// at the moment, only 'equalTo' and 'between' are supported... also, the 'total' key returned
// in the json does not reflect these filters...
// -MG
if (array_key_exists('where', $params)) {
$outer = $select->where->nest;
foreach ($params['where'] as $whereCond) {
$type = $whereCond['type'];
$column = $whereCond['column'];
if ($type == 'equalTo') {
$val = $whereCond['val'];
if (is_array($val)) {
$where = $select->where->nest;
foreach ($val as $currentval) {
$where->equalTo($column, $currentval);
if ($currentval != end($val)) {
$where->or;
}
}
$where->unnest;
} else {
$outer->equalTo($column, $val);
}
} else {
if ($type == 'between') {
$val1 = $whereCond['val1'];
$val2 = $whereCond['val2'];
$outer->between($column, $val1, $val2);
}
}
}
$outer->unnest;
}
//@TODO: Make this better
if (isset($params['adv_where'])) {
$select->where($params['adv_where']);
}
if (isset($params['adv_search']) && !empty($params['adv_search'])) {
$i = 0;
foreach ($params['adv_search'] as $search_col) {
$target = array();
foreach ($schema as $col) {
if ($col['id'] == $search_col['id']) {
$target = $col;
break;
}
}
if (empty($target)) {
continue;
}
// TODO: fix this, it must be refactored
if (isset($target['relationship']) && $target['relationship']['type'] == "MANYTOMANY") {
$relatedTable = $target['relationship']['table_related'];
$relatedAliasName = $relatedTable . "_" . $i;
if ($target['relationship']['type'] == "MANYTOMANY") {
$junctionTable = $target['relationship']['junction_table'];
$jkl = $target['relationship']['junction_key_left'];
$jkr = $target['relationship']['junction_key_right'];
$keyleft = $params['table_name'] . ".id";
$keyRight = $junctionTable . '.' . $jkl;
$jkeyleft = $junctionTable . '.' . $jkr;
$jkeyright = $relatedAliasName . ".id";
$select->join($junctionTable, "{$keyleft} = {$keyRight}", array(), Select::JOIN_INNER);
} else {
$select->join(array($relatedAliasName => $relatedTable), $tableName . '.' . $target['column_name'] . " = " . $relatedAliasName . ".id", array(), Select::JOIN_INNER);
}
$relatedTableMetadata = TableSchema::getSchemaArray($relatedTable);
if ($search_col['type'] == "like") {
$select->join(array($relatedAliasName => $relatedTable), "{$jkeyleft} = {$jkeyright}", array(), Select::JOIN_INNER);
$search_col['value'] = "%" . $search_col['value'] . "%";
if (isset($target['options']['filter_column'])) {
$targetCol = $target['options']['filter_column'];
} else {
$targetCol = $target['options']['visible_column'];
}
foreach ($relatedTableMetadata as $col) {
if ($col['id'] == $targetCol) {
if ($col['type'] == 'VARCHAR' || $col['type'] == 'INT') {
$where = $select->where->nest;
$columnName = $this->adapter->platform->quoteIdentifier($col['column_name']);
$columnName = $relatedAliasName . "." . $columnName;
$like = new Predicate\Expression("LOWER({$columnName}) LIKE ?", $search_col['value']);
$where->addPredicate($like, Predicate\Predicate::OP_OR);
$where->unnest;
}
}
}
} else {
$select->where($jkeyleft . ' = ' . $this->adapter->platform->quoteValue($search_col['value']));
}
} elseif (isset($target['relationship']) && $target['relationship']['type'] == "MANYTOONE") {
$relatedTable = $target['relationship']['table_related'];
$keyLeft = $this->getTable() . "." . $target['relationship']['junction_key_left'];
$keyRight = $relatedTable . ".id";
$filterColumn = $target['options']['filter_column'];
$joinedFilterColumn = $relatedTable . "." . $filterColumn;
// do not let join this table twice
// TODO: do a extra checking in case it's being used twice
// and none for sorting
if ($target['column_name'] != $params['orderBy']) {
$select->join($relatedTable, "{$keyLeft} = {$keyRight}", array(), Select::JOIN_LEFT);
}
if ($search_col['type'] == 'like') {
$searchLike = '%' . $search_col['value'] . '%';
$spec = function (Where $where) use($joinedFilterColumn, $searchLike) {
$where->like($joinedFilterColumn, $searchLike);
};
$select->where($spec);
} else {
$select->where($search_col['id'] . " " . $search_col['type'] . " " . $this->adapter->platform->quoteValue($search_col['value']));
}
} else {
if ($target['type'] == "DATETIME" && strpos($search_col['value'], " ") == false) {
$select->where('date(' . $tableName . '.' . $search_col['id'] . ") = " . $this->adapter->platform->quoteValue($search_col['value']));
} else {
if ($search_col['type'] == "like") {
$select->where($tableName . '.' . $search_col['id'] . " " . $search_col['type'] . " " . $this->adapter->platform->quoteValue("%" . $search_col['value'] . "%"));
} else {
$select->where($tableName . '.' . $search_col['id'] . " " . $search_col['type'] . " " . $this->adapter->platform->quoteValue($search_col['value']));
}
}
}
$i++;
}
} else {
if (isset($params['search']) && !empty($params['search'])) {
$params['search'] = "%" . $params['search'] . "%";
$where = $select->where->nest;
foreach ($schema as $col) {
if ($col['type'] == 'VARCHAR' || $col['type'] == 'INT') {
$columnName = $this->adapter->platform->quoteIdentifier($col['column_name']);
$like = new Predicate\Expression("LOWER({$columnName}) LIKE ?", strtolower($params['search']));
$where->addPredicate($like, Predicate\Predicate::OP_OR);
}
}
$where->unnest;
}
}
return $select;
}
