/**
* Compile an Query.
*
* @param Query $query The query to compile.
* @return [string $sql, array $values]
*/
public function compile(Query $query)
{
$type = $query->getType();
$sql = $this->compileComponents($query->getComponents(), $type);
$val = $this->compileValues($query->getComponentValues(), $type);
return array($sql, $val);
}
/**
* @covers ::aggregate()
*/
public function testAggregate()
{
$conn = $this->getMock('SimpleAR\\Database\\Connection', array('select'));
$q = new Query(new SelectBuilder(), $conn);
$sql = 'SELECT `blog_id` AS `blogId` ,AVG(`views`) AS `views_avg` FROM `articles` GROUP BY `blog_id`';
$expected = [['blogId' => 1, 'views_avg' => 100], ['blogId' => 2, 'views_avg' => 123.45], ['blogId' => 3, 'views_avg' => 1200]];
$conn->expects($this->once())->method('select')->with($sql, [])->will($this->returnValue($expected));
$res = $q->root('Article')->groupBy('blogId')->avg('views', 'views_avg');
$this->assertEquals($expected, $res);
// - - - Withour groupBy
$conn = $this->getMock('SimpleAR\\Database\\Connection', array('select'));
$q = new Query(new SelectBuilder(), $conn);
$sql = 'SELECT COUNT(*) FROM `articles`';
$conn->expects($this->once())->method('select')->with($sql, [])->will($this->returnValue([['COUNT(*)' => 12]]));
$res = $q->root('Article')->count();
$this->assertEquals(12, $res);
}
/**
* @covers ::lastInsertId()
*/
public function testLastInsertId()
{
$conn = m::mock('\\SimpleAR\\Database\\Connection[insert]');
$conn->shouldReceive('insert')->once()->andReturn(12);
$q = new Query(new InsertBuilder());
$q->root('Article')->fields(['title'])->values(['Yo.']);
$q->setConnection($conn);
$this->assertEquals(12, $q->lastInsertId());
}
/**
* Add an exists condition to the query.
*
* @param Query $q The Select sub-query.
* @return $this
*/
public function whereExists(Query $query, $logic = 'AND', $not = false)
{
$type = 'Exists';
$cond = compact('type', 'query', 'logic', 'not');
$this->_addWhere($cond);
$this->addValueToQuery($query->getComponentValues());
return $this;
}
/**
* @covers ::whereNotExists
*/
public function testWhereNotExists()
{
$subQuery = new Query(new SelectBuilder());
$subQuery->setInvolvedTable('_', Blog::table());
$subQuery->setRootAlias('__')->root('Article')->where('title', 'Article 1')->whereAttr('blogId', '_/id');
$b = new WhereBuilder();
$b->root('Blog')->whereNotExists($subQuery);
$components = $b->build();
$where[] = ['type' => 'Exists', 'query' => $subQuery, 'logic' => 'AND', 'not' => true];
// There is a sub array because Builders do not flatten value array.
$val['where'] = [['Article 1']];
$this->assertEquals($where, $components['where']);
$this->assertEquals($val, $b->getValues());
}
/**
* Select result of a sub-query.
*
* @param Query $sub The query of which to select result.
* @param string $alias The alias to give to the sub-result.
*/
public function selectSub(Query $sub, $alias)
{
$this->addValueToQuery($sub->getValues());
$subSql = new Expression('(' . $sub->getSql() . ')');
$this->_selectColumn($subSql, $alias);
}
/**
* @covers ::_whereNotExists
*/
public function testCompileWhereNotExists()
{
$compiler = new BaseCompiler();
$select = new Query();
$select->setComponent('columns', ['a' => ['columns' => ['id']]]);
$select->setComponent('from', [new JoinClause('articles')]);
$where = ['type' => 'Exists', 'query' => $select, 'logic' => 'AND', 'not' => true];
//$where = new ExistsCond($select);
$components['where'] = [$where];
$expected = 'WHERE NOT EXISTS (SELECT `id` FROM `articles`)';
$result = $compiler->compileWhere($components);
$this->assertEquals($expected, $result);
}