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