public function getTransactionsByMember(Corporation $corp, array $member_ids, Carbon $date) { $start = $date->copy(); $start->subMonth()->setTime(0, 0, 0); $end = $date->copy(); $end->setTime(23, 59, 59); $sql = 'SELECT jt.owner_id2, group_concat(DISTINCT jt.id) as ids FROM journal_transactions as jt LEFT JOIN accounts as acc on jt.account_id=acc.id WHERE acc.corporation_id = :corp_id AND jt.owner_id2 in ( :owner_ids ) AND jt.date >= :start_date AND jt.date <= :end_date GROUP BY jt.owner_id2'; $rsm = new ResultSetMappingBuilder($this->getEntityManager()); $rsm->addRootEntityFromClassMetadata('AppBundle\\Entity\\JournalTransaction', 'jt'); $rsm->addFieldResult('jt', 'owner_id2', 'owner_id2'); $rsm->addFieldResult('jt', 'ids', 'id'); $q = $this->getEntityManager()->createNativeQuery($sql, $rsm); $q->setParameter('corp_id', $corp->getId()); $q->setParameter('owner_ids', $member_ids, Connection::PARAM_INT_ARRAY); $q->setParameter('start_date', $start); $q->setParameter('end_date', $end); $results = $q->getResult(); $real_res = []; foreach ($results as $res) { $ids = explode(',', $res->getId()); $rt = $this->createQueryBuilder('jt')->select('sum(jt.amount) as total_amount')->where('jt.id in (:j_ids)')->setParameter('j_ids', $ids)->getQuery()->getResult(); $r = $this->createQueryBuilder('jt')->select('jt')->where('jt.id in (:j_ids)')->setParameter('j_ids', $ids)->getQuery()->getResult(); $real_res[] = ['user' => $res->getOwnerId2(), 'total' => $rt, 'orig_ids' => $r]; } return $real_res; }
public function testIssue() { $config = $this->_em->getConfiguration(); $config->addEntityNamespace('MyNamespace', 'Doctrine\\Tests\\ORM\\Functional\\Ticket'); $user = new DDC2256User(); $user->name = 'user'; $group = new DDC2256Group(); $group->name = 'group'; $user->group = $group; $this->_em->persist($user); $this->_em->persist($group); $this->_em->flush(); $this->_em->clear(); $sql = 'SELECT u.id, u.name, g.id as group_id, g.name as group_name FROM ddc2256_users u LEFT JOIN ddc2256_groups g ON u.group_id = g.id'; // Test ResultSetMapping. $rsm = new ResultSetMapping(); $rsm->addEntityResult('MyNamespace:DDC2256User', 'u'); $rsm->addFieldResult('u', 'id', 'id'); $rsm->addFieldResult('u', 'name', 'name'); $rsm->addJoinedEntityResult('MyNamespace:DDC2256Group', 'g', 'u', 'group'); $rsm->addFieldResult('g', 'group_id', 'id'); $rsm->addFieldResult('g', 'group_name', 'name'); $this->_em->createNativeQuery($sql, $rsm)->getResult(); // Test ResultSetMappingBuilder. $rsm = new ResultSetMappingBuilder($this->_em); $rsm->addRootEntityFromClassMetadata('MyNamespace:DDC2256User', 'u'); $rsm->addJoinedEntityFromClassMetadata('MyNamespace:DDC2256Group', 'g', 'u', 'group', array('id' => 'group_id', 'name' => 'group_name')); $this->_em->createNativeQuery($sql, $rsm)->getResult(); }
/** * Map N unused coupon codes to the given customer * * We need very strict isolation in order to avoid mapping * an already mapped code to another user in case of lots * of calls to mapNToCustomer happening in parallel. * * Therefore, we wrap everything into a transaction * and read with SELECT ... FOR UPDATE. * * This ensures a) that no codes are mapped at all if not all * mappings where successful, and b) locks the row of a free * code that is currently read. * * @see https://dev.mysql.com/doc/refman/5.5/en/innodb-locking-reads.html * * @param $count * @param \AppBundle\Entity\Customer $customer * @return bool */ public function mapNToCustomer($count, Customer $customer) { $this->em->getConnection()->setTransactionIsolation(Connection::TRANSACTION_SERIALIZABLE); $this->em->getConnection()->beginTransaction(); try { for ($i = 0; $i < $count; $i++) { $rsm = new ResultSetMappingBuilder($this->em); $rsm->addRootEntityFromClassMetadata('\\AppBundle\\Entity\\Couponcode', 'c'); $query = $this->em->createNativeQuery('SELECT * FROM couponcode WHERE customer_id IS NULL LIMIT 1 FOR UPDATE', $rsm); $couponcode = $query->getOneOrNullResult(); if (is_null($couponcode)) { $this->em->getConnection()->rollback(); return false; } $couponcode->setCustomer($customer); // if (strlen($couponcode->getCode()) === 64) { $normalizedEmployeeNumber = preg_replace("/[^0-9]+/", "", $customer->getEmployeeNumber()); $code = $couponcode->getCode(); $newCode = substr($code, 0, 45); $newCode .= str_pad(substr($normalizedEmployeeNumber, 0, 16), 16, '0', \STR_PAD_LEFT); $newCode .= '0' . $customer->getSalesdivision(); $newCode .= substr($code, -1, 1); $couponcode->setCode($newCode); } $this->em->persist($couponcode); $this->em->flush(); } $this->em->getConnection()->commit(); return true; } catch (Exception $e) { $this->em->getConnection()->rollback(); return false; } }
/** * return All Product Shop * * @param string $name * @param int $count * * @return array */ public function findByProductShop($name, $count) { $rsm = new ResultSetMappingBuilder($this->getEntityManager()); $rsm->addEntityResult("ShopProductBundle:Product", "p"); $rsm->addFieldResult("p", "id", "id"); $rsm->addFieldResult("p", "price", "price"); $rsm->addJoinedEntityResult("UserUserBundle:Users", "u", "p", "likeProduct"); $rsm->addFieldResult("u", "likes", "id"); $rsm->addJoinedEntityResult("ShopProductBundle:ProductImage", "pi", "p", "image"); $rsm->addFieldResult("pi", "image_id", "id"); $rsm->addFieldResult("pi", "path", "path"); $query = $this->getEntityManager()->createNativeQuery(' SELECT DISTINCT ON (p.id) p.id, p.price, count(u.id) as likes, pi.id as image_id, pi.path FROM product p LEFT JOIN product_image pi ON pi.product_id = p.id LEFT JOIN product_like pl ON pl.product_id = p.id LEFT JOIN users u ON pl.users_id = u.id LEFT JOIN shops s ON s.id = p.shops_id WHERE s.unique_name = ? GROUP BY p.id, p.price, pi.id, pi.path LIMIT 16 OFFSET ? ', $rsm)->setParameters([1 => $name, 2 => $count]); try { return $query->getArrayResult(); } catch (\Doctrine\ORM\NoResultException $e) { return null; } }
public function findByLog($field, $value) { $search = 's:' . strlen($field) . ':"' . $field . '";s:' . strlen($value) . ':"' . $value . '";'; $search = '"' . $field . '":' . str_replace('\\', '\\\\', json_encode($value)); $sql = 'SELECT * FROM content_log cl WHERE cl.data LIKE \'%' . $search . '%\' ESCAPE \'|\' GROUP BY cl.object_id ORDER BY cl.id DESC '; $rsm = new ResultSetMappingBuilder($this->getEntityManager()); $rsm->addRootEntityFromClassMetadata(str_replace('\\Entity\\Content', '\\Entity\\Log\\Content', $this->getClassName()) . 'Log', 'cl'); $q = $this->getEntityManager()->createNativeQuery($sql, $rsm); $logValues = $q->getResult(); $ret = array(); foreach ($logValues as $logValue) { $object = $this->find($logValue->getObjectId()); if ($object) { if ($logValue->getLocale()) { $object->setTranslatableLocale($logValue->getLocale()); $this->getEntityManager()->refresh($object); } $ret[] = $object; } } return $ret; }
public function searchAll() { $rsm = new ResultSetMappingBuilder($this->getEntityManager()); $rsm->addRootEntityFromClassMetadata('AMiE\\EntreprisesBundle\\Entity\\Partenaire', 'p'); $query = 'SELECT p.* FROM Partenaire p WHERE 1 '; $request = $this->getEntityManager()->createNativeQuery($query, $rsm); return $request; }
/** * @inheritdoc */ public function index($entities = null) { $em = $this->modelManager->getManager(); $repo = $this->modelManager->get($this->getModel()); $indexRepo = $this->modelManager->get($this->getIndexModel()); // first, we need to check this is actually fulltext table $rsm = new Query\ResultSetMappingBuilder($em); $rsm->addScalarResult('Index_type', 'type'); $rsm->addScalarResult('Key_name', 'key'); $meta = $em->getClassMetadata($indexRepo->getClassName()); $indexName = 'search_text_index'; $indexCheck = $em->createNativeQuery("SHOW INDEX FROM {$meta->getTableName()} WHERE KEY_NAME = '{$indexName}' AND Index_type='FULLTEXT'", $rsm); $q = $indexCheck->execute(); if (!count($q)) { throw new FullTextIndexMissingException($meta->getTableName(), $indexName, array('text')); } $className = $repo->getClassName(); if ($entities instanceof $className) { $entities = array($entities); } elseif ($entities === null) { $entities = $repo->findAll(); } else { throw new EntityNotSupportedException($entities); } foreach ($entities as $entity) { $deleteQb = $indexRepo->createQueryBuilder('t'); $deleteQb->delete()->where("t.entity = :entity"); $deleteQb->setParameter(':entity', $entity); $deleteQb->getQuery()->execute(); /** @var FullText $indexEnitiy */ foreach ($this->entityMappings as $field => $properties) { if (is_array($properties)) { foreach ($properties as $subField) { $subEntities = $entity->{"get{$field}"}(); if ($subEntities instanceof Collection) { foreach ($subEntities as $subEntity) { $indexEnitiy = $this->modelManager->create($this->getIndexModel()); $indexEnitiy->setEntity($entity); $indexEnitiy->setText($subEntity->{"get{$subField}"}()); $em->persist($indexEnitiy); } } else { $indexEnitiy = $this->modelManager->create($this->getIndexModel()); $indexEnitiy->setEntity($entity); $indexEnitiy->setText($subEntities->{"get{$subField}"}()); $em->persist($indexEnitiy); } } } else { $indexEnitiy = $this->modelManager->create($this->getIndexModel()); $indexEnitiy->setEntity($entity); $indexEnitiy->setText($entity->{"get{$field}"}()); $em->persist($indexEnitiy); } } $em->flush(); } }
public function searchSecteur($secteur) { $rsm = new ResultSetMappingBuilder($this->getEntityManager()); $rsm->addRootEntityFromClassMetadata('AMiE\\UserBundle\\Entity\\User', 'u'); $query = 'SELECT count(u.secteur) FROM AMiE\\UserBundle\\Entity\\User u WHERE u.secteur = :secteur AND u.typeUt = \'Entreprise\''; $request = $this->getEntityManager()->createQuery($query); $request->setParameters(array('secteur' => $secteur)); return $request; }
/** * Get a random proxy (only enabled ones) * * @return ProxyEntity */ public function findRandom() { $rsmb = new ResultSetMappingBuilder($this->_em); $rsmb->addRootEntityFromClassMetadata('steevanb\\ProxyBundle\\Entity\\Proxy', 'p'); $rsmb->addIndexBy('p', 'id'); $query = $this->_em->createNativeQuery('SELECT * FROM proxy_proxies p WHERE state = :state ORDER BY RAND() LIMIT 1', $rsmb); $query->setParameter('state', ProxyEntity::STATE_ENABLED); return $query->getSingleResult(); }
/** * Wyszukuje wydarzenia w zadanej odległości od podanych współrzędnych * * @param float $lat * @param float $lng * @param int $distance * @return ArrayObject $events */ public function findEventsInRadius($lat, $lng, $distance = 2) { $query = "\n SELECT\n subSel2.*\n FROM (\n SELECT\n sin(subSel.dlat / 2) * \n sin(subSel.dlat / 2) + \n cos(subSel.lat1) * \n cos(subSel.lat2) * \n sin(subSel.dlng / 2) * \n sin(subSel.dlng / 2) sel,\n subSel.*\n FROM (\n SELECT \n (radians(:lat)-radians(lat)) dlat, \n (radians(:lng)-radians(lng)) dlng, \n radians(lat) lat1, \n radians(lng) lng1,\n radians(:lat) lat2,\n radians(:lng) lng2,\n Event.*\n From \n Event \n ) subSel \n ) subSel2\n WHERE\n (6372.797 * \n (2 * atan2(sqrt(subSel2.sel), sqrt(1 - subSel2.sel)))) <= :distance\n "; $rsm = new ResultSetMappingBuilder($this->entityManger); $rsm->addRootEntityFromClassMetadata('Events\\Entity\\Event', 'event'); $nativeQuery = $this->entityManger->createNativeQuery($query, $rsm); $nativeQuery->setParameters(array('lat' => $lat, 'lng' => $lng, 'distance' => $distance)); return $nativeQuery->getResult(); }
/** * @param Project $project * * @return Doctrine\Common\Collections\Collection */ public function findLastByRefs(Project $project) { $query = 'SELECT b.* FROM (SELECT * FROM build WHERE build.project_id = ? ORDER BY created_at DESC) b GROUP BY b.ref'; $rsm = new ResultSetMappingBuilder($this->getEntityManager()); $rsm->addRootEntityFromClassMetadata('Model:Build', 'b'); $query = $this->getEntityManager()->createNativeQuery($query, $rsm); $query->setParameter(1, $project->getId()); return $query->execute(); }
/** * @param Player $player * * @return PointLog[] */ public function getForPlayer(Player $player) { $sql = "\n SELECT\n *\n FROM\n pointlog pl\n LEFT JOIN\n match m ON m.id = pl.match_id\n WHERE\n m.player1_id = :id\n OR\n m.player2_id = :id\n OR\n m.team1attack = :id\n OR\n m.team1defence = :id\n OR\n m.team2attack = :id\n OR\n m.team2defence = :id\n ORDER BY\n m.date DESC"; $rsm = new ResultSetMappingBuilder($this->_em); $rsm->addRootEntityFromClassMetadata('Application\\Model\\Entity\\PointLog', 'pl'); $query = $this->_em->createNativeQuery($sql, $rsm); $query->setParameter('id', $player->getId()); $logs = $query->getResult(); return $logs; }
/** * usage: http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/native-sql.html */ public function getQuizList($status, $amount) { $rsm = new ResultSetMappingBuilder($this->_em); $rsm->addRootEntityFromClassMetadata('Eng:PhrasesEntity', 'w'); // Limit parameter can not be parameterized $query = $this->_em->createNativeQuery("SELECT *, (success - failure) seq FROM phrases w WHERE w.status = :status ORDER BY seq ASC, failure DESC, create_time ASC LIMIT " . intval($amount), $rsm); $query->setParameter('status', $status, \PDO::PARAM_INT); $words = $query->getArrayResult(); return $words; }
/** * @param Event $event * * @return Unregistration[] */ public function getUnprocessedUnregistrations(Event $event) { $rsm = new ResultSetMappingBuilder($this->_em); $rsm->addRootEntityFromClassMetadata('BCRM\\BackendBundle\\Entity\\Event\\Unregistration', 'u'); $eventId = $event->getId(); $unregistrations = "SELECT MAX(id) FROM unregistration WHERE confirmed = 1 AND event_id = {$eventId} AND processed = 0 GROUP BY event_id, email ORDER BY created ASC, id ASC"; $sql = "SELECT * FROM unregistration WHERE id IN ({$unregistrations})"; $query = $this->_em->createNativeQuery($sql, $rsm); return $query->getResult(); }
public function buildAliasedSelectClause(ResultSetMappingBuilder $rsm) { $clauses = []; foreach ($rsm->getAliasMap() as $tableAlias => $class) { foreach ($this->getColumnAliases($class, $tableAlias) as $columnName => $columnAlias) { $clauses[] = $this->quoter->quoteIdentifier($tableAlias) . '.' . $this->quoter->quoteIdentifier($columnName) . ' as ' . $this->quoter->quoteIdentifier($columnAlias); } } return join(', ', $clauses); }
public function findPaths($limit = 0) { $query = 'SELECT e.* FROM ( ' . 'SELECT * FROM `mrk_gitpushwatcherbundle_push` ORDER BY datetime DESC ' . ') AS e GROUP BY e.path ORDER BY e.datetime DESC '; if ($limit > 0) { $query .= 'LIMIT ' . $limit; } $em = $this->getEntityManager(); $rsm = new ResultSetMappingBuilder($em); $rsm->addRootEntityFromClassMetadata('Mrk\\GitPushWatcherBundle\\Entity\\Push', 'e'); return $em->createNativeQuery($query, $rsm)->getResult(); }
public function findRelatedItems(Taggable $item, $class, $locale, $nbOfItems = 1) { $instance = new $class(); if (!$instance instanceof Taggable) { return NULL; } $em = $this->em; $rsm = new ResultSetMappingBuilder($em); $rsm->addRootEntityFromClassMetadata($class, 'i'); $meta = $em->getClassMetadata($class); $tableName = $meta->getTableName(); $escapedClass = str_replace('\\', '\\\\', $class); $query = <<<EOD SELECT i.*, COUNT(i.id) as number FROM {$tableName} i LEFT JOIN kuma_taggings t ON t.resource_id = i.id AND t.resource_type = '{$instance->getTaggableType()}' WHERE t.tag_id IN ( SELECT tg.tag_id FROM kuma_taggings tg WHERE tg.resource_id = {$item->getId()} AND tg.resource_type = '{$item->getTaggableType()}' ) AND i.id <> {$item->getId()} EOD; if ($item instanceof AbstractPage) { $query .= <<<EOD AND i.id IN ( SELECT nodeversion.refId FROM kuma_nodes as node INNER JOIN kuma_node_translations as nodetranslation ON node.id = nodetranslation.node AND nodetranslation.lang = '{$locale}' INNER JOIN kuma_node_versions as nodeversion ON nodetranslation.publicNodeVersion = nodeversion.id AND nodeversion.refEntityname = '{$escapedClass}' AND node.deleted = 0 AND nodetranslation.online = 1 ) EOD; } $query .= <<<EOD GROUP BY i.id HAVING number > 0 ORDER BY number DESC LIMIT {$nbOfItems}; EOD; $items = $em->createNativeQuery($query, $rsm)->getResult(); return $items; }
public function searchTypeContrat($type) { $rsm = new ResultSetMappingBuilder($this->getEntityManager()); $rsm->addRootEntityFromClassMetadata('AMiE\\MiagistesBundle\\Entity\\Formulaire', 'f'); // $query = 'SELECT count(*) FROM Formulaire f WHERE typeContrat = :type'; // $request = $this->getEntityManager()->createNativeQuery($query, $rsm); $query = 'SELECT count(f.typeContrat) FROM AMiE\\MiagistesBundle\\Entity\\Formulaire f WHERE f.typeContrat = :type'; $request = $this->getEntityManager()->createQuery($query); $request->setParameters(array('type' => $type)); return $request; }
/** * Find all members with a birthday in the next $days days. * * When $days equals 0 or isn't given, it will give all birthdays of today. * * @param int $days The number of days to look ahead. * * @return array Of members sorted by birthday */ public function findBirthdayMembers($days) { // unfortunately, there is no support for functions like DAY() and MONTH() // in doctrine2, thus we have to use the NativeSQL here $builder = new ResultSetMappingBuilder($this->em); $builder->addRootEntityFromClassMetadata('Decision\\Model\\Member', 'm'); $select = $builder->generateSelectClause(array('m' => 't1')); $sql = "SELECT {$select} FROM Member AS t1" . " WHERE DATEDIFF(DATE_SUB(t1.birth, INTERVAL YEAR(t1.birth) YEAR)," . " DATE_SUB(CURDATE(), INTERVAL YEAR(CURDATE()) YEAR)) BETWEEN 0 AND :days" . " AND t1.expiration >= CURDATE()" . "ORDER BY DATE_SUB(t1.birth, INTERVAL YEAR(t1.birth) YEAR) ASC"; $query = $this->em->createNativeQuery($sql, $builder); $query->setParameter('days', $days); return $query->getResult(); }
/** * {@inheritDocs} */ public function getNextVipRegistrations(Event $event, $day) { $rsm = new ResultSetMappingBuilder($this->_em); $rsm->addRootEntityFromClassMetadata('BCRM\\BackendBundle\\Entity\\Event\\Registration', 'r'); $types = join(',', array(Registration::TYPE_SPONSOR, Registration::TYPE_VIP)); $eventId = $event->getId(); $dayName = $day == Ticket::DAY_SATURDAY ? 'saturday' : 'sunday'; $registrations = "SELECT MAX(id) FROM registration WHERE confirmed = 1 AND type IN ({$types}) AND event_id = {$eventId} GROUP BY event_id, email ORDER BY created ASC, id ASC"; $sql = "SELECT * FROM registration WHERE id IN ({$registrations}) AND email NOT IN (SELECT email FROM ticket WHERE day = {$day} AND event_id = {$eventId}) AND {$dayName} = 1"; $query = $this->_em->createNativeQuery($sql, $rsm); return $query->getResult(); }
public function findByGuideAndSpotDetail(\Buggl\MainBundle\Entity\EGuide $eguide, \Buggl\MainBundle\Entity\SpotDetail $spotDetail) { $sql = "SELECT itsd.* \n\t\t\t\t\tFROM itinerary_to_spot_detail itsd\n\t\t\t\t\tLEFT JOIN itinerary it\n\t\t\t\t\t\tON itsd.itinerary_id = it.id\n\t\t\t\t\tLEFT JOIN e_guide eg\n\t\t\t\t\t\tON it.e_guide_id = eg.id\n\t\t\t\t\tWHERE 1\n\t\t\t\t\t\tAND it.e_guide_id = ?\n\t\t\t\t\t\tAND itsd.spot_detail_id = ?\n\t\t\t\t\t"; $params = array($eguide->getId(), $spotDetail->getID()); $em = $this->getEntityManager(); $rsm = new ResultSetMappingBuilder($em); $rsm->addRootEntityFromClassMetadata('BugglMainBundle:ItineraryToSpotDetail', 'itsd'); $query = $this->_em->createNativeQuery($sql, $rsm); $query->setParameters($params); $result = $query->getResult(); return $result; }
public function testIssueWithoutExtraColumn() { $sql = "SELECT o.product_id, o.customer_id FROM ddc_2660_customer_order o"; $rsm = new ResultSetMappingBuilder($this->_getEntityManager()); $rsm->addRootEntityFromClassMetadata(__NAMESPACE__ . '\\DDC2660CustomerOrder', 'c'); $query = $this->_em->createNativeQuery($sql, $rsm); $result = $query->getResult(); $this->assertCount(5, $result); foreach ($result as $order) { $this->assertNotNull($order); $this->assertInstanceOf(__NAMESPACE__ . '\\DDC2660CustomerOrder', $order); } }
/** * Find the locations within the given radius from the specified origin coordinates. Calculations are done at sea * level using a mean Earth radius and does not cater for altitude or "oblate spheroid" related anomalies. * * <strong>This method uses native SQL</strong> however it is very restricted to highly compatible functions: * `SIN()`, `COS()`, `ACOS()`, and `PI()`, which should be supported on most (all?) platforms. * * @param array $origin An array consisting of 'latitude' and 'longitude' keys. Other keys are ignored. * @param int $radius Number of metres around the origin to include. * * @return array */ public function findInRadius(array $origin, $radius) { $resultSetMappingBuilder = new ResultSetMappingBuilder($this->getEntityManager()); $resultSetMappingBuilder->addRootEntityFromClassMetadata('Locations:Item', 'li'); return $this->getEntityManager()->createNativeQuery(sprintf('select li.* from %s li where ( ACOS( SIN(%s * PI() / 180) * SIN(li.latitude * PI() / 180) + COS(%s * PI() / 180) * COS(li.latitude * PI() / 180) * COS((%s - li.longitude) * PI() / 180) ) * 180 / PI() * 60 * %s ) <= %s', $this->getClassMetadata()->getTableName(), $origin['latitude'], $origin['latitude'], $origin['longitude'], self::RATIO_KM, $radius), $resultSetMappingBuilder)->execute(); }
public function findTop20Videos($scholarship, $seed = null) { if (!$scholarship) { return []; } $em = $this->getEntityManager(); $scholarshipid = $scholarship->getId(); $rsm = new ResultSetMappingBuilder($em); $rsm->addRootEntityFromClassMetadata('GotChosenSiteBundle:Video', 'v'); $q = $em->createNativeQuery('SELECT v.* FROM Video v ' . 'LEFT JOIN Scholarships s ON (v.scholarship_id = s.idScholarships) ' . 'LEFT JOIN User u ON v.user_id = u.id ' . 'WHERE s.idScholarships = :sship AND v.status_id = 1 AND u.status = :userStatus ' . 'ORDER BY (SELECT COUNT(vote.id) FROM VideoVote vote WHERE vote.video_id = v.id) DESC, RAND(:seed) LIMIT 20', $rsm); $q->setParameter('sship', $scholarshipid); $q->setParameter('seed', $seed === null ? microtime(true) : $seed); $q->setParameter('userStatus', 'active'); return $q->getResult(); }
public function register(Container $app) { $app['mc.cache.owners'] = $app->protect(function ($class) use($app) { $em = $app['orm.em']; $isClass = strrpos($class, '\\'); $class = strtolower($isClass ? substr($class, $isClass + 1) : $class); $sql = "SELECT (SELECT value FROM jsonb_array_elements(json#>'{contact}') AS c WHERE\n c->'contactId' = (SELECT value FROM\n jsonb_array_elements(json#>'{metadata,resourceInfo,citation,responsibleParty}') AS rol\n WHERE rol@>'{\"role\":\"owner\"}' LIMIT 1)->'contactId')->>'organizationName' as \"owner\"\n FROM {$class} p\n GROUP BY owner"; $rsm = new ResultSetMappingBuilder($em); $rsm->addScalarResult('owner', 'owner'); $query = $em->createNativeQuery($sql, $rsm); $query->useResultCache(true, null, "mc_{$class}_owner"); $result = $query->getArrayResult(); return $result; }); }
/** * @param $lookupStr * @return array|mixed */ protected function getAddressHandlingDTOs($lookupStr) { $searchString = $this->constructFulltextSearchString($lookupStr); $rsm = new ResultSetMappingBuilder($this->getEntityManager()); $rsm->addRootEntityFromClassMetadata('Tixi\\CoreDomain\\Address', 'a'); $sql = "SELECT a.id, a.street, a.postalCode, a.city, a.country, a.lat, a.lng, a.source FROM address a\n WHERE MATCH (name, street, postalCode, city, country, source)\n AGAINST ('.{$searchString}.' IN BOOLEAN MODE) AND a.isDeleted = 0\n LIMIT 0, " . $this->getLookupLimit(); $query = $this->getEntityManager()->createNativeQuery($sql, $rsm); $results = $query->getResult(); $addresses = array(); /** @var $result Address */ foreach ($results as $result) { $addresses[] = AddressHandleAssembler::toAddressHandleDTO($result); } return $addresses; }
public function getAdminsOfBases(array $basList) { $rsm = new ResultSetMappingBuilder($this->em); $rsm->addRootEntityFromClassMetadata('Alchemy\\Phrasea\\Model\\Entities\\User', 'u'); $rsm->addScalarResult('base_id', 'base_id'); $selectClause = $rsm->generateSelectClause(); $query = $this->em->createNativeQuery(' SELECT b.base_id, ' . $selectClause . ' FROM Users u, basusr b WHERE u.id = b.usr_id AND b.base_id IN (' . implode(', ', $basList) . ') AND u.model_of IS NULL AND b.actif="1" AND b.canadmin="1" AND u.deleted="0"', $rsm); return $query->getResult(); }
public function getLastOrderByGuide(\Buggl\MainBundle\Entity\EGuide $eGuide) { $sql = "SELECT sd.* \n\t\t\t\t\tFROM e_guide_to_spot_detail sd\n\t\t\t\t\tWHERE 1\n\t\t\t\t\t\tAND sd.e_guide_id = ?\n\t\t\t\t\t\tORDER BY sd.order DESC\n\t\t\t\t\t\tLIMIT 0, 1"; $params = array($eGuide->getID()); $em = $this->getEntityManager(); $rsm = new ResultSetMappingBuilder($em); $rsm->addRootEntityFromClassMetadata('BugglMainBundle:EGuideToSpotDetail', 'sd'); $query = $this->_em->createNativeQuery($sql, $rsm); $query->setParameters($params); try { $result = $query->getResult(); } catch (\Doctrine\ORM\NoResultException $e) { return null; } return $result; }
public function __construct(EntityManager $em, $defaultRenameMode = Doctrine\ORM\Query\ResultSetMappingBuilder::COLUMN_RENAMING_INCREMENT) { parent::__construct($em, $defaultRenameMode); $this->em = $em; $this->platform = $this->em->getConnection()->getDatabasePlatform(); $this->defaultRenameMode = $defaultRenameMode; }
/** * @param array $criteria * @param array $orderBy * @param null $limit * @param null $offset * @param bool $innerBubble * * @return array */ public function findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null, $innerBubble = false) { $rsm = new ResultSetMappingBuilder($this->getEntityManager()); $rsm->addRootEntityFromClassMetadata($this->getEntityName(), 'e'); $qb = $this->getEntityManager()->createQueryBuilder(); $platform = $this->getEntityManager()->getConnection()->getDatabasePlatform(); $result = $params = []; foreach ($criteria as $column => &$value) { if (!is_object($value)) { $params[$column] = $value; } if (!$innerBubble && $value instanceof Func && strpos($value->getName(), ' IN') !== false) { $lengths = 0; foreach ($value->getArguments() as $in) { $lengths += strlen($in); $ids[$in] = $in; if ($lengths > 65000) { $result = array_merge($result, $this->findBy(array_merge($criteria, [$column => $qb->expr()->in($column, $ids)]), $orderBy, $limit, $offset, true)); $lengths = 0; $ids = []; } } $result = array_merge($result, $this->findBy(array_merge($criteria, [$column => $qb->expr()->in($column, $ids)]), $orderBy, $limit, $offset, true)); } elseif ($this->getClassMetadata()->hasField($column)) { if (!$innerBubble && !is_subclass_of($value, 'Doctrine\\ORM\\Query\\Expr\\Base') && !$value instanceof Comparison && !$value instanceof Func) { $mapping = $this->getClassMetadata()->getFieldMapping($column); $value = Type::getType($mapping['type'])->convertToDatabaseValue($value, $platform); $exp[] = $qb->expr()->eq($mapping['columnName'], ":" . $mapping['fieldName']); } else { $exp[] = $value; } } } if (!empty($exp)) { // it may be empty if recurrence is taking place $cql = $qb->where(call_user_func_array(function () { return new Andx(func_get_args()); }, $exp))->select($this->getSelectColumns($criteria) . ' from ' . $this->getClassMetadata()->getTableName())->setMaxResults($limit); $query = $this->getEntityManager()->createNativeQuery($cql . ' allow filtering', $rsm)->setParameters($params); $hydrator = !is_null(@$this->_hydrator) ? $this->_hydrator : $this->_defaultHydrator; $result = array_merge($result, $limit == 1 ? $query->getSingleResult() : $query->getResult($hydrator)); } return $result; }