public function testSelect()
{
$query = new Query(Query::SELECT, $this->object);
$query->from($this->object->getTable(), 'User')->fields('id', 'username');
$this->assertEquals($query, $this->object->select('id', 'username'));
}
public function testExecuteQueryStorageCache()
{
$this->loadFixtures('Users');
$storage = new MemoryStorage();
$this->object->setStorage($storage);
$this->assertEquals([], $this->object->getLoggedQueries());
$this->assertFalse($storage->has(__METHOD__));
$query = new Query(Query::SELECT, $this->table);
$query->from('users')->cache(__METHOD__, '+5 minutes');
$result = $this->object->executeQuery($query);
$this->assertEquals([$result], $this->object->getLoggedQueries());
$this->assertTrue($storage->has(__METHOD__));
// Execute it again
$result = $this->object->executeQuery($query);
$this->assertEquals([$result], $this->object->getLoggedQueries());
$this->assertTrue($storage->has(__METHOD__));
}
public function testBuildSubQuery()
{
// In fields
$query = new Query(Query::SELECT, new User());
$query->from('users')->fields($query->subQuery('id')->from('profiles'));
$this->assertRegExp('/SELECT\\s+\\(SELECT\\s+(`|\\")?id(`|\\")? FROM (`|\\")?profiles(`|\\")?\\) FROM (`|\\")?users(`|\\")?;/', $this->object->buildSelect($query));
// In fields with alias
$query = new Query(Query::SELECT, new User());
$query->from('users')->fields($query->subQuery('id')->from('profiles')->asAlias('column'));
$this->assertRegExp('/SELECT\\s+\\(SELECT\\s+(`|\\")?id(`|\\")? FROM (`|\\")?profiles(`|\\")?\\) AS (`|\\")?column(`|\\")? FROM (`|\\")?users(`|\\")?;/', $this->object->buildSelect($query));
// In function in fields
$query = new Query(Query::SELECT, new User());
$query->from('users')->fields(Query::func('UPPER', [$query->subQuery('id')->from('profiles')]));
$this->assertRegExp('/SELECT\\s+UPPER\\(\\(SELECT\\s+(`|\\")?id(`|\\")? FROM (`|\\")?profiles(`|\\")?\\)\\) FROM (`|\\")?users(`|\\")?;/', $this->object->buildSelect($query));
// In where clause w/ function
$query = new Query(Query::SELECT, new User());
$query->from('users')->where('column1', $query->subQuery(Query::func('MAX', ['column2' => 'field']))->from('profiles'));
$this->assertRegExp('/SELECT\\s+\\* FROM (`|\\")?users(`|\\")?\\s+WHERE (`|\\")?column1(`|\\")? = \\(SELECT\\s+MAX\\((`|\\")?column2(`|\\")?\\) FROM (`|\\")?profiles(`|\\")?\\);/', $this->object->buildSelect($query));
// In where clause w/ SOME filter
$query = new Query(Query::SELECT, new User());
$query->from('users')->where('column1', $query->subQuery('column2')->from('profiles')->withFilter('some'));
$this->assertRegExp('/SELECT\\s+\\* FROM (`|\\")?users(`|\\")?\\s+WHERE (`|\\")?column1(`|\\")? = SOME \\(SELECT\\s+(`|\\")?column2(`|\\")? FROM (`|\\")?profiles(`|\\")?\\);/', $this->object->buildSelect($query));
// In where clause using IN operator
$query = new Query(Query::SELECT, new User());
$query->from('users')->where('column1', 'in', $query->subQuery('column2')->from('profiles'));
$this->assertRegExp('/SELECT\\s+\\* FROM (`|\\")?users(`|\\")?\\s+WHERE (`|\\")?column1(`|\\")? IN \\(SELECT\\s+(`|\\")?column2(`|\\")? FROM (`|\\")?profiles(`|\\")?\\);/', $this->object->buildSelect($query));
// In where clause using EXISTS operator
$query = new Query(Query::SELECT, new User());
$query->from('users')->where('column1', $query->subQuery('column2')->from('profiles')->withFilter('exists'));
$this->assertRegExp('/SELECT\\s+\\* FROM (`|\\")?users(`|\\")?\\s+WHERE EXISTS \\(SELECT\\s+(`|\\")?column2(`|\\")? FROM (`|\\")?profiles(`|\\")?\\);/', $this->object->buildSelect($query));
}
