/** * Получаем последние диалоги пользователя * * @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(); $out = @$recs[0]['out']; if ($out) { $request->request->set('format', 'json'); return [$out]; } else { throw new HttpException(404, "No {$entity2}(s) found for {$entity1} id: {$id}"); } } catch (\Doctrine\ORM\NoResultException $e) { throw new HttpException(404, "No record found for id: {$id}"); } })->bind('related')->value('format', 'json'); return $controllers; }
/** * 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; } }