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