Cannot select entity through identification variables without choosing at least one root entity alias

Question

Ads entity is described by geographic information: Country> Region>County. The Ads entity is only linked with County. Consequently, retrieving Ads by countries will require us joining entities twice.

My goal is counting the number of Ads for a given country. For that, I tried this DQL query but without success:

public function getMotorsAdsCountByCountry($slug){
    $qb = $this->_em->createQueryBuilder()
            ->select("m.id, COUNT(m.id) AS cnt")
            ->from("MinnAdsBundle:MotorsAds", "m")
            ->join("m.county","county")->addSelect("county")
            ->join("county.region","region")->addSelect("region")
            ->join("region.country","country")->addSelect("country")
            ->where("country.slug=:slug")
            ->setParameter(":slug", $slug);
    return $qb->getQuery()->getSingleScalarResult();
}

The error I got is:

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

I have even seen a post regarding the same error in this link but without success.

score 5 · Accepted Answer · answered Mar 09 '15 at 16:53

I found the solution:

    $qb = $this->_em->createQueryBuilder()
            ->select("COUNT(m.id) AS cnt")
            ->from("MinnAdsBundle:MotorsAds", "m")
            ->join("m.county","county")
            ->join("county.region","region")
            ->join("region.country","country")
            ->where("country.slug=:slug")
            ->setParameter(":slug", $slug);

I have just remove the addSelect() in addition to the modification of the select().

score 2 · Answer 2 · answered Mar 09 '15 at 08:53

2

try changing

->select("m.id, COUNT(m.id) AS cnt")

to

->select("m, COUNT(m.id) AS cnt")

or change hydration to array

answered Mar 09 '15 at 08:53

LPodolski

2,888
4
21
24

I tried both `->select("m, COUNT(m.id) AS cnt")` and `->select(array("m","COUNT(m.id) AS cnt"))` but without success! the error I get is: **500 Internal Server Error - NonUniqueResultException** – Amine Jallouli Mar 09 '15 at 15:34

score 1 · Answer 3 · answered Sep 28 '19 at 12:02

1

this is known as Doctrine limitation you should first join with MotorAds Entity like this

from('MinnAdsBundle','mi')->leftJoin(MotorsAds::class,'m','WITH','mi.motorsAds = m');

then you can select form MotorsAds directly refer to this answer

answered Sep 28 '19 at 12:02

Ghazaleh Javaheri

1,829
19
25

Moving to another EntityRepository didn't work. Changing `leftJoin` parameters as stated in this answer helped me. – k00ni Feb 28 '22 at 09:16

Cannot select entity through identification variables without choosing at least one root entity alias

3 Answers3

Linked