Query from repository gives back semantical error - not sure why

Question

I'm rather new to Symfony, and I am playing a bit with it. I'm following the SymfonyCast tutorial and just updating my project, learning as I go.

So I have a Question entity, and Answer entity. One question can have many answers, and so my entities look like this:

Answer:

<?php

namespace App\Entity;

use App\Repository\AnswerRepository;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity(repositoryClass=AnswerRepository::class)
 */
class Answer
{
  /**
   * @ORM\Id()
   * @ORM\GeneratedValue()
   * @ORM\Column(type="integer")
   */
  private $id;

  /**
   * @ORM\Column(type="string", length=255)
   */
  private $content;

  /**
   * @ORM\Column(type="bigint", nullable=true)
   */
  private $vote = 0;

  /**
   * @ORM\ManyToOne(targetEntity="Question")
   */
  private $question;

  /**
   * @ORM\ManyToOne(targetEntity=User::class, inversedBy="answers")
   * @ORM\JoinColumn(nullable=false)
   */
  private $author;

  public function getId(): ?int
  {
    return $this->id;
  }

  public function getContent(): ?string
  {
    return $this->content;
  }

  public function setContent(string $content): self
  {
    $this->content = $content;

    return $this;
  }

  public function getVote(): ?string
  {
    return $this->vote;
  }

  public function setVote(?string $vote): self
  {
    $this->vote = $vote;

    return $this;
  }

  /**
   * @return mixed
   */
  public function getQuestion()
  {
    return $this->question;
  }

  /**
   * @param mixed $question
   */
  public function setQuestion(Question $question): void
  {
    $this->question = $question;
  }

  public function setVoteCount(int $vote): self
  {
    $this->vote = $vote;

    return $this;
  }

  public function getAuthor(): ?User
  {
      return $this->author;
  }

  public function setAuthor(?User $author): self
  {
      $this->author = $author;

      return $this;
  }
}

Question:

<?php

namespace App\Entity;

use App\Repository\QuestionRepository;
use Doctrine\ORM\Mapping as ORM;
use Gedmo\Mapping\Annotation as Gedmo;

/**
 * @ORM\Entity(repositoryClass=QuestionRepository::class)
 */
class Question
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

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

    /**
     * @Gedmo\Slug(fields={"title"})
     * @ORM\Column(type="string", length=100, unique=true)
     */
    private $slug;

    /**
     * @ORM\Column(type="string", length=255)
     */
    private $questionContent;

    /**
     * @ORM\Column(type="datetime", nullable=true)
     */
    private $publishedAt;

    /**
     * @ORM\ManyToOne(targetEntity=User::class, inversedBy="questions")
     * @ORM\JoinColumn(nullable=false)
     */
    private $author;

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getTitle(): ?string
    {
        return $this->title;
    }

    public function setTitle(string $title): self
    {
        $this->title = $title;

        return $this;
    }

    public function getSlug(): ?string
    {
        return $this->slug;
    }

    public function setSlug(string $slug): self
    {
        $this->slug = $slug;

        return $this;
    }

    public function getQuestionContent(): ?string
    {
        return $this->questionContent;
    }

    public function setQuestionContent(string $questionContent): self
    {
        $this->questionContent = $questionContent;

        return $this;
    }

    public function getPublishedAt(): ?\DateTimeInterface
    {
        return $this->publishedAt;
    }

    public function setPublishedAt(?\DateTimeInterface $publishedAt): self
    {
        $this->publishedAt = $publishedAt;

        return $this;
    }

    public function getAuthor(): ?User
    {
        return $this->author;
    }

    public function setAuthor(?User $author): self
    {
        $this->author = $author;

        return $this;
    }
}

So I wanted to execute this query

select question_id, count(question_id) as answer_number
from answer
group by question_id;

in my AnswerRepository so that I can get this data out - how many answers there are for a given question.

When I do this in TablePlus I get the correct results

So I made in my AnswerRepository

  public function findNumberOfAnswersForQuestions()
  {
    return $this->getOrCreateQueryBuilder()
      ->select('an.question as question_id, count(an.question) as answer_number')
      ->groupBy('an.question')
      ->getQuery()
      ->getResult();
  }

  private function getOrCreateQueryBuilder(QueryBuilder $qb = null)
  {
    return $qb ?: $this->createQueryBuilder('an');
  }

but this threw an error

[Semantical Error] line 0, col 10 near 'question as question_id,': Error: Invalid PathExpression. Must be a StateFieldPathExpression.

so I added IDENTITY around my question

  public function findNumberOfAnswersForQuestions()
  {
    return $this->getOrCreateQueryBuilder()
      ->select('IDENTITY(an.question) as question_id, count(an.question) as answer_number')
      ->groupBy('an.question')
      ->getQuery()
      ->getResult();
  }

And this works fine.

Since I'm new to Symfony and Doctrine, I'm wondering why is this necessary? I've looked at the docs, and it says

IDENTITY(single\_association\_path\_expression [, fieldMapping]) - Retrieve the foreign key column of association of the owning side

but I'm not sure I understand this. Do I need to do this always when handling foreign keys? Why?

Any help in explaining this is welcomed :)

Answer 1

A common mistake for beginners is to mistake DQL for being just some form of SQL and therefore trying to use table names and column names or join arbitrary tables together in a query. You need to think about DQL as a query language for your object model, not for your relational schema. The composition of the expressions in the SELECT clause also influences the nature of the query result. There are three cases: All objects, All scalars, Mixed. In your case, you are selecting an object and a scalar that has no relation to your object model, and IDENTITY, as was already mentioned, selects only the value of id of your object, turning your query result into array of scalars. You can rewrite it this way:

return $this->createQueryBuilder('an')
     ->select('q.id, count(an.question) as answer_number')
     ->leftJoin('an.question', 'q')
     ->groupBy('q')
     ->getQuery()
     ->getResult();