Exemple #1
0
 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;
     }
 }
Exemple #2
0
 /**
  * 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;
 }
Exemple #3
0
 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']);
 }
Exemple #5
0
 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);
 }
Exemple #8
0
 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);
 }
Exemple #12
0
 /**
  * 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();
}
Exemple #14
0
 /**
 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;
 }
Exemple #17
0
 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();
 }