public function testInsertFromSelect() { $dialect = $this->getDbByType('PgSQL')->getDialect(); $select = OSQL::select()->from('test_table2')->get('field3')->get('field_7')->andWhere(Expression::gt('field2', DBValue::create('33'))); $insert = OSQL::insert()->setSelect($select)->into('test_table')->set('field2', 2)->set('field16', 3); $this->assertEquals($insert->toDialectString($dialect), 'INSERT INTO "test_table" ("field2", "field16") (' . 'SELECT "test_table2"."field3", "test_table2"."field_7" ' . 'FROM "test_table2" WHERE ("field2" > \'33\')' . ')'); }
/** * @return SelectQuery **/ public function makeCountQuery() { $query = $this->makeFetchQuery(); if ($query->isDistinct()) { $countFunction = SQLFunction::create('count', DBField::create($this->container->getDao()->getIdName(), $this->container->getDao()->getTable()))->setAggregateDistinct(); $query->unDistinct(); } else { $countFunction = SQLFunction::create('count', DBValue::create('*')); } return $query->dropFields()->dropOrder()->dropLimit()->get($countFunction->setAlias('count')); }
public function testSelectGet() { $dialect = $this->getDbByType('PgSQL')->getDialect(); $query = OSQL::select()->from('test_table')->get(DBField::create('field1', 'test_table'), 'alias1')->get(DBField::create('field2', 'test_table'))->get('field3', 'alias3')->get('field4')->get(SQLFunction::create('count', DBField::create('field5', 'test_table'))->setAggregateDistinct()->setAlias('alias5'))->get(SQLFunction::create('substring', BinaryExpression::create(DBField::create('field6', 'test_table'), DBValue::create('a..b'), 'from')->noBrackets())); $this->assertEquals($query->toDialectString($dialect), 'SELECT ' . '"test_table"."field1" AS "alias1", ' . '"test_table"."field2", ' . '"test_table"."field3" AS "alias3", ' . '"test_table"."field4", ' . 'count(DISTINCT "test_table"."field5") AS "alias5", ' . 'substring("test_table"."field6" from \'a..b\') ' . 'FROM "test_table"'); }
public function testSqlFunction() { $criteria = Criteria::create(TestCity::dao())->addProjection(Projection::property(SQLFunction::create('count', SQLFunction::create('substring', BinaryExpression::create('name', BinaryExpression::create(DBValue::create('M....w'), DBValue::create('#'), 'for')->noBrackets(), 'from')->noBrackets()))->setAggregateDistinct()->setAlias('my_alias'))); $this->assertEquals($criteria->toDialectString(ImaginaryDialect::me()), 'SELECT count(DISTINCT substring(custom_table.name from M....w for #)) AS my_alias FROM custom_table'); }
/** * @return BinaryExpression **/ public static function eqId($field, Identifiable $object) { return self::eq($field, DBValue::create($object->getId())); }
/** * @return SelectQuery **/ public function makeTotalCountQuery() { return OSQL::select()->get(SQLFunction::create('count', DBValue::create('*')))->from($this->getTable()); }
/** * @return DialectString * * FIXME: DBI-result, method works only for PostgreSQL. * Research how to generate series of values in MySQL and implement * this. **/ private function getSeriesGenerator($start, $stop, $step = null) { if (!$step) { $result = SQLFunction::create('generate_series', DBValue::create($start)->castTo(DataType::create(DataType::INTEGER)->getName()), DBValue::create($stop)->castTo(DataType::create(DataType::INTEGER)->getName())); } else { $result = SQLFunction::create('generate_series', DBValue::create($start)->castTo(DataType::create(DataType::INTEGER)->getName()), DBValue::create($stop)->castTo(DataType::create(DataType::INTEGER)->getName()), DBValue::create($step)->castTo(DataType::create(DataType::INTEGER)->getName())); } return $result; }
/** * @param string $name * @return TestCity */ public static function getCityByName($name) { return Criteria::create(TestCity::dao())->add(Expression::eq('name', DBValue::create($name)))->get(); }