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;
 }
Ejemplo n.º 2
0
 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;
     }
 }
Ejemplo n.º 4
0
 /**
  * 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;
     }
 }
Ejemplo n.º 5
0
    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;
    }
Ejemplo n.º 6
0
 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();
     }
 }
Ejemplo n.º 8
0
 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;
 }
Ejemplo n.º 9
0
 /**
  * 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();
 }
Ejemplo n.º 10
0
 /**
  * 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();
 }
Ejemplo n.º 11
0
 /**
  * @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();
 }
Ejemplo n.º 12
0
 /**
  * @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);
 }
Ejemplo n.º 16
0
 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();
 }
Ejemplo n.º 17
0
    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;
    }
Ejemplo n.º 18
0
 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;
 }
Ejemplo n.º 19
0
 /**
  * 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;
 }
Ejemplo n.º 22
0
 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);
     }
 }
Ejemplo n.º 23
0
    /**
     * 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();
    }
Ejemplo n.º 24
0
 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();
 }
Ejemplo n.º 25
0
 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;
 }
Ejemplo n.º 27
0
 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;
 }