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; }