/** * Method used for search functionality * * @param int $tagID * @return array */ public function searchByTag($tagID) { $rsm = new ResultSetMappingBuilder($this->em); $rsm->addEntityResult(Page::class, 'p'); $rsm->addFieldResult('p', 'id', 'id'); $rsm->addFieldResult('p', 'title', 'title'); $rsm->addFieldResult('p', 'intro', 'intro'); $rsm->addFieldResult('p', 'intro_html', 'introHtml'); //$rsm->addFieldResult('p', 'text', 'text'); $rsm->addMetaResult('p', 'author', 'author'); $rsm->addMetaResult('p', 'url', 'url'); $rsm->addFieldResult('p', 'created_at', 'createdAt'); $rsm->addFieldResult('p', 'is_draft', 'isDraft'); $rsm->addFieldResult('p', 'published_at', 'publishedAt'); $rsm->addFieldResult('p', 'allowed_comments', 'allowedComments'); $rsm->addScalarResult('commentsCount', 'commentsCount', 'integer'); $rsm->addIndexByColumn('p', 'id'); $rsm->addJoinedEntityResult(Locale::class, 'l', 'p', 'locale'); $rsm->addFieldResult('l', 'locale_id', 'id'); $rsm->addFieldResult('l', 'locale_name', 'name'); $rsm->addFieldResult('l', 'locale_code', 'code'); $rsm->addFieldResult('l', 'locale_lang', 'lang'); $rsm->addFieldResult('l', 'locale_default', 'default'); $nativeQuery = $this->em->createNativeQuery('SELECT p.id, p.title, p.intro, p.intro_html, p.author, p.url, p.created_at, p.is_draft, p.published_at, p.allowed_comments, COUNT(c.page) AS commentsCount, l.id AS locale_id, l.name AS locale_name, l.code AS locale_code, l.lang AS locale_lang, l.default AS locale_default FROM ( SELECT pts.page_id, pts.tag_id FROM page_tag pts WHERE pts.tag_id = :id GROUP BY pts.page_id ) AS pt JOIN page p ON (p.id = pt.page_id AND p.is_draft = 0 AND p.published_at <= NOW()) JOIN locale l ON (l.id = p.locale) LEFT JOIN comment c ON (c.page = pt.page_id) GROUP BY pt.page_id', $rsm)->setParameter('id', $tagID); $pages = $nativeQuery->getResult(); $this->em->createQuery('SELECT PARTIAL page.{id}, tags FROM ' . Page::class . ' page LEFT JOIN page.tags tags INDEX BY tags.id WHERE page.id IN (:ids)')->setParameter('ids', array_keys($pages))->execute(); return $pages; }
/** * @return Role[] */ public function findRolesThatAreNotParents() { $rsm = new ResultSetMappingBuilder($this->em); $rsm->addEntityResult(Role::class, 'r'); $rsm->addFieldResult('r', 'id', 'id'); $rsm->addFieldResult('r', 'name', 'name'); $rsm->addJoinedEntityResult(Role::class, 'p', 'r', 'parent'); $rsm->addFieldResult('p', 'parent_id', 'id'); $rsm->addFieldResult('p', 'parent_name', 'name'); $nativeQuery = $this->em->createNativeQuery(' SELECT r.id, r.name, p.id AS parent_id, p.name AS parent_name FROM role r LEFT JOIN role p ON (p.id = r.parent_id) WHERE r.id NOT IN( SELECT r2.parent_id FROM role r2 WHERE r2.parent_id IS NOT NULL ) ', $rsm); return $nativeQuery->getResult(); }