public function findRelatedItems(Taggable $item, $class, $locale, $nbOfItems = 1)
{
$instance = new $class();
if (!$instance instanceof Taggable) {
return NULL;
}
$em = $this->em;
$rsm = new ResultSetMappingBuilder($em);
$rsm->addRootEntityFromClassMetadata($class, 'i');
$meta = $em->getClassMetadata($class);
$tableName = $meta->getTableName();
$escapedClass = str_replace('\\', '\\\\', $class);
$query = <<<EOD
SELECT i.*, COUNT(i.id) as number
FROM {$tableName} i
LEFT JOIN kuma_taggings t
ON t.resource_id = i.id
AND t.resource_type = '{$instance->getTaggableType()}'
WHERE t.tag_id IN (
SELECT tg.tag_id
FROM kuma_taggings tg
WHERE tg.resource_id = {$item->getId()}
AND tg.resource_type = '{$item->getTaggableType()}'
)
AND i.id <> {$item->getId()}
EOD;
if ($item instanceof AbstractPage) {
$query .= <<<EOD
AND i.id IN (
SELECT nodeversion.refId
FROM kuma_nodes as node
INNER JOIN kuma_node_translations as nodetranslation
ON node.id = nodetranslation.node
AND nodetranslation.lang = '{$locale}'
INNER JOIN kuma_node_versions as nodeversion
ON nodetranslation.publicNodeVersion = nodeversion.id
AND nodeversion.refEntityname = '{$escapedClass}'
AND node.deleted = 0
AND nodetranslation.online = 1
)
EOD;
}
$query .= <<<EOD
GROUP BY
i.id
HAVING
number > 0
ORDER BY
number DESC
LIMIT {$nbOfItems};
EOD;
$items = $em->createNativeQuery($query, $rsm)->getResult();
return $items;
}
