CODEWITHSUNDEEP
phpmysqlsymfonydoctrine-orm
Lukas Klizas
Lukas Klizas

Reputation: 175

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

I'm using this query builder in my repository:

public function findByCityCategory($city, $category)
{
    $qb = $this->createQueryBuilder('e')
        ->select(['e.id','e.title','e.address', 'e.lat', 'e.lng', 'e.siteUrl', 'e.phoneNo', 'w', 'd.id as category', 'avg(r.rating) as rating'])
        ->innerJoin('e.workingTimes', 'w')
        ->innerJoin('e.category', 'd')
        ->where('d.id = :categoryId')
        ->andWhere('e.city = :cityId')
        ->leftJoin('e.ratings', 'r')
        ->groupBy('r.place')
        ->setParameter('categoryId', $category)
        ->setParameter('cityId', $city);

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

But when I try to execute it, I get: "message": "[Semantical Error] line 0, col -1 near 'SELECT e.id,': Error: Cannot select entity through identification variables without choosing at least one root entity alias.", "class": "Doctrine\\ORM\\Query\\QueryException",

I looked for similar problems here and here but none of these worked for me. My Entities looks like that:

<?php

namespace AppBundle\Entity;

use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\ORM\Mapping as ORM;
use Symfony\Component\Validator\Constraints as Assert;

/** 
 * Place
 *
 * @ORM\Table(name="place")
 * @ORM\Entity(repositoryClass="AppBundle\Repository\PlaceRepository")
 */
class Place
{
/**
 * @var int
 *
 * @ORM\Column(name="id", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private $id;

/**
 * @var string
 * @Assert\NotBlank()
 * @ORM\Column(name="title", type="string", length=255)
 */
private $title;

/**
 * @var string
 * @Assert\NotBlank()
 * @ORM\Column(name="address", type="string", length=255)
 */
private $address;

/**
 * @var float
 * @Assert\NotBlank()
 * @ORM\Column(name="lat", type="float")
 */
private $lat;

/**
 * @var float
 * @Assert\NotBlank()
 * @ORM\Column(name="lng", type="float")
 */
private $lng;

/**
 * @var string
 *
 * @ORM\Column(name="description", type="text", nullable=true)
 */
private $description;

/**
 * @var string
 *
 * @ORM\Column(name="fb_page", type="string", length=255, nullable=true)
 */
private $fbPage;

/**
 * @Assert\NotBlank()
 * @ORM\ManyToOne(targetEntity="City")
 * @ORM\JoinColumn(name="city_id", referencedColumnName="id")
 */
private $city;

/**
 * @Assert\NotBlank()
 * @ORM\ManyToOne(targetEntity="Category", inversedBy="places")
 * @ORM\JoinColumn(name="category_id", referencedColumnName="id")
 */
private $category;

/**
 * @var string
 *
 * @ORM\Column(name="site_url", type="string", length=255, nullable=true)
 */
private $siteUrl;

/**
 * @ORM\ManyToMany(targetEntity="WorkingTime", cascade={"persist"})
 * @ORM\JoinTable(name="places_workingtimes",
 *      joinColumns={@ORM\JoinColumn(name="place_id", referencedColumnName="id")},
 *      inverseJoinColumns={@ORM\JoinColumn(name="workingtime_id", referencedColumnName="id", unique=true)}
 *      )
 */
private $workingTimes;

/**
 * @var string
 * @ORM\Column(name="phone_no", type="string", length=255, nullable=true)
 *
 */
private $phoneNo;

/**
 * @ORM\OneToMany(targetEntity="Rating", mappedBy="place")
 */
private $ratings;

} ` 

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

 /**
 * Rating
 *
 * @ORM\Table(name="rating")
 * @ORM\Entity(repositoryClass="AppBundle\Repository\RatingRepository")
 */
class Rating
{
/**
 * @var int
 *
 * @ORM\Column(name="id", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private $id;

/**
 * @var int
 *
 * @ORM\Column(name="rating", type="smallint")
 */
private $rating;

/**
 * @ORM\ManyToOne(targetEntity="Place", inversedBy="ratings")
 * @ORM\JoinColumn(name="place_id", referencedColumnName="id")
 */
private $place;

/**
 * @ORM\ManyToOne(targetEntity="User")
 * @ORM\JoinColumn(name="user_id", referencedColumnName="id")
 */
private $user;

This error only occurs, when I try to select 'w'. So, how can I join this collection of objects?

Upvotes: 0

Views: 2367

Answers (2)

Lukas Klizas
Lukas Klizas

Reputation: 175

I've managed to do it by excluding unnecessary fields from Place entity using JMS Serializer's Exclude() annotation instead of selecting required fields from entity.

Upvotes: 0

Rapha&#235;l Mali&#233;
Rapha&#235;l Mali&#233;

Reputation: 4012

If you want to only get partial fields from your entity, you have to use the PARTIAL keyword as explained on documentation: http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/partial-objects.html

It would be better to load all the fields instead of some if you use the ORM query builder, because the ORM is made to work with objects. If you need only few fields, it can be a better practice to use the native SQL query builder.

Upvotes: 2

Related Questions