CODEWITHSUNDEEP
symfonyjoindoctrine-ormrepositorydql
wawa
wawa

Reputation: 5074

Symfony load joined entities with where and order by

I have two entities, first called Page, second one PageEntry

The PageEntries are mapped in Page by:

/**
 * @var \Doctrine\Common\Collections\Collection|PageEntry[]
 *
 * @ORM\ManyToMany(targetEntity="PageEntry")
 * @ORM\JoinTable(name="foobar_page_to_entries")
 * @ORM\OrderBy({"position" = "ASC"})
 */
private $entries;

I tried now to have a function in the PageRepository to load all the joined PageEntry with the condition hide = false and sorted by position ASC

To do so, I created this function:

public function getEntriesByPage(Page $page = null) {
    if (empty($page)) {
        return array();
    }

    $query = $this->_em->createQueryBuilder()
            ->select('e')
            ->from('FooBarBundle:Page', 'p')
            ->andWhere('p.id = :id')
            ->join('p.entries', 'e')
            ->andWhere('e.hide = :hide')
            ->addOrderBy('e.position', 'ASC')

            ->setParameters(array(
                'hide'  => false,
                'id'    => $page->getId()
            ))
    ;

    return $query->getQuery()->getResult();
}

Which results in a Semantical Error:

[Semantical Error] line 0, col -1 near 'SELECT e FROM': Error: Cannot select entity through identification variables without choosing at least one root entity alias.

What can I do, to fix this? Or is there a better way? (I tried to use Criteria but the version of Doctrine is a little outdated and tells me, that Criteria does only work on OneToMany relations).

Upvotes: 0

Views: 118

Answers (2)

Wilt
Wilt

Reputation: 44383

You have to add the root alias 'p' to your select statement for this query to work:

$query = $this->_em->createQueryBuilder()
    ->select('p', 'e')
    ->from('FooBarBundle:Page', 'p')
    ->andWhere('p.id = :id')
    ->join('p.entries', 'e')
    ->andWhere('e.hide = :hide')
    ->addOrderBy('e.position', 'ASC')

    ->setParameters(array(
        'hide'  => false,
        'id'    => $page->getId()
    ));

Since you are in your PageRepository you can do more simple like this:

$query = $this->createQueryBuilder('p')
    ->addSelect('e')
    ->andWhere('p.id = :id')
    ->join('p.entries', 'e')
    ->andWhere('e.hide = :hide')
    ->addOrderBy('e.position', 'ASC')

from is not necessary if you are already in the correct entity repository. You can just pass the alias for the entity (in this case 'p') directly to the createQueryBuilder method and skip the from part.

Upvotes: 0

Ashok Chitroda
Ashok Chitroda

Reputation: 361

change your query like this one and try it. let me know if any issue.

$query = $this->_em->createQueryBuilder()
            ->select('e')
            ->from('FooBarBundle:Page', 'p')
            ->join('p.entries', 'e')
             ->Where('p.id = :id')
            ->andWhere('e.hide = :hide')
            ->OrderBy('e.position', 'ASC')

            ->setParameters(array(
                'hide'  => false,
                'id'    => $page->getId()
            ))
    ;

Upvotes: 0

Related Questions