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

doctrine-orm, mysql, php, symfony

Solution

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

Problem

Original source