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(); }