Exemple #1
0
 /**
  * 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;
 }