PHP ResultSetMappingBuilder::addJoinedEntityResult方法代码示例

 /**
  * Получаем последние диалоги пользователя
  *
  * @param int $id
  * @param int $count
  *
  * @return array
  */
 public function findByDialog($id, $count)
 {
     $rsm = new ResultSetMappingBuilder($this->getEntityManager());
     $rsm->addEntityResult("UserMessagesBundle:Dialog", "d");
     $rsm->addFieldResult("d", "id", "id");
     $rsm->addFieldResult("d", "flags", "flags");
     $rsm->addJoinedEntityResult("UserUserBundle:Users", "du", "d", "usersTo");
     $rsm->addFieldResult("du", "users_to_id", "id");
     $rsm->addFieldResult("du", "realname", "realname");
     $rsm->addFieldResult("du", "path", "path");
     $rsm->addJoinedEntityResult("UserMessagesBundle:Messages", "m", "d", "messages");
     $rsm->addFieldResult("m", "message_id", "id");
     $rsm->addFieldResult("m", "text", "text");
     $rsm->addJoinedEntityResult("UserUserBundle:Users", "u", "m", "users");
     $rsm->addFieldResult("u", "users_id", "id");
     $rsm->addFieldResult("u", "m_realname", "realname");
     $rsm->addFieldResult("u", "m_path", "path");
     $query = $this->getEntityManager()->createNativeQuery('
             SELECT 
               DISTINCT ON (d.id) d.id, 
               d.flags,
               du.id as users_to_id, 
               du.realname, 
               du.path,
               m.id as message_id, 
               m.text,
               u.id as users_id, 
               u.realname as m_realname, 
               u.path as m_path
             FROM dialog d
               LEFT JOIN users du ON d.users_to_id = du.id
               LEFT JOIN messages m ON d.id = m.dialog_id
               LEFT JOIN users u ON m.users_id = u.id
             WHERE d.users_id = ?
             GROUP BY d.id, d.flags, du.id, m.text, u.id, m.id
             LIMIT 20 OFFSET ?
         ', $rsm)->setParameters([1 => $id, 2 => $count]);
     try {
         return $query->getArrayResult();
     } catch (\Doctrine\ORM\NoResultException $e) {
         return null;
     }
 }

 /**
  * Корзина пользователя
  *
  * @param int $id
  *
  * @return array
  */
 public function findByProductsUsersBasket($id)
 {
     $rsm = new ResultSetMappingBuilder($this->getEntityManager());
     $rsm->addEntityResult("ShopOrderBundle:OrderItem", "oi");
     $rsm->addFieldResult("oi", "id", "id");
     $rsm->addFieldResult("oi", "number", "number");
     $rsm->addJoinedEntityResult("ShopOrderBundle:Order", "o", "oi", "order");
     $rsm->addJoinedEntityResult("ShopProductBundle:Product", "p", "oi", "product");
     $rsm->addFieldResult("p", "product", "id");
     $rsm->addFieldResult("p", "price", "price");
     $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 (oi.id) oi.id,
               oi.product_id,
               oi.number,
               p.id as product,
               p.price,
               pi.id as image_id,
               pi.path
             FROM order_item oi
               LEFT JOIN "order" o ON oi.order_id = o.id
               LEFT JOIN product p ON oi.product_id = p.id
               LEFT JOIN product_image pi ON p.id = pi.product_id
             WHERE 
               o.users_id = ? 
               AND o.is_create_order = \'f\'
         ', $rsm)->setParameter(1, $id);
     try {
         return $query->getArrayResult();
     } catch (\Doctrine\ORM\NoResultException $e) {
         return null;
     }
 }

 public function connect(Application $app)
 {
     $controllers = $app['controllers_factory'];
     $controllers->get('/{id}/view', function (Application $app, $id) {
         $em = $app['orm.em'];
         try {
             $query = $em->createQuery("SELECT 1 from MetaCat\\Entity\\Project c where c.projectid = ?1");
             $query->setParameter(1, $id);
             $item = $query->getSingleScalarResult();
             $subRequest = Request::create($app['url_generator']->generate('projectview', ['id' => $id]), 'GET');
             return $app->handle($subRequest, HttpKernelInterface::SUB_REQUEST);
         } catch (\Doctrine\ORM\NoResultException $e) {
             try {
                 $query = $em->createQuery("SELECT 1 from MetaCat\\Entity\\Product c where c.productid = ?1");
                 $query->setParameter(1, $id);
                 $item = $query->getSingleScalarResult();
                 $subRequest = Request::create($app['url_generator']->generate('productview', ['id' => $id]), 'GET');
                 return $app->handle($subRequest, HttpKernelInterface::SUB_REQUEST);
             } catch (\Doctrine\ORM\NoResultException $e) {
                 throw new HttpException(404, "No record found for id: {$id}");
             }
         }
     })->bind('metadatabaseview');
     $controllers->get('/{entity}/{id}.{format}', function (Application $app, $entity, $id, $format) {
         $em = $app['orm.em'];
         $qb = $em->createQueryBuilder();
         $qb->select('c.' . $format)->from('MetaCat\\Entity\\' . ucfirst($entity), 'c')->where("c.{$entity}id = ?1")->setParameter(1, $id);
         $query = $qb->getQuery();
         try {
             $result = $query->getSingleScalarResult();
         } catch (\Doctrine\ORM\NoResultException $e) {
             throw new HttpException(404, "No record found for id: {$id}");
         }
         return [trim($result)];
     })->bind('metadata')->value('format', 'json');
     $controllers->get('/{id}.{format}', function (Application $app, Request $request, $id, $format) {
         $em = $app['orm.em'];
         if (isset($app['config']['white']['entity'][$id])) {
             $em = $app['orm.em'];
             $class = 'MetaCat\\Entity\\' . ucfirst($id);
             //check owner
             $owners = array_flip(array_column($app['mc.cache.owners']($class), 'owner'));
             $owner = $request->query->get('owner', '');
             if ($owner && !isset($owners[$owner])) {
                 throw new HttpException(404, "Owner does not exist: {$owner}");
             }
             $sql = "SELECT json_agg(p.json) as out FROM {$id} p";
             $where = " WHERE ((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 role\n                          WHERE role@>'{\"role\":\"owner\"}' LIMIT 1)->'contactId') ->>'organizationName') = ? ";
             //add filter
             if ($owner) {
                 $sql .= $where;
             }
             $rsm = new ResultSetMappingBuilder($em);
             $rsm->addRootEntityFromClassMetadata($class, 'p');
             $rsm->addScalarResult('out', 'out');
             $query = $em->createNativeQuery($sql, $rsm);
             if ($owner) {
                 $query->setParameter(1, $owner);
             }
             $item = $query->getSingleScalarResult();
             if (!$item) {
                 throw new HttpException(404, "No {$id} records found.");
             }
             return [$item];
         }
         try {
             $query = $em->createQuery("SELECT c.{$format} from MetaCat\\Entity\\Project c where c.projectid = ?1");
             $query->setParameter(1, $id);
             $item = $query->getSingleScalarResult();
         } catch (\Doctrine\ORM\NoResultException $e) {
             try {
                 $query = $em->createQuery("SELECT c.{$format} from MetaCat\\Entity\\Product c where c.productid = ?1");
                 $query->setParameter(1, $id);
                 $item = $query->getSingleScalarResult();
             } catch (\Doctrine\ORM\NoResultException $e) {
                 throw new HttpException(404, "No record found for id: {$id}");
             }
         }
         return [trim($item)];
     })->bind('metadatabase')->value('format', 'json');
     $controllers->get('{entity1}/{id}/{entity2}.{format}', function (Application $app, Request $request, $entity1, $id, $entity2) {
         $em = $app['orm.em'];
         $white = $app['config']['white']['entity'];
         if (isset($white[$entity1], $white[$entity2])) {
             $em = $app['orm.em'];
             $class = 'MetaCat\\Entity\\' . ucfirst($entity2);
             $class2 = 'MetaCat\\Entity\\' . ucfirst($entity1);
             $col = $request->get('short') ? 'p.json#>\'{metadata,resourceInfo,citation}\'' : 'p.json';
             $sql = "SELECT json_agg({$col}) as out FROM {$entity2} p JOIN {$entity1} p2 USING(projectid) WHERE p2.{$entity1}id = ?";
             $rsm = new ResultSetMappingBuilder($em);
             $rsm->addRootEntityFromClassMetadata($class, 'p');
             $rsm->addJoinedEntityResult($class2, 'p2', 'p', 'projectid');
             $rsm->addScalarResult('out', 'out');
             $query = $em->createNativeQuery($sql, $rsm);
             $query->setParameter(1, $id);
         } else {
             throw new HttpException(403, 'Entity name not allowed. Valid entities are: ' . join('|', array_keys($white)));
         }
         try {
             $recs = $query->getScalarResult();
//.........这里部分代码省略.........

 /**
  * 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;
     }
 }