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.

Processional

Answer 1

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().

Processional

Answer 2

try changing

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

to

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

or change hydration to array

Abuttals

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 – Processional

Answer 3

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

Holzman

Moving to another EntityRepository didn't work. Changing leftJoin parameters as stated in this answer helped me. – Incite