public function testSelectStatementWithOrder()
{
$table = new TableExpression('table', 't');
$field1 = new FieldExpression('field1', $table, 'f1');
$field2 = new FieldExpression('field2', $table);
$field3 = new FieldExpression('field3');
$statement = new SelectStatement();
$statement->addField($field1);
$statement->addField($field2);
$statement->addField($field3);
$statement->addTable($table);
$statement->addOrderBy(new OrderExpression($field1));
$statement->addOrderBy(new OrderExpression($field2, OrderExpression::DIRECTION_DESC));
$statement->addOrderBy(new OrderExpression($field3));
$sql = $this->parser->parseStatement($statement);
$this->assertNotNull($sql);
$this->assertEquals('SELECT `t`.`field1` AS `f1`, `t`.`field2`, `field3` FROM `table` AS `t` ORDER BY `f1` ASC, `t`.`field2` DESC, `field3` ASC', $sql);
}
/**
* Get the SQL for a select statement
* @param zibo\library\database\manipulation\statement\manipulation\SelectStatement $statement
* @return string SQL of the select statement
*/
protected function parseSelectStatement(SelectStatement $statement)
{
$operator = $statement->getOperator();
$sql = 'SELECT ';
if ($statement->isDistinct()) {
$sql .= 'DISTINCT ';
}
$fields = $statement->getFields();
if (empty($fields)) {
$sql . '*';
} else {
$sql .= $this->parseExpressionsForSelect($fields);
}
$tables = $statement->getTables();
if ($tables) {
$sql .= ' FROM ' . $this->parseTableExpressionsForFrom($tables);
}
$conditions = $statement->getConditions();
if ($conditions) {
$sql .= ' WHERE ' . $this->parseConditions($conditions, $operator, false);
}
$group = $statement->getGroupBy();
if ($group) {
$sql .= ' GROUP BY ' . $this->parseExpressions($group, ', ', true);
}
$having = $statement->getHaving();
if ($having) {
$sql .= ' HAVING ' . $this->parseConditions($having, $operator, true);
}
$order = $statement->getOrderBy();
if ($order) {
$sql .= ' ORDER BY ' . $this->parseExpressions($order, ', ', false);
}
$limit = $statement->getLimit();
if ($limit) {
$sql .= $this->parseLimitExpression($limit);
}
return $sql;
}
/**
* Adds the order by expressions to the statement
* @param array $orderBy Array with database order expressions
* @return null
*/
private function addOrderBy(array $orderBy)
{
foreach ($orderBy as $order) {
$this->statement->addOrderBy($order);
}
}
/**
* Gets the names of the models used by this query
* @param zibo\library\database\manipulation\statement\SelectStatement $statemenet
* @return array Array with the names of the models used by this query
*/
private function getUsedModels(SelectStatement $statement)
{
$usedModels = array();
$tables = $statement->getTables();
foreach ($tables as $table) {
$usedModels[] = $table->getName();
$joins = $table->getJoins();
foreach ($joins as $join) {
$modelName = $join->getTable()->getName();
$usedModels[$modelName] = $modelName;
}
}
return $usedModels;
}
/**
* Gets the primary keys of the has many values
* @param Model $model Model of the has many field
* @param string $foreignKey Name of the foreign key to this model
* @param integer $id Value for the foreign key
* @return array Array with the primary key of the has many value as key and value
*/
private function findOldHasManyAndBelongsTo($model, $foreignKey, $id)
{
$condition = new SimpleCondition(new FieldExpression($foreignKey), new ScalarExpression($id), Condition::OPERATOR_EQUALS);
$statement = new SelectStatement();
$statement->addTable(new TableExpression($model->getName()));
$statement->addField(new FieldExpression(ModelTable::PRIMARY_KEY));
$statement->addCondition($condition);
$result = $this->executeStatement($statement);
$model->clearCache();
$oldHasMany = array();
foreach ($result as $record) {
$oldHasMany[$record[ModelTable::PRIMARY_KEY]] = $record[ModelTable::PRIMARY_KEY];
}
return $oldHasMany;
}