public function execute(&$value, &$error)
{
$className = $this->getParameter('class');
$columnName = $className . '.' . $this->getParameter('column');
$primaryKeys = sfDoctrine::getTable($className)->getPrimaryKeys();
foreach ($primaryKeys as $primaryKey) {
if (is_null($primaryKeyValue = $this->getContext()->getRequest()->getParameter($primaryKey))) {
}
break;
}
$query = new Doctrine_Query();
$query->from($className);
$value = strtolower($value);
if ($primaryKeyValue === null) {
$query->where($columnName . ' = ?');
$res = $query->execute(array($value));
} else {
$query->where($columnName . ' = ? AND ' . $primaryKey . ' != ?');
$res = $query->execute(array($value, $primaryKeyValue));
}
if (sizeof($res)) {
$error = $this->getParameterHolder()->get('unique_error');
return false;
}
return true;
}
/**
* Get items
*
* @param int $offset
* @param int $itemsPerPage
* @return Doctrine_Collection
*/
public function getItems($offset, $itemsPerPage)
{
if ($itemsPerPage !== null) {
$this->_query->limit($itemsPerPage);
}
if ($offset !== null) {
$this->_query->offset($offset);
}
return $this->_query->execute();
}
public function testInlineSite()
{
$yml = <<<END
---
Ticket_Product:
Product_1:
name: book
Site:
name: test
END;
try {
file_put_contents('test.yml', $yml);
Doctrine_Core::loadData('test.yml', true);
$this->conn->clear();
$query = new Doctrine_Query();
$query->from('Ticket_Product p, p.Site s')->where('p.name = ?', 'book');
$product = $query->execute()->getFirst();
$this->assertEqual($product->name, 'book');
$this->assertEqual(is_object($product->Site), true);
$this->assertEqual($product->Site->name, 'test');
$this->pass();
} catch (Exception $e) {
//echo $e->getMessage();
$this->fail();
}
unlink('test.yml');
}
/**
* Implementation of method from Zend_Paginator_Adapter_Interface.
*
* @param integer $offset
* @param integer $itemsPerPage
* @return array[numeric|whatever]=>array|Doctrine_Record
*/
public function getItems($offset, $itemsPerPage)
{
$this->_query->limit($itemsPerPage);
$this->_query->offset($offset);
$result = $this->_query->execute(array(), $this->_hydrationMode);
return $result;
}
public function testQuerySupportsIdentifierQuotingInWherePart()
{
$q = new Doctrine_Query();
$q->parseDqlQuery('SELECT u.name FROM User u WHERE u.id = 3');
$this->assertEqual($q->getSqlQuery(), 'SELECT "e"."id" AS "e__id", "e"."name" AS "e__name" FROM "entity" "e" WHERE ("e"."id" = 3 AND ("e"."type" = 0))');
$q->execute();
}
public function testZeroValuesMaintained3()
{
$q = new Doctrine_Query();
$q->from('ZeroValueTest');
$users = $q->execute();
$this->assertIdentical($users[0]['is_super_admin'], false);
}
public function testDiffForOneToManyRelatedCollection()
{
$q = new Doctrine_Query();
$q->from('User u LEFT JOIN u.Phonenumber p')
->where('u.id = 8');
$coll = $q->execute();
$this->assertEqual($coll->count(), 1);
$this->assertEqual($coll[0]->Phonenumber->count(), 3);
$this->assertTrue($coll[0]->Phonenumber instanceof Doctrine_Collection);
unset($coll[0]->Phonenumber[0]);
$coll[0]->Phonenumber->remove(2);
$this->assertEqual(count($coll[0]->Phonenumber->getSnapshot()), 3);
$coll[0]->save();
$this->assertEqual($coll[0]->Phonenumber->count(), 1);
$this->connection->clear();
$q = new Doctrine_Query();
$q = Doctrine_Query::create()->from('User u LEFT JOIN u.Phonenumber p')->where('u.id = 8');
$coll = $q->execute();
$this->assertEqual($coll[0]->Phonenumber->count(), 1);
}
public function execute($params = array(), $hydrationMode = null)
{
$breadcrumb = $this->getBreadcrumb();
$result = parent::execute($params, $hydrationMode);
$this->restoreBreadcrumb($breadcrumb);
return $result;
}
/**
public function testShortAliasesWithSingleComponent() {
$q = new Doctrine_Query();
$q->select('u.name')->from('User u');
$this->assertEqual($q->getSqlQuery(), 'SELECT e.id AS e__id, e.name AS e__name FROM entity e WHERE (e.type = 0)');
}
*/
public function testShortAliasesWithOneToManyLeftJoin()
{
$q = new Doctrine_Query();
$q->select('u.name, p.id')->from('User u LEFT JOIN u.Phonenumber p');
$this->assertEqual($q->getSqlQuery(), 'SELECT e.id AS e__id, e.name AS e__name, p.id AS p__id FROM entity e LEFT JOIN phonenumber p ON e.id = p.entity_id WHERE (e.type = 0)');
$users = $q->execute();
$this->assertEqual($users->count(), 8);
}
/**
* Format the value returned from rec_query() into an array radix.
*
* @param Doctrine_Query $q
* @return array $radix
*/
protected function format_query_radix(Doctrine_Query $q)
{
$recs = $q->execute();
$radix = array();
foreach ($recs as $rec) {
$radix[] = $this->format_radix($rec);
}
return $radix;
}
public static function getEschoolTypes($public_only = false)
{
$q = new Doctrine_Query();
$q->select('e.*');
$q->from('GcrEschoolType e');
if ($public_only) {
$q->where('e.is_public = ?', 't');
}
return $q->execute();
}
public function testFetchArraySupportsTwoAggregatesInRelationAndRoot()
{
$q = new Doctrine_Query();
$q->select("u.*, p.*, CONCAT(u.name, '_1') concat1, CONCAT(u.name, '_2') concat2, CONCAT(p.phonenumber, '_3') concat3, CONCAT(p.phonenumber, '_3') concat4")->from('User u')->innerJoin('u.Phonenumber p')->where("u.name = 'zYne'");
$users = $q->execute(array(), Doctrine::HYDRATE_ARRAY);
$this->assertEqual($users[0]['concat1'], 'zYne_1');
$this->assertEqual($users[0]['concat2'], 'zYne_2');
$this->assertEqual($users[0]['Phonenumber'][0]['concat3'], '123 123_3');
$this->assertTrue(isset($users[0]['Phonenumber'][0]['concat4']));
}
public static function getTypes($public_only = false)
{
$q = new Doctrine_Query();
$q->select('i.*');
$q->from('GcrInstitutionType i');
if ($public_only) {
$q->where('i.is_public = ?', 't');
}
return $q->execute();
}
public function testHydratation()
{
$q = new Doctrine_Query();
$q->select('t.name')->from('NewTag t INDEXBY t.name');
try {
$results = $q->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
} catch (Exception $e) {
$this->fail($e->getMessage());
}
}
public function testGetLimitSubqueryWithOrderByOnAggregateValuesAndColumns()
{
$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, u.name');
$q->groupby('u.id');
$q->limit(5);
$q->execute();
$this->assertEqual($this->dbh->pop(), '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 doctrine_subquery_alias.id FROM (SELECT DISTINCT e2.id, COUNT(DISTINCT a2.id) AS a2__0 FROM entity e2 LEFT JOIN album a2 ON e2.id = a2.user_id WHERE (e2.type = 0) GROUP BY e2.id ORDER BY a2__0, e2.name LIMIT 5) AS doctrine_subquery_alias) AND (e.type = 0) GROUP BY e.id ORDER BY a__0, e.name');
}
public function testUnknownColumnWithinFunctionInSelectClauseThrowsException()
{
$q = new Doctrine_Query();
try {
$q->parseDqlQuery('SELECT CONCAT(u.name, u.unknown) FROM User u');
$q->execute();
$this->fail();
} catch (Doctrine_Query_Exception $e) {
$this->pass();
}
}
public function testDoctrineQueryJoinSelect()
{
$q = new Doctrine_Query();
$q->select('person.id, points.total')->from('T1015_Person person')->innerJoin('person.T1015_Points points WITH person.id = 1');
$results = $q->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
//var_dump($results);
$person = $results[0];
// number of points for person id of 1 should be 15
$this->assertEqual(15, $person['T1015_Points']['total']);
//THIS WILL FAIL
}
public function execute(&$value, &$error)
{
$value_temp = preg_replace('#\\s+#', ' ', $value);
$value_temp = strtolower(trim($value_temp));
$query = new Doctrine_Query();
$query->from('UserPrivateData')->where('(login_name = ?) OR (username = ?) OR (topo_name = ?)');
$res = $query->execute(array($value_temp, $value_temp, $value_temp));
if (sizeof($res)) {
$error = $this->getParameterHolder()->get('login_unique_error');
return false;
}
return true;
}
public function testGetLimitSubqueryWithHavingOnAggregateValuesIncorrectAlias()
{
$q = new Doctrine_Query();
$q->select('u.name, COUNT(a.id) num_albums');
$q->from('User u, u.Album a');
$q->orderby('num_albums DESC');
$q->having('num_albums > 0');
$q->groupby('u.id');
$q->limit(5);
$q->execute();
$this->dbh->pop();
$this->assertEqual($this->dbh->pop(), 'SELECT DISTINCT e2.id, COUNT(a2.id) AS a2__0 FROM entity e2 LEFT JOIN album a2 ON e2.id = a2.user_id WHERE (e2.type = 0) GROUP BY e2.id HAVING a2__0 > 0 ORDER BY a2__0 DESC LIMIT 5');
}
public function testSubqueryExtractionUsesWrongAliases()
{
$q = new Doctrine_Query();
$q->from('RelX x');
$q->leftJoin('x.y xy');
$q->where('x.created_at IN (SELECT MAX(x2.created_at) latestInCategory FROM RelX x2 WHERE x.category = x2.category)');
$q->limit(5);
//echo $sql = $q->getSqlQuery();
// echo $sql;
$xs = $q->execute();
// Doctrine_Ticket_1254_TestCase : method testSubqueryExtractionUsesWrongAliases failed on line 76
// This fails sometimes at
$this->assertEqual(2, count($xs));
}
/**
* Get the version
*
* @param Doctrine_Record $record
* @param mixed $version
* @return array An array with version information
*/
public function getVersion(Doctrine_Record $record, $version)
{
$className = $this->_options['className'];
$q = new Doctrine_Query();
$values = array();
foreach ((array) $this->_options['table']->getIdentifier() as $id) {
$conditions[] = $className . '.' . $id . ' = ?';
$values[] = $record->get($id);
}
$where = implode(' AND ', $conditions) . ' AND ' . $className . '.' . $this->_options['versionColumn'] . ' = ?';
$values[] = $version;
$q->from($className)->where($where);
return $q->execute($values, Doctrine::HYDRATE_ARRAY);
}
public function execute(&$value, &$error)
{
$value_temp = preg_replace('#\\s+#', ' ', $value);
$value_temp = trim($value_temp);
$user_id = sfContext::getInstance()->getUser()->getId();
$query = new Doctrine_Query();
$query->from('UserPrivateData')->where('id != ? AND username = ?');
$res = $query->execute(array($user_id, $value_temp));
if (sizeof($res)) {
$error = $this->getParameterHolder()->get('nickname_unique_error');
return false;
}
return true;
}
public function testHasOneMultiLevelRelations()
{
$policy_code = new PolicyCodeN();
$policy_code->code = 1;
$policy_code->description = "Special Policy";
$policy_code->save();
$coverage_code = new CoverageCodeN();
$coverage_code->code = 1;
$coverage_code->description = "Full Coverage";
$coverage_code->save();
$coverage_code = new CoverageCodeN();
$coverage_code->code = 3;
# note we skip 2
$coverage_code->description = "Partial Coverage";
$coverage_code->save();
$liability_code = new LiabilityCodeN();
$liability_code->code = 1;
$liability_code->description = "Limited Territory";
$liability_code->save();
$rate = new RateN();
$rate->policy_code = 1;
$rate->coverage_code = 3;
$rate->liability_code = 1;
$rate->total_rate = 123.45;
$rate->save();
$policy = new PolicyN();
$policy->rate_id = 1;
$policy->policy_number = "123456789";
$policy->save();
$q = new Doctrine_Query();
# If I use
# $p = $q->from('PolicyN p')
# this test passes, but there is another issue just not reflected in this test yet, see "in my app" note below
$q->from('PolicyN p, p.RateN r, r.PolicyCodeN y, r.CoverageCodeN c, r.LiabilityCodeN l')->where('(p.id = ?)', array('1'));
$p = $q->execute()->getFirst();
$this->assertEqual($p->rate_id, 1);
$this->assertEqual($p->RateN->id, 1);
$this->assertEqual($p->RateN->policy_code, 1);
$this->assertEqual($p->RateN->coverage_code, 3);
# fail
$this->assertEqual($p->RateN->liability_code, 1);
$c = $p->RateN->coverage_code;
$c2 = $p->RateN->CoverageCodeN->code;
$c3 = $p->RateN->coverage_code;
$this->assertEqual($c, $c2);
# fail
$this->assertEqual($c, $c3);
# in my app this fails as well, but I can't reproduce this
#echo "Values " . serialize(array($c, $c2, $c3));
}
public function testQueryWithOneToManyRelation() {
$q = new Doctrine_Query($this->connection);
$q->from("User.Phonenumber");
$users = $q->execute(array(), Doctrine::FETCH_VHOLDER);
$this->assertEqual($users->count(), 8);
$this->assertTrue($users[0] instanceof Doctrine_ValueHolder);
$this->assertTrue($users[3] instanceof Doctrine_ValueHolder);
$this->assertTrue($users[7] instanceof Doctrine_ValueHolder);
$this->assertEqual(count($users[0]->Phonenumber), 1);
$this->assertEqual(count($users[1]->Phonenumber), 3);
$this->assertEqual(count($users[2]->Phonenumber), 1);
$this->assertEqual(count($users[3]->Phonenumber), 1);
$this->assertEqual(count($users[4]->Phonenumber), 3);
}
public function testAggregateValueMappingSupportsLeftJoins()
{
foreach ($this->_albums as $album) {
$album->clearRelated();
}
$q = new Doctrine_Query();
$q->select('a.name, COUNT(s.id) count')->from('Album a')->leftJoin('a.Song s')->groupby('a.id');
$albums = $q->execute();
try {
// Collection[0] should refer to the object with aggregate value
$this->assertEqual($albums[0]['count'], 4);
} catch (Exception $e) {
$this->fail('count aggregate value should be available');
}
}
public function testSelfReferencingWithNestedOrderBy()
{
$query = new Doctrine_Query();
$query->from('Forum_Category.Subcategory.Subcategory');
$query->orderby('Forum_Category.id ASC, Forum_Category.Subcategory.name DESC');
$coll = $query->execute();
$category = $coll[0];
$this->assertEqual($category->name, 'Root');
$this->assertEqual($category->Subcategory[0]->name, 'Sub 2');
$this->assertEqual($category->Subcategory[1]->name, 'Sub 1');
$this->assertEqual($category->Subcategory[1]->Subcategory[0]->name, 'Sub 1 Sub 1');
$this->assertEqual($category->Subcategory[1]->Subcategory[1]->name, 'Sub 1 Sub 2');
$this->assertEqual($category->Subcategory[0]->Subcategory[0]->name, 'Sub 2 Sub 1');
$this->assertEqual($category->Subcategory[0]->Subcategory[1]->name, 'Sub 2 Sub 2');
$this->connection->clear();
}
public function testSubqueryExtractionUsesWrongAliases()
{
$q = new Doctrine_Query();
$q->from('RelX x');
$q->leftJoin('x.y xy');
$q->where('x.created_at IN (SELECT MAX(x2.created_at) latestInCategory FROM RelX x2 WHERE x.category = x2.category)');
$q->limit(5);
//echo $sql = $q->getSql();
// echo $sql;
$xs = $q->execute();
$this->assertEqual(3, count($xs));
}
public function testManyToManyRelationFetchingWithAliasesAndCustomPKs2() {
$q = new Doctrine_Query();
try {
$q->from('M2MTest2 m INNER JOIN m.JC3');
$this->pass();
} catch(Doctrine_Exception $e) {
$this->fail();
}
try {
$q->execute();
$this->pass();
} catch(Doctrine_Exception $e) {
$this->fail();
}
}
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');
}
/**
* Actualitza les sessions d'una setmana. Es pot especificar la data a la URL
* /horari/actualitza/X/Y/Z on X és el dia, Y el mes i Z l'any amb
* quatre xifres. Si no s'especifica data s'actualitza les sessions de la
* setmana actual.
*
* @param sfRequest $request A request object
*/
public function executeActualitza(sfWebRequest $request)
{
Doctrine_Manager::connection()->setAttribute(Doctrine_Core::ATTR_AUTO_FREE_QUERY_OBJECTS, true);
$query = new Doctrine_Query();
$query->from('CarreraCurs');
$carreresCursos = $query->execute();
// És un paràmetre opcional del mètode actualitza, l'inicialitzem a NULL.
$date = NULL;
// S'ha especificat una data per actualitzar
if ($request->getParameter('any')) {
$date = $request->getParameter('any') . '/' . $request->getParameter('mes') . '/' . $request->getParameter('dia');
}
foreach ($carreresCursos as $carreraCurs) {
$taulaSessions = Doctrine::getTable('Sessio');
$taulaSessions->actualitza($carreraCurs, $date);
}
}
