Select only relevant news items that are linked on a page (multiple one two many relationships)

Question

I have a page entity that consists of multiple containers. In those containers a user can link a news list. Those news lists again contain news items.

Now, I want to search for news items but I need the page the news list is linked on as well.

I've tried this:

$query = $this->getEntityManager()->createQuery('
    SELECT p, c, n, i
    FROM VendorNameBundle:Page p
    LEFT JOIN p.container c
    LEFT JOIN c.news n
    LEFT JOIN n.items i
    WHERE i.title LIKE :title
    GROUP BY i.id
');

Which basically works as it gives me the correct page. But to find the correct news item I would have to run through all containers and their news lists. And of course, when I retrieve the items I get all, not just the one important for the search.

How can I solve this in an elegant way?

Summarized: I need the news items and their corresponding page entities. How can I solve this in a neat and elegant way?

Note: If a news list is listed on multiple pages, one page in the result is enough and it's not important which.

Edit

Here are the relations as requested in the comments:

PAGE

/**
 * @ORM\OneToMany(targetEntity="Container", mappedBy="page", cascade={"remove"})
 * @ORM\OrderBy({"position" = "asc"})
 */
protected $containers;

CONTAINER

/**
 * @ORM\ManyToOne(targetEntity="Page", inversedBy="containers")
 * @ORM\JoinColumn(name="Page_id", referencedColumnName="id")
 */
private $page;

/**
 * @ORM\ManyToOne(targetEntity="News", inversedBy="containers")
 * @ORM\JoinColumn(name="news_id", referencedColumnName="id", onDelete="SET NULL")
 */
protected $news;

NEWS

/**
 * @ORM\OneToMany(targetEntity="Container", mappedBy="news")
 */
protected $containers;

Answer 1

The query

Because you are searching for news items, I suggest you work from those news items up to the pages (in stead of the other way around):

SELECT i, n, c, p
FROM VendorNameBundle:Item i
JOIN i.news n
JOIN n.container c
JOIN c.page p
WHERE i.title LIKE :title

This query does assume that every news item is within a news list, every news list is within a container and every container is within a page. If this is not true, use a LEFT JOIN in stead of a JOIN, otherwise you're going to miss some news items.

Grouping

This query will give you the data you want, but probably not formatted the way you want it. If you want to group the items per page or news list (etc), you'll need to do this manually (in code).

You can however let the query do some ordering, so the grouping (in code) becomes easier. The following example will order the results by first the title of the page, then the title of the news list:

SELECT i, n, c, p
FROM VendorNameBundle:Item i
JOIN i.news n
JOIN n.container c
JOIN c.page p
WHERE i.title LIKE :title
ORDER BY p.title ASC, n.title ASC

Now grouping can be done like this:

$currentPage     = null;
$currentNewsList = null;

foreach ($newsItems as $newsItem) {
    $newsList = $newsItem->getNews();
    $page     = $newsList->getContainer()->getPage();

    if ($page !== $currentPage) {
        $currentPage = $page;
        echo '- ' . $page->getTitle() . "\n";
    }

    if ($newsList !== $currentNewsList) {
        $currentNewsList = $newsList;
        echo '    - ' . $newsList->getTitle() . "\n";
    }

    echo '        - ' . $newsItem->getTitle() . "\n";
}

Which will print something like this:

- Page A
    - News list A
        - Found news item
    - News list C
        - Another found news item
- Page B
    - News list B
        - Yet another found news item