Select only the relevant news that are linked on the page (several two relationships)

Question

I have an object pagethat consists of several containers. In these containers, the user can bind a news list. Those news listcontain again news items.

Now I want to search news items, but I need pageand news listalso connected.

I 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 in order to find the right one news item, I would have to run through everything containerand their news lists. And of course, when I retrieve the elements, I get everything, and not just one important thing for the search.

How can I solve this in an elegant way?

: news items page. ?

. a news list , page , .

Edit

:

PAGE

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

/**
 * @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;

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

Answer 1

, ( , ):

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

, , , . , LEFT JOIN JOIN, .

, , , , . ( ..), ( ).

, ( ) . , , :

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

:

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

- :

- 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

Answer 2

RIGHT JOIN INNER JOIN Items Pages:

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

Answer 3

, items, :

$query = $em->createQuery("SELECT i FROM Vendor:Item i WHERE i.title LIKE :title");

items, .

, Item, , , - :

$items = $query->getResult();
$pages = array();
foreach ($items as $index => $item) {
  $pages[$index] = $item->getNews()->getContainer()->getPage();
}

, ( , , - ). , , , :

$pages[$index] = $item->getNews()[0]->getContainers()[0]->getPages()[0];

, , , Container - :

/**
 * @ORM\ManyToOne(targetEntity="Page", inversedBy="containers")
*/
private $page;
//this returns an instance of Page entity
public function getPage() { return $this->page; }

Page , , :

/**
 * @ORM\OneToMany(targetEntity="Container", mappedBy="page")
*/
private $containers;
//this returns an instance of PersistentCollection which is iterable, can be
// ArrayAccess'd, well, it a glorified array
public function getContainers() { return $this->containers; }

: , , , ( Doctrine) , Doctrine , .

, !

Answer 4

, - (http://doctrine-orm.readthedocs.org/en/latest/reference/association-mapping.html#one-to-many-bidirectional), , :

$query = $this->getEntityManager()->createQuery('
    SELECT i
    FROM VendorNameBundle:Items i
    WHERE i.title LIKE :title
    GROUP BY i.id
');

, . :

$item->getNewsList()->getContainer()->getPage();

and doctrine moisturizes objects. If the news list is on some pages (as you said in the note to your post), you will need to access any container and then the page.