public function testQueryCopy()
{
$q = new Doctrine_Query();
$q->from('User u');
$q2 = $q->copy();
$this->assertEqual($q->getSql(), $q2->getSql());
$this->assertEqual($q->getSql(), 'SELECT e.id AS e__id, e.name AS e__name, e.loginname AS e__loginname, e.password AS e__password, e.type AS e__type, e.created AS e__created, e.updated AS e__updated, e.email_id AS e__email_id FROM entity e WHERE (e.type = 0)');
}
public function testSelectDistinctIsSupported2()
{
$q = new Doctrine_Query();
$q->select('DISTINCT u.name')->from('User u');
$this->assertEqual($q->getSql(), "SELECT DISTINCT e.id AS e__id, e.name AS e__name FROM entity e WHERE (e.type = 0)");
}
public function testApplyInheritance()
{
$query = new Doctrine_Query();
$query->from('InheritanceDeal d, d.Users u');
$query->where('u.id = 1');
$sql = 'SELECT i.id AS i__id, i.name AS i__name, i2.id AS i2__id, i2.username AS i2__username FROM inheritance_deal i LEFT JOIN inheritance_entity_user i3 ON (i.id = i3.entity_id) AND i3.type = 1 LEFT JOIN inheritance_user i2 ON i2.id = i3.user_id WHERE i2.id = 1';
$this->assertEqual($sql, $query->getSql());
}
/**
* constructor
*
* @param Doctrine_Query $query
*/
public function __construct(Doctrine_Query $query, $viewName)
{
$this->_name = $viewName;
$this->_query = $query;
$this->_query->setView($this);
$this->_conn = $query->getConnection();
$this->_dql = $query->getDql();
$this->_sql = $query->getSql();
}
/**
* Searchable keyword search
*
* @param string $string Keyword string to search for
* @param Doctrine_Query $query Query object to alter. Adds where condition to limit the results using the search index
* @return mixed The Doctrine_Collection or array of ids and relevancy
*/
public function search($string, $query = null)
{
$q = new Doctrine_Search_Query($this->_table);
if ($query instanceof Doctrine_Query) {
$q->query($string, false);
$newQuery = $query->copy();
$query->getSql();
$newQuery->addWhere($query->getRootAlias() . '.id IN(' . $q->getSql() . ')', $q->getParams());
return $newQuery;
} else {
$q->query($string);
return $this->_options['connection']->fetchAll($q->getSql(), $q->getParams());
}
}
/**
* Searchable keyword search
*
* @param string $string Keyword string to search for
* @param Doctrine_Query $query Query object to alter. Adds where condition to limit the results using the search index
* @return mixed The Doctrine_Collection or array of ids and relevancy
*/
public function search($string, $query = null)
{
$q = new Doctrine_Search_Query($this->_table);
if ($query instanceof Doctrine_Query) {
$q->query($string, false);
$newQuery = $query->copy();
$query->getSql();
$key = (array) $this->getOption('table')->getIdentifier();
$newQuery->addWhere($query->getRootAlias() . '.' . current($key) . ' IN (SQL:' . $q->getSql() . ')', $q->getParams());
return $newQuery;
} else {
$q->query($string);
return $this->_options['connection']->fetchAll($q->getSql(), $q->getParams());
}
}
public function testGetLimitSubqueryOrderBy2()
{
$q = new Doctrine_Query();
$q->select('u.name, COUNT(DISTINCT a.id) num_albums');
$q->from('User u, u.Album a');
$q->orderby('num_albums');
$q->groupby('u.id');
try {
// this causes getLimitSubquery() to be used, and it fails
$q->limit(5);
$users = $q->execute();
$count = $users->count();
} catch (Doctrine_Exception $e) {
$this->fail();
}
$this->assertEqual($q->getSql(), 'SELECT e.id AS e__id, e.name AS e__name, COUNT(DISTINCT a.id) AS a__0 FROM entity e LEFT JOIN album a ON e.id = a.user_id WHERE e.id IN (SELECT DISTINCT e2.id FROM entity e2 LEFT JOIN album a2 ON e2.id = a2.user_id WHERE (e2.type = 0) GROUP BY e2.id ORDER BY a__0 LIMIT 5) AND (e.type = 0) GROUP BY e.id ORDER BY a__0');
}
public function testDqlUpdate()
{
$query = new Doctrine_Query($this->connection);
$query->update('EnumTest2 u')
->set('u.status', '?', 'verified');
$this->assertEqual($query->getSql(), 'UPDATE enum_test2 SET status = ?');
$query->execute();
try {
$query = new Doctrine_Query($this->connection);
$ret = $query->query("FROM EnumTest2 WHERE EnumTest2.status = 'verified'");
$this->assertEqual(count($ret), 1);
} catch (Exception $e) {
$this->fail();
}
}
public function testTicket()
{
Doctrine_Manager::getInstance()->setAttribute('use_dql_callbacks', true);
$q = new Doctrine_Query();
$q->select('s.*')->from('Ticket_1116_User s')->where('s.username = ?', array('test'));
// to see the error switch dbh to a real db, the next line will trigger the error
$test = $q->fetchOne();
//will only fail with "real" mysql
$this->assertFalse($test);
$sql = $q->getSql();
// just getSql()?!?! and it works ? the params are ok after this call
$params = $q->getFlattenedParams();
$this->assertEqual(count($params), 1);
// now we have array('test',null) very strange .....
$this->assertEqual($sql, "SELECT u.id AS u__id, u.username AS u__username, u.deleted_at AS u__deleted_at FROM user u WHERE u.username = ? AND (u.deleted_at IS NULL)");
$this->assertEqual($params, array('test'));
//now also this works! (always works witch mock only fails with mysql)
$test = $q->fetchOne();
$this->assertFalse($test);
Doctrine_Manager::getInstance()->setAttribute('use_dql_callbacks', false);
}
public function testNonPortableFunctionsAreSupported()
{
$query = new Doctrine_Query();
// we are using stored procedure here, so adjust portability settings
$this->conn->setAttribute(Doctrine::ATTR_PORTABILITY, Doctrine::PORTABILITY_ALL ^ Doctrine::PORTABILITY_EXPR);
$lat = '13.23';
$lon = '33.23';
$radius = '33';
$query->select("l.*, i18n.*, GeoDistKM(l.lat, l.lon, $lat, $lon) distance")
->from('Location l, l.LocationI18n i18n')
->where('l.id <> ? AND i18n.culture = ?', array(1, 'en'))
->having("distance < $radius")
->orderby('distance ASC')
->groupby('l.id')
->limit(5);
$this->assertEqual($query->getSql(), "SELECT l.id AS l__id, l.lat AS l__lat, l.lon AS l__lon, l2.name AS l2__name, l2.id AS l2__id, l2.culture AS l2__culture, GeoDistKM(l.lat, l.lon, 13.23, 33.23) AS l__0 FROM location l LEFT JOIN location_i18n l2 ON l.id = l2.id WHERE l.id IN (SELECT DISTINCT l3.id FROM location l3 LEFT JOIN location_i18n l4 ON l3.id = l4.id WHERE (l3.id <> ? AND l4.culture = ?) GROUP BY l3.id HAVING l__0 < 33 ORDER BY l__0 ASC LIMIT 5) AND (l.id <> ? AND l2.culture = ?) GROUP BY l.id HAVING l__0 < 33 ORDER BY l__0 ASC");
$this->conn->setAttribute(Doctrine::ATTR_PORTABILITY, Doctrine::PORTABILITY_ALL);
}
public function testEqualNestRelationsQuerying()
{
$this->connection->clear();
$q = new Doctrine_Query();
$q->from('NestTest n')->innerJoin('n.Relatives r')->where('n.id = 1');
$n = $q->execute();
$this->assertEqual($q->getSql(), 'SELECT n.id AS n__id, n.name AS n__name, n2.id AS n2__id, n2.name AS n2__name FROM nest_test n INNER JOIN nest_reference n3 ON n.id = n3.child_id OR n.id = n3.parent_id INNER JOIN nest_test n2 ON (n2.id = n3.parent_id OR n2.id = n3.child_id) AND n2.id != n.id WHERE n.id = 1');
$this->assertEqual($n[0]->Relatives->count(), 5);
}
/**
* Ticket #1038
*/
public function testLimitOffsetLimitSubqueriesForOracleWithGroupByOrderBy()
{
$this->dbh = new Doctrine_Adapter_Mock('oracle');
$conn = $this->manager->openConnection($this->dbh);
$q = new Doctrine_Query($conn);
// The orderBy(p.id) will force p.id to be added to the SELECT part of the
// SELECT DISTINCT subquery because that is required by oracle. This, however,
// can result in duplicated primary keys that would cause incorrect ROWNUM calculations,
// hence an additional subquery used to filter out the primary keys is added.
$q->from('User u')->innerJoin('u.Phonenumber p')
->groupBy('u.name') // !
->orderBy('p.id') // !!
->limit(5)->offset(2);
$correctSql = "SELECT e.id AS e__id, e.name AS e__name, e.loginname AS e__loginname, "
. "e.password AS e__password, e.type AS e__type, e.created AS e__created, "
. "e.updated AS e__updated, e.email_id AS e__email_id, p.id AS p__id, "
. "p.phonenumber AS p__phonenumber, p.entity_id AS p__entity_id "
. "FROM entity e "
. "INNER JOIN phonenumber p ON e.id = p.entity_id "
. "WHERE e.id IN ("
. "SELECT b.id FROM ("
. "SELECT a.*, ROWNUM AS doctrine_rownum "
. "FROM ("
. "SELECT doctrine_subquery_alias.id FROM ("
. "SELECT e2.id, p2.id "
. "FROM entity e2 "
. "INNER JOIN phonenumber p2 ON e2.id = p2.entity_id "
. "WHERE (e2.type = 0) GROUP BY e2.name ORDER BY p2.id"
. ") doctrine_subquery_alias GROUP BY doctrine_subquery_alias.id ORDER BY MIN(ROWNUM)"
. ") a"
. " ) b "
. "WHERE doctrine_rownum BETWEEN 3 AND 7"
. ") AND (e.type = 0) GROUP BY e.name ORDER BY p.id";
$this->assertEqual($q->getSql(), $correctSql);
}
public function testStringColumnInheritance()
{
$q = new Doctrine_Query();
$q->select('g.name')->from('Group g');
$this->assertEqual($q->getSql(), "SELECT e.id AS e__id, e.name AS e__name FROM entity e WHERE (e.type = 1)");
}
public function testReferenfingParentColumnsUsesProperAliases()
{
$q = new Doctrine_Query();
$q->from('CTITest c')->where("c.name = 'Jack'");
$this->assertEqual($q->getSql(), "SELECT c.id AS c__id, c3.added AS c__added, c2.name AS c__name, c2.verified AS c__verified, c.age AS c__age FROM c_t_i_test_parent4 c LEFT JOIN c_t_i_test_parent2 c2 ON c.id = c2.id LEFT JOIN c_t_i_test_parent3 c3 ON c.id = c3.id WHERE c2.name = 'Jack'");
$q = new Doctrine_Query();
$q->from('CTITest c')->where("name = 'Jack'");
$this->assertEqual($q->getSql(), "SELECT c.id AS c__id, c3.added AS c__added, c2.name AS c__name, c2.verified AS c__verified, c.age AS c__age FROM c_t_i_test_parent4 c LEFT JOIN c_t_i_test_parent2 c2 ON c.id = c2.id LEFT JOIN c_t_i_test_parent3 c3 ON c.id = c3.id WHERE c2.name = 'Jack'");
}
public function testAggregateFunctionParser4()
{
$q = new Doctrine_Query();
$q->select('CONCAT(i.price, i.quantity)')->from('QueryTest_Item i');
$this->assertEqual($q->getSql(), 'SELECT CONCAT(q.price, q.quantity) AS q__0 FROM query_test__item q');
}
public function testCorrelatedSubqueryWithInOperatorIsSupported()
{
$q = new Doctrine_Query();
$q->select('u.id')->from('User u')->where('u.name IN (SELECT u2.name FROM User u2 WHERE u2.id = u.id)');
$this->assertEqual($q->getSql(), 'SELECT e.id AS e__id FROM entity e WHERE e.name IN (SELECT e2.name AS e2__name FROM entity e2 WHERE e2.id = e.id AND (e.type = 0)) AND (e.type = 0)');
}
public function testQuerySupportsCustomJoinsAndWithKeyword()
{
$q = new Doctrine_Query();
$q->select('c.*, c2.*, d.*')->from('Record_Country c')->innerJoin('c.City c2 WITH c2.id = 2')->where('c.id = ?', array(1));
$this->assertEqual($q->getSql(), 'SELECT r.id AS r__id, r.name AS r__name, r2.id AS r2__id, r2.name AS r2__name, r2.country_id AS r2__country_id, r2.district_id AS r2__district_id FROM record__country r INNER JOIN record__city r2 ON r.id = r2.country_id AND (r2.id = 2) WHERE r.id = ?');
}
public function testLimitSubqueryNotNeededIfSelectSingleFieldDistinct()
{
$q = new Doctrine_Query();
$q->select('u.id')->distinct()->from('User u LEFT JOIN u.Phonenumber p');
$q->where('u.name = ?');
$q->limit(5);
$users = $q->execute(array('zYne'));
$this->assertEqual(1, $users->count());
$this->assertEqual($q->getSql(), "SELECT DISTINCT e.id AS e__id FROM entity e LEFT JOIN phonenumber p ON e.id = p.entity_id WHERE e.name = ? AND (e.type = 0) LIMIT 5");
}
public function testQueryCopyClone()
{
$query = new Doctrine_Query();
$query->select('u.*')->from('User u');
$sql = $query->getSql();
$data = $query->execute();
$query2 = $query->copy();
$this->assertTrue($sql, $query2->getSql());
$query2->limit(0);
$query2->offset(0);
$query2->select('COUNT(u.id) as nb');
$this->assertTrue($query2->getSql(), 'SELECT COUNT(e.id) AS e__0 FROM entity e WHERE (e.type = 0)');
}