public static final function factoryData($where, $table, $dbconnection, array $options = null) { // Select * from $table where $where $build = QueryBuilder::select($table)->where($where); if (is_array($options)) { // Limit if (key_exists('limit', $options) && key_exists('offset', $options)) { $build->limit($options['limit'], $options['offset']); } elseif (key_exists('limit', $options)) { $build->limit($options['limit']); } // Sort if (key_exists('sort', $options)) { if (is_array($options['sort'])) { foreach ($options['sort'] as $sortby) { list($sort, $dir) = explode(' ', $sortby, 2); $build->sortBy($sort, $dir); } } else { list($sort, $dir) = explode(' ', $options['sort'], 2); $build->sortBy($sort, $dir); } } } $query = new Query($dbconnection); list($data) = $query->sql($build)->execute(); return $data; }
public function testNotInBlankClause() { $qb = QueryBuilder::select(['database', 'schema', 'test' => 't'], ['id']); $qb->where(['!t.data' => []]); list($sql, $data) = $qb->resolve(); $this->assertEquals('SELECT `id` FROM `database`.`schema`.`test` as `t` WHERE true', $sql); $this->assertEquals(0, count($data)); }
public function getChildren() { // Find all direct child/parent relationships $query = new Query(static::getConnection()); $query->sql(QueryBuilder::select($this->closureTable, ['child_id'])->where(['parent_id' => $this->id, 'depth' => 1])); list($results) = $query->execute(); $children = []; foreach ($results as $row) { $children[] = $row['child_id']; } return static::findAll(['id' => $children]); }
public function joinCount($var, $where = []) { if (!is_null($this->__external[$var]) && !$this->__external[$var] instanceof Collection) { return 1; } if ($this->__external[$var]) { return $this->__external[$var]->filter($where)->count(); } // If this Model_Data isn't linked to the db yet, then linked values cannot exist if (!($id = $this->__data['id'])) { return 0; } /* FOREIGN KEYS */ // 1-1, just grab the object - not worth optimising if (key_exists($var, (array) $this->__model['one-to-one'])) { /* Call Tablename::factory(foreign key id) to get the object we want */ $table = $this->__model['one-to-one'][$var]; $this->__external[$var] = Model::factoryObjectCache($id, $table, $this->__database); return $this->__external[$var] ? 1 : 0; } // M-1, just grab the object - not worth optimising if (key_exists($var, (array) $this->__model['many-to-one'])) { /* Call Tablename::factory(foreign key id) to get the object we want */ $table = $this->__model['many-to-one'][$var]; $this->__external[$var] = Model::factoryObjectCache($this->__data[$var . '_id'], $table, $this->__database); return $this->__external[$var] ? 1 : 0; } /* Look for lists of objects in other tables referencing this one */ if (key_exists($var, (array) $this->__model['one-to-many'])) { $table = $this->__model['one-to-many'][$var]['table']; $column = $this->__model['one-to-many'][$var]['column_name']; // Use the model factory to find the relevant items list($data) = Model::factoryDataCount($where + [$column => $id], $table, $this->__database); return $data['count']; } if (key_exists($var, (array) $this->__model['many-to-many'])) { // Get pivot schema $pivot = $this->__model['many-to-many'][$var]; // We can only support simple connection access for 2 key pivots. if (count($pivot['connections']) != 1) { throw new Exception\Model('MODEL_DATA:CANNOT_CALL_MULTIPIVOT_AS_PROPERTY', array($var)); } // Get a list of ids linked to this object (i.e. the tablename_id stored in the pivot table) $pivot_schema = $this->__schema->getTable($pivot['pivot']); $pivot_tablename = $pivot_schema['table_name']; $clauses = []; if ($where) { foreach ($where as $clause_column => $clause_value) { // Rewrite $where clauses to insert `pivotjoin` table in column name preg_match('/^([!=<>%]*)(.+?)([!=<>%]*)$/', $clause_column, $parts); $prefix = $parts[1] ?: $parts[3]; $clause_column = $parts[2]; $clauses['`pivotjoin`.`' . $clause_column . '`' . $prefix] = $clause_value; } } // Build Query $q = QueryBuilder::select([$pivot_tablename => 'pivot'], ['pivot.*'])->where(['`pivot`.' . $pivot['id'] => $this->__data['id']])->join([Schema::underscoreCase($pivot['connections'][0]['table']) => 'pivotjoin'])->joinOn(['pivotjoin.id' => "`pivot`.`{$pivot['connections'][0]['column']}`"])->where($clauses); $query = new Query($this->__database); list($raw) = $query->sql($q)->execute(); // Rearrange the list of ids into a flat array $id = array(); foreach ($raw as $raw_id) { $id[] = $raw_id[$pivot['connections'][0]['column']]; } $dedup = array_unique($id); return count($dedup); } throw new Exception\Model("MODEL_DATA:UNKNOWN_FOREIGN_PROPERTY", ['property' => $var, 'data' => $this]); }