public function fetchRelatedFor(Doctrine_Record $record)
{
$id = $record->getIncremented();
if (empty($id) || !$this->definition['table']->getAttribute(Doctrine_Core::ATTR_LOAD_REFERENCES)) {
return Doctrine_Collection::create($this->getTable());
} else {
$q = new Doctrine_RawSql($this->getTable()->getConnection());
$assocTable = $this->getAssociationFactory()->getTableName();
$tableName = $record->getTable()->getTableName();
$identifierColumnNames = $record->getTable()->getIdentifierColumnNames();
$identifier = array_pop($identifierColumnNames);
$sub = 'SELECT ' . $this->getForeignRefColumnName() . ' FROM ' . $assocTable . ' WHERE ' . $this->getLocalRefColumnName() . ' = ?';
$condition[] = $tableName . '.' . $identifier . ' IN (' . $sub . ')';
$joinCondition[] = $tableName . '.' . $identifier . ' = ' . $assocTable . '.' . $this->getForeignRefColumnName();
if ($this->definition['equal']) {
$sub2 = 'SELECT ' . $this->getLocalRefColumnName() . ' FROM ' . $assocTable . ' WHERE ' . $this->getForeignRefColumnName() . ' = ?';
$condition[] = $tableName . '.' . $identifier . ' IN (' . $sub2 . ')';
$joinCondition[] = $tableName . '.' . $identifier . ' = ' . $assocTable . '.' . $this->getLocalRefColumnName();
}
$q->select('{' . $tableName . '.*}, {' . $assocTable . '.*}')->from($tableName . ' INNER JOIN ' . $assocTable . ' ON ' . implode(' OR ', $joinCondition))->where(implode(' OR ', $condition))->orderBy($tableName . '.' . $identifier . ' ASC');
if ($orderBy = $this->getOrderByStatement($tableName, true)) {
$q->addOrderBy($orderBy);
}
$q->addComponent($tableName, $this->getClass());
$path = $this->getClass() . '.' . $this->getAssociationFactory()->getComponentName();
if ($this->definition['refClassRelationAlias']) {
$path = $this->getClass() . '.' . $this->definition['refClassRelationAlias'];
}
$q->addComponent($assocTable, $path);
$params = $this->definition['equal'] ? array($id, $id) : array($id);
$res = $q->execute($params);
return $res;
}
}
/**
* Returns all the groups for this user id that are in the array of groups passed in.
*
* @param unknown_type $userId
* @param unknown_type $uid
* @param unknown_type $gids
* @return unknown
*/
public static function getGroupsByUserIdAndGroupIds($userId, $gids)
{
$inPart = implode(",", $gids);
$q = new Doctrine_RawSql();
$q->select('{g.*}')->from('groups g INNER JOIN groups_member gm ON g.gid=gm.gid')->where("uid={$userId} AND g.gid IN ({$inPart})")->addComponent('g', 'RingsideGroup g')->addComponent('gm', 'g.RingsideGroupsMember gm');
$groups = $q->execute();
return $groups;
}
public function testRawSQLaddWhere()
{
//this checks for an error in parseDqlQueryPart
$query = new Doctrine_RawSql();
$q = $query->select('{i.*}')->addComponent('i', 'T1195_Item i')->from('items i')->addWhere('i.col1 = ?', 'a')->addWhere('i.col2 = ?', 'a');
$res = $q->execute();
$this->assertEqual($res->count(), 1);
}
public function testDoctrineRawSQLJoinSelect()
{
$q = new Doctrine_RawSql();
$q->select('{person.id}, {points.total}')->from('person person INNER JOIN points points ON person.id = points.person_id AND person.id=1')->addComponent('person', 'T1015_Person person')->addComponent('points', 'person.T1015_Points points');
$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']);
}
public function getIntitulePostes()
{
$q = new Doctrine_RawSql();
$q->select('{a.intituleposte}')->from('mission a')->addComponent('a', 'mission')->distinct(true)->where('a.intituleposte != ?', '')->orderBy('a.intituleposte');
$results = $q->execute(array(), Doctrine::HYDRATE_NONE);
$intitulepostes = array();
foreach ($results as $result) {
$intitulepostes[$result['0']] = $result['0'];
}
return $intitulepostes;
}
/**
* Obtiene la lista de miembros de celula dado el id del lider, la lista
* solo muestra los miembros que pueden habrir célula
* @param integer $id_lider el Identificacor del lider
* @return MiembroCelula Lista de miembros encontrados
*/
public function getMiembrosLideresPorLider($id_lider, $genero)
{
$q = new Doctrine_RawSql();
$q->select('d.*');
$q->from('miembro_celula mc inner join celula c on c.id = mc.celula_id inner join sf_guard_user d on mc.discipulo_id = d.id');
$q->where('c.discipulo_lider_id = ? OR d.tipo_discipulo = ?', array($id_lider, 4));
$q->andWhere('d.genero = ?', $genero);
$q->addComponent('d', 'Discipulo d');
// $q->orWhere('d.tipo_discipulo = ?', 4);
return $q->execute();
}
public static function getFriends($userid, $exclude = null)
{
$limit = sfConfig::get('app_autocomplete_suggest_max_results');
$outings_limit = 20;
$exclude[] = $userid;
$exclude = array_unique($exclude);
// get the 5 users we shared most of our last 20 outings with
// FIXME: any way to get some simplier / better query?
$q = new Doctrine_RawSql();
$q->select('{u.id}, {m.culture}, {m.name}')->from('(SELECT a.main_id AS id, COUNT(a.main_id) FROM ' . '(SELECT linked_id FROM app_documents_associations WHERE type=\'uo\' AND main_id = ? ORDER BY linked_id DESC LIMIT ?) o ' . 'LEFT JOIN app_documents_associations a ON a.type=\'uo\' AND a.linked_id = o.linked_id ' . 'AND a.main_id NOT IN (' . implode(',', array_fill(0, sizeof($exclude), '?')) . ') ' . 'GROUP BY main_id ORDER BY COUNT DESC LIMIT ?) AS u LEFT JOIN users_i18n m on m.id = u.id')->addComponent('u', 'User u')->addComponent('m', 'u.UserI18n m');
return $q->execute(array_merge(array($userid, $outings_limit), $exclude, array($limit)), Doctrine::FETCH_ARRAY);
}
public function fetchRelatedFor(Doctrine_Record $record)
{
$id = $record->getIncremented();
$q = new Doctrine_RawSql();
$assocTable = $this->getAssociationFactory()->getTableName();
$tableName = $record->getTable()->getTableName();
$identifier = $record->getTable()->getIdentifier();
$sub = 'SELECT ' . $this->getForeign() . ' FROM ' . $assocTable . ' WHERE ' . $this->getLocal() . ' = ?';
$sub2 = 'SELECT ' . $this->getLocal() . ' FROM ' . $assocTable . ' WHERE ' . $this->getForeign() . ' = ?';
$q->select('{' . $tableName . '.*}, {' . $assocTable . '.*}')->from($tableName . ' INNER JOIN ' . $assocTable . ' ON ' . $tableName . '.' . $identifier . ' = ' . $assocTable . '.' . $this->getLocal() . ' OR ' . $tableName . '.' . $identifier . ' = ' . $assocTable . '.' . $this->getForeign())->where($tableName . '.' . $identifier . ' IN (' . $sub . ') OR ' . $tableName . '.' . $identifier . ' IN (' . $sub2 . ')');
$q->addComponent($tableName, $record->getTable()->getComponentName());
$q->addComponent($assocTable, $record->getTable()->getComponentName() . '.' . $this->getAssociationFactory()->getComponentName());
return $q->execute(array($id, $id));
}
public function getUnmarkedEpisodesThatMissedDeadlines($subreddit_id = null)
{
$sql = "`episode_assignment` ea\nLEFT JOIN `episode` ON (`episode`.`id` = ea.`episode_id`";
$sql .= is_null($subreddit_id) ? '' : " AND `episode`.`subreddit_id` = " . $subreddit_id;
$sql .= ")\n/* Joing the deadline for the deadline seconds */\nLEFT JOIN `deadline` ON (`deadline`.`author_type_id` = ea.`author_type_id` AND `deadline`.`subreddit_id` = `episode`.`subreddit_id`)\n/* Make sure we're using the right deadlines for the episode's subreddit */\nWHERE (ea.`missed_deadline` <> 1 OR ea.`missed_deadline` IS NULL)\n/* Is the episode past the deadline for the assignment in question? */\nAND UNIX_TIMESTAMP(`episode`.`release_date`) < (UNIX_TIMESTAMP() + `deadline`.`seconds`)";
$q = new Doctrine_RawSql();
$q->select('{ea.*}')->from($sql)->addComponent('ea', 'EpisodeAssignment ea');
$assignments = $q->execute();
return $assignments;
}
/**
* returns a list of ids from online-identities connected to
* the user
*
* @author Matthias Pfefferle
* @param int $pUserId
* @return array
*/
public static function getIdsOfFriendsByUserId($pUserId)
{
$lOis = array();
$lQueryString = array();
// @todo refactor this to query only the ids
$lOwnedOis = OnlineIdentityTable::retrieveByUserId($pUserId);
foreach ($lOwnedOis as $lIdentity) {
if ($lIdentity->getFriendIds() != '') {
$lQueryString[] = '(oi.community_id = ' . $lIdentity->getCommunityId() . ' AND oi.original_id IN (' . $lIdentity->getFriendIds() . '))';
}
}
$q = new Doctrine_RawSql();
$q->select('{oi.id}')->from('online_identity oi')->distinct()->addComponent('oi', 'OnlineIdentity');
// !empty means, ther's at least 1 contact
if (!empty($lQueryString)) {
$q->where(implode(' OR ', $lQueryString));
$lOis = $q->execute(array(), Doctrine_Core::HYDRATE_NONE);
}
return HydrationUtils::flattenArray($lOis);
}
/**
* Get nearest docs to a point
*/
public static function getNearest($lon, $lat, $model = 'Document', $exclude = null)
{
$module = c2cTools::model2module($model);
$distance = sfConfig::get('app_autocomplete_near_max_distance');
$limit = sfConfig::get('app_autocomplete_suggest_max_results');
// We have to use raw SQL because of postgis functions
// Not that we first filter width ST_DWithin and only after that compute the dstance,
// which is more effective
$q = new Doctrine_RawSql();
$q->select('{d.id}, {m.culture}, {m.name}')->from('(SELECT id FROM ' . $module . ' ' . 'WHERE ST_DWithin(geom, ST_Transform(ST_SetSRID(ST_MakePoint(?, ?), 4326), 900913), ?) ' . 'AND redirects_to IS NULL ' . (empty($exclude) ? '' : ' AND id NOT IN (' . implode(',', array_fill(0, sizeof($exclude), '?')) . ') ') . 'ORDER BY ST_Distance(geom, ST_Transform(ST_SetSRID(ST_MakePoint(?, ?), 4326), 900913)) ' . 'LIMIT ?) AS d ' . 'LEFT JOIN ' . $module . '_i18n m ON d.id = m.id')->addComponent('d', $model . ' d')->addComponent('m', 'd.' . $model . 'I18n m');
return $q->execute(array_merge(array($lon, $lat, $distance), (array) $exclude, array($lon, $lat, $limit)), Doctrine::FETCH_ARRAY);
}
/**
* Generate a messages collection containing all messages exchanged between the message sender and the recipient
* @return
*/
function getMessageHistory()
{
// Use doctrine raw sql
$q = new Doctrine_RawSql();
$q->select('{m.*}, {mr.*}');
$q->from('message m INNER JOIN messagerecipient mr ON (m.id = mr.messageid AND ISNULL(m.commentid))');
$q->where("(m.senderid = '" . $this->getSenderID() . "' AND mr.recipientid = '" . $this->getRecipient()->getID() . "') OR \n\t\t\t\t\t(m.senderid = '" . $this->getRecipient()->getID() . "' AND mr.recipientid = '" . $this->getSenderID() . "') ORDER BY m.datecreated ASC ");
$q->addComponent('m', 'Message m');
$q->addComponent('mr', 'm.recipients mr');
$result = $q->execute();
// debugMessage($result->toArray());
return $result;
}
#!/usr/bin/env php
<?php
require_once realpath(dirname(__FILE__) . '/../app/init.php');
require_once 'AIR2_DBManager.php';
/**
* create-tbl.php
*
* This utility lets you create single tables from doctrine models.
*
* @package default
*/
AIR2_DBManager::init();
$conn = AIR2_DBManager::get_connection();
$q = new Doctrine_RawSql($conn);
$q->select('*')->from('image i')->where("img_ref_type = 'L'")->addComponent('i', 'ImageOrgLogo i');
$logos = $q->execute();
foreach ($logos as $logo) {
if (!$logo->get_path_to_orig_asset()) {
continue;
}
printf("org %s logo %s\n", $logo->Organization->org_name, $logo->get_path_to_orig_asset());
// must copy to temp path because make_sizes() will unlink all the orig files
$tmp_path = "/tmp/" . $logo->img_file_name;
copy($logo->get_path_to_orig_asset(), $tmp_path);
$logo->set_image($tmp_path);
$logo->make_sizes();
}
/**
public function fetchRelatedFor(Doctrine_Record $record)
{
$id = $record->getIncremented();
if (empty($id) || ! $this->definition['table']->getAttribute(Doctrine::ATTR_LOAD_REFERENCES)) {
return new Doctrine_Collection($this->getTable());
} else {
$q = new Doctrine_Query();
$c = $this->getTable()->getComponentName();
$a = substr($c, 0, 1);
$c2 = $this->getAssociationTable()->getComponentName();
$a2 = substr($c2, 0, 1);
$q->from($c)
->innerJoin($c . '.' . $c2)
$sub = 'SELECT ' . $this->getForeign()
. ' FROM ' . $c2
. ' WHERE ' . $this->getLocal()
. ' = ?';
}
}
*/
public function fetchRelatedFor(Doctrine_Record $record)
{
$id = $record->getIncremented();
if (empty($id) || !$this->definition['table']->getAttribute(Doctrine::ATTR_LOAD_REFERENCES)) {
return new Doctrine_Collection($this->getTable());
} else {
$q = new Doctrine_RawSql();
$assocTable = $this->getAssociationFactory()->getTableName();
$tableName = $record->getTable()->getTableName();
$identifier = $record->getTable()->getIdentifier();
$sub = 'SELECT ' . $this->getForeign() . ' FROM ' . $assocTable . ' WHERE ' . $this->getLocal() . ' = ?';
$condition[] = $tableName . '.' . $identifier . ' IN (' . $sub . ')';
$joinCondition[] = $tableName . '.' . $identifier . ' = ' . $assocTable . '.' . $this->getForeign();
if ($this->definition['equal']) {
$sub2 = 'SELECT ' . $this->getLocal() . ' FROM ' . $assocTable . ' WHERE ' . $this->getForeign() . ' = ?';
$condition[] = $tableName . '.' . $identifier . ' IN (' . $sub2 . ')';
$joinCondition[] = $tableName . '.' . $identifier . ' = ' . $assocTable . '.' . $this->getLocal();
}
$q->select('{' . $tableName . '.*}, {' . $assocTable . '.*}')->from($tableName . ' INNER JOIN ' . $assocTable . ' ON ' . implode(' OR ', $joinCondition))->where(implode(' OR ', $condition));
$q->addComponent($tableName, $record->getTable()->getComponentName());
$q->addComponent($assocTable, $record->getTable()->getComponentName() . '.' . $this->getAssociationFactory()->getComponentName());
$params = $this->definition['equal'] ? array($id, $id) : array($id);
return $q->execute($params);
}
}
public function testConvenienceMethods()
{
$query = new Doctrine_RawSql($this->connection);
$query->select('{entity.name}')->from('entity');
$query->addComponent('entity', 'User');
$coll = $query->execute();
$this->assertEqual($coll->count(), 8);
$this->assertTrue(is_numeric($coll[0]->id));
$this->assertTrue(is_numeric($coll[3]->id));
$this->assertTrue(is_numeric($coll[7]->id));
}
public function getReviewerSuggestionList($searchBy)
{
$value = '%' . $searchBy . '%';
$q = new Doctrine_RawSql();
$q->select('{e.*}')->from('hs_hr_emp_reportto jk')->leftjoin('hs_hr_employee e ON e.emp_number = jk.erep_sup_emp_number')->andwhere('e.emp_firstname' . ' LIKE ?', $value)->addComponent('e', 'Employee');
$query = $q->execute();
return $query;
}
public function executeRecentUpdates()
{
$q = new Doctrine_RawSql();
$q->select('{e.*}, {i.*}, {u.*}, {p.*}')->from('entity e LEFT JOIN image i ON (e.id = i.entity_id AND i.is_deleted = 0) LEFT JOIN sf_guard_user u ON (u.id = e.last_user_id) LEFT JOIN sf_guard_user_profile p ON (u.id = p.user_id)')->where('e.is_deleted IS NULL OR e.is_deleted = ?', false)->addComponent('e', 'Entity e')->addComponent('i', 'e.Image i')->addComponent('u', 'e.LastUser u')->addComponent('p', 'u.Profile p')->orderBy('e.updated_at DESC')->limit(@$this->limit ? $this->limit : 10);
if (@$this->group) {
if (count($userIds = $this->group->getUserIds())) {
$q->andWhereIn('e.last_user_id', $userIds);
if (count($entityIds = $this->group->getEntityIds())) {
$q->andWhereIn('e.id', $entityIds);
}
} else {
$q->andWhere('1=0');
}
}
if ($this->network) {
$q->leftJoin('ls_list_entity le ON (e.id = le.entity_id)')->addComponent('le', 'e.LsListEntity le')->andWhere('le.list_id = ?', $this->network['id']);
}
$this->entities = $q->execute();
}