/**
* Получаем последние диалоги пользователя
*
* @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;
}
}
