/** * @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 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; }); }
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(); }
/** * @Route("/periodo-medio-pago", name="periodo_medio_pago") */ public function periodoMedioPagoAction(Request $request) { $em = $this->getDoctrine()->getManager(); $desde = $request->get('desde'); $hasta = $request->get('hasta'); if (!$desde) { $desde = \DateTime::createFromFormat('d/m/Y', '01/01/1970'); } else { $desde = \DateTime::createFromFormat('d/m/Y', $desde); } if (!$hasta) { $hasta = \DateTime::createFromFormat('d/m/Y', '31/12/2050'); } else { $hasta = \DateTime::createFromFormat('d/m/Y', $hasta); } $year = $desde->format('Y'); $trimestre = intval($desde->format('m')); if ($trimestre >= 1 && $trimestre <= 3) { $trimestre = "1er"; } elseif ($trimestre >= 4 && $trimestre <= 6) { $trimestre = "2o"; } elseif ($trimestre >= 7 && $trimestre <= 9) { $trimestre = "3er"; } if ($trimestre >= 10 && $trimestre <= 12) { $trimestre = "4o"; } $rsm = new ResultSetMappingBuilder($em); $rsm->addScalarResult('pagos', 'pagos'); $rsm->addScalarResult('total', 'total'); $rsm->addScalarResult('media', 'media'); $trs = array("aprovisionamiento", "adquisicion", "desagregar"); $resultados = array(); // FACTURAS PAGADAS EN EL TRIMESTRE $query = $em->createNativeQuery("SELECT COUNT(*) as pagos,SUM(total_pagar) as total,(SUM(total_pagar*(DATEDIFF(f_pago,f_registro)-30)) ) as media" . " FROM rec,estado_factura WHERE tipo_registro=0 AND estado_factura_id = estado_factura.id AND estado_factura.anulada = 0 " . " AND f_registro < :hasta AND f_pago BETWEEN :desde AND :hasta", $rsm); // Dentro del periodo legal de pago $query->setParameter('desde', $desde->format('Y-m-d')); $query->setParameter('hasta', $hasta->format('Y-m-d')); $result = $query->getResult(); $resultados['pagadas'] = $result[0]; // FACTURAS NO PAGADAS EN EL TRIMESTRE $query = $em->createNativeQuery("SELECT COUNT(*) as pagos,SUM(total_pagar) as total,(SUM(total_pagar*(DATEDIFF(:hasta,f_registro)-30)) ) as media" . " FROM rec,estado_factura WHERE tipo_registro=0 AND estado_factura_id = estado_factura.id AND estado_factura.anulada = 0" . " AND f_registro <= :hasta AND (f_pago IS NULL OR f_pago > :hasta)", $rsm); // Dentro del periodo legal de pago $query->setParameter('desde', $desde->format('Y-m-d')); $query->setParameter('hasta', $hasta->format('Y-m-d')); $result = $query->getResult(); $resultados['nopagadas'] = $result[0]; //print_r($resultados); //return $this->render('::base.html.twig'); $html = $this->renderView('reports/periodoMedioPago.html.twig', array('resultados' => $resultados, 'trimestre' => $trimestre, 'year' => $year)); return new Response($this->get('knp_snappy.pdf')->getOutputFromHtml($html, array('orientation' => 'Landscape')), 200, array('Content-Type' => 'application/pdf', 'Content-Disposition' => 'attachment; filename="periodoMedioPago.pdf"')); }
public function search($page = 1, $limit = 0) { if ($limit === null || $limit === 0) { $limit = $this->configManager->get('newpost.max', "forum"); } $configUsermanager = $this->configManager->getSymbbConfig('usermanager'); $configGroupManager = $this->configManager->getSymbbConfig('groupmanager'); $userlcass = $configUsermanager['user_class']; $groupclass = $configGroupManager['group_class']; $sql = "SELECT\n p\n FROM\n SymbbCoreForumBundle:Post p\n INNER JOIN\n SymbbCoreForumBundle:Topic t WITH\n t.id = p.topic\n LEFT JOIN\n SymbbCoreSystemBundle:Flag f WITH\n f.objectClass = 'Symbb\\Core\\ForumBundle\\Entity\\Post' AND\n f.objectId = p.id AND\n f.user = :user AND\n f.flag = '" . AbstractFlagHandler::FLAG_NEW . "'\n WHERE\n p.author != :user AND\n (\n ( SELECT COUNT(a.id) FROM SymbbCoreSystemBundle:Access a WHERE\n a.objectId = t.forum AND\n a.object = 'Symbb\\Core\\ForumBundle\\Entity\\Forum' AND\n a.identity = :userclass AND\n a.identityId = :user AND\n a.access = 'VIEW'\n ) > 0 OR\n ( SELECT COUNT(a2.id) FROM SymbbCoreSystemBundle:Access a2 WHERE\n a2.objectId = t.forum AND\n a2.object = 'Symbb\\Core\\ForumBundle\\Entity\\Forum' AND\n a2.identity = :groupclass AND\n a2.identityId IN (:groups) AND\n a2.access = 'VIEW'\n ) > 0\n )\n GROUP BY\n p.id\n ORDER BY\n f.id DESC,\n p.created DESC "; $groupIds = array(); foreach ($this->getUser()->getGroups() as $group) { $groupIds[] = $group->getId(); } //// count $query = $this->em->createQuery($sql); $rsm = new ResultSetMappingBuilder($this->em); $rsm->addScalarResult('count', 'count'); $queryCount = $query->getSQL(); $queryCount = "SELECT COUNT(*) count FROM (" . $queryCount . ") as temp"; $queryCount = $this->em->createNativeQuery($queryCount, $rsm); $queryCount->setParameter(0, $this->getUser()->getId()); $queryCount->setParameter(1, $this->getUser()->getId()); $queryCount->setParameter(2, $userlcass); $queryCount->setParameter(3, $this->getUser()->getId()); $queryCount->setParameter(4, $groupclass); $queryCount->setParameter(5, $groupIds); $count = $queryCount->getSingleScalarResult(); //// if (!$count) { $count = 0; } $query->setParameter('user', $this->getUser()->getId()); $query->setParameter('userclass', $userlcass); $query->setParameter('groupclass', $groupclass); $query->setParameter('groups', $groupIds); $query->setHint('knp_paginator.count', $count); $pagination = $this->paginator->paginate($query, $page, $limit, array('distinct' => false)); return $pagination; }
public function createPagination($query, $page, $limit) { $rsm = new ResultSetMappingBuilder($this->em); $rsm->addScalarResult('count', 'count'); $queryCount = $query->getSql(); $queryCount = "SELECT COUNT(*) as count FROM (" . $queryCount . ") as temp"; $queryCount = $this->em->createNativeQuery($queryCount, $rsm); $queryCount->setParameters($query->getParameters()); $count = $queryCount->getSingleScalarResult(); if (!$count) { $count = 0; } if ($limit === null) { $limit = 20; } if ($page === 'last') { $page = $count / (int) $limit; $page = ceil($page); } if ($page <= 0) { $page = 1; } $query->setHint('knp_paginator.count', $count); $pagination = $this->paginator->paginate($query, (int) $page, $limit, array('distinct' => false)); return $pagination; }
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; }
protected function _count($alias = null) { $sql = $this->countSql($alias); $rsm = new ResultSetMappingBuilder($this->em()); $rsm->addScalarResult('COUNT(*)', 'COUNT(*)', Type::INTEGER); $query = $this->em()->createNativeQuery($sql, $rsm); $this->setParams($query); return (int) $query->getSingleScalarResult(); }
/** * Search for a series * * @param string $q The search query * @param integer $page The page of results to retrieve * @param integer $perPage How many results to retrieve * @return SearchResult The search results * @see ChaosTangent\FansubEbooks\Bundle\AppBundle\DataFixtures\ORM\CreateSearchIndex */ public function search($q, $page = 1, $perPage = 30) { // count total results $countSql = 'SELECT COUNT(s.id) FROM series s WHERE to_tsvector(:config, s.title) @@ to_tsquery(:query)'; $total = $this->_em->getConnection()->fetchColumn($countSql, [':config' => 'english', ':query' => $q], 0); $fileSql = 'SELECT fs.id, COUNT(f.id) AS file_count FROM series fs JOIN files f ON f.series_id = fs.id GROUP BY fs.id'; $lineSql = 'SELECT ls.id, COUNT(l.id) AS line_count FROM series ls JOIN files lf ON lf.series_id = ls.id JOIN lines l ON l.file_id = lf.id GROUP BY ls.id'; $tweetSql = 'SELECT ts.id, COUNT(t.id) AS tweet_count FROM series ts JOIN files tf ON tf.series_id = ts.id JOIN lines tl ON tl.file_id = tf.id JOIN tweets t ON t.line_id = tl.id GROUP BY ts.id'; // fetch page of results $rsm = new ResultSetMappingBuilder($this->_em); $rsm->addRootEntityFromClassMetadata('ChaosTangent\\FansubEbooks\\Entity\\Series', 's'); $rsm->addScalarResult('file_count', 'file_count', 'integer'); $rsm->addScalarResult('line_count', 'line_count', 'integer'); $rsm->addScalarResult('tweet_count', 'tweet_count', 'integer'); $sql = 'WITH file_counts AS (' . $fileSql . '), line_counts AS (' . $lineSql . '), tweet_counts AS (' . $tweetSql . ') SELECT ' . $rsm->generateSelectClause() . ', fc.file_count, lc.line_count, tc.tweet_count FROM series s LEFT JOIN file_counts fc ON fc.id = s.id LEFT JOIN line_counts lc ON lc.id = s.id LEFT JOIN tweet_counts tc ON tc.id = s.id WHERE to_tsvector(:config, s.title) @@ to_tsquery(:query) ORDER BY ts_rank(to_tsvector(:config, s.title), to_tsquery(:query)) LIMIT :limit OFFSET :offset'; $query = $this->_em->createNativeQuery($sql, $rsm); $query->setParameters(['query' => $q, 'limit' => $perPage, 'offset' => ($page - 1) * $perPage, 'config' => 'english']); $result = $query->getResult(); $ret = []; foreach ($result as $row) { $ret[] = $row[0]->setFileCount($row['file_count'])->setLineCount($row['line_count'])->setTweetCount($row['tweet_count']); } return new SearchResult($q, $ret, $total, $page, $perPage); }
/** * Search for a line * * @param string $q The search query * @param integer $page The page of results to retrieve * @param integer $perPage How many results to retrieve * @return SearchResult The search results * @see ChaosTangent\FansubEbooks\Bundle\AppBundle\DataFixtures\ORM\CreateSearchIndex */ public function search($q, $page = 1, $perPage = 30, Series $series = null) { if (empty(trim($q))) { return new SearchResult($q, [], 0, $page, $perPage); } // default query parameters $defaultParams = [':query' => trim($q), ':config' => 'english']; // default where clause $whereClause = 'WHERE to_tsvector(:config, l.line) @@ to_tsquery(:query)'; if ($series !== null) { $whereClause .= ' AND f.series_id = :series'; $defaultParams['series'] = $series->getId(); } // count total results from search query $countSql = 'SELECT COUNT(l.id) FROM lines l JOIN files f ON f.id = l.file_id ' . $whereClause; $total = $this->_em->getConnection()->fetchColumn($countSql, $defaultParams, 0); // get the selected page of results from search query $rsm = new ResultSetMappingBuilder($this->_em); $rsm->addRootEntityFromClassMetadata('ChaosTangent\\FansubEbooks\\Entity\\Line', 'l'); $rsm->addScalarResult('positive_votes', 'positive_votes', 'integer'); $rsm->addScalarResult('negative_votes', 'negative_votes', 'integer'); $rsm->addScalarResult('tweet_id', 'tweet_id'); $sql = 'SELECT ' . $rsm->generateSelectClause() . ', SUM(CASE WHEN v.positive = true THEN 1 ELSE 0 END) AS positive_votes, SUM(CASE WHEN v.positive = false THEN 1 ELSE 0 END) AS negative_votes, t.tweet_id FROM lines l JOIN files f ON f.id = l.file_id LEFT JOIN votes v ON v.line_id = l.id LEFT JOIN tweets t ON t.line_id = l.id ' . $whereClause . ' GROUP BY l.id, t.tweet_id ORDER BY ts_rank(to_tsvector(:config, l.line), to_tsquery(:query)) LIMIT :limit OFFSET :offset'; $query = $this->_em->createNativeQuery($sql, $rsm); $query->setParameters(array_merge($defaultParams, ['limit' => $perPage, 'offset' => ($page - 1) * $perPage])); $result = $query->getResult(); $ret = []; foreach ($result as $row) { $ret[] = $row[0]->setPositiveVoteCount($row['positive_votes'])->setNegativeVoteCount($row['negative_votes'])->setTweetId($row['tweet_id']); } // bundle it all into a searchresult object return new SearchResult($q, $ret, $total, $page, $perPage); }
public function createPagination($query, $page, $limit) { $rsm = new ResultSetMappingBuilder($this->em); $rsm->addScalarResult('count', 'count'); // get sql, get the from part and remove all other fields then the id field // so that we have a query who select only one field // for count this is better because we dont need the data $queryCount = $query->getSql(); $queryCountTmp = explode("FROM", $queryCount); $queryCountSelect = array_shift($queryCountTmp); $queryCountEnd = implode("FROM", $queryCountTmp); $queryCountSelect = explode(",", $queryCountSelect); $queryCountSelect = reset($queryCountSelect); if (strpos($queryCountEnd, "GROUP BY") === false) { $queryCount = " SELECT COUNT(*) as count FROM " . $queryCountEnd; } else { $queryCount = "SELECT COUNT(*) as count FROM (" . $queryCountSelect . " FROM " . $queryCountEnd . ") as temp"; } // create now the query based on the native sql query and get the count $queryCount = $this->em->createNativeQuery($queryCount, $rsm); $queryCount->setParameters($query->getParameters()); $count = $queryCount->getSingleScalarResult(); if (!$count) { $count = 0; } if ($page === 'last') { $page = $count / $limit; $page = ceil($page); } if ($page <= 0) { $page = 1; } $query->setHint('knp_paginator.count', $count); $pagination = $this->paginator->paginate($query, (int) $page, $limit, array('distinct' => false)); return $pagination; }
/** * @expectedException Doctrine\ORM\Cache\CacheException * @expectedExceptionMessage Second level cache does not support scalar results. */ public function testScalarResultException() { $result = array(); $key = new QueryCacheKey('query.key1', 0); $rsm = new ResultSetMappingBuilder($this->em); $rsm->addScalarResult('id', 'u'); $this->queryCache->put($key, $rsm, $result); }