Translating a MySQL query to DQL

Question

This question is a follow-up to what I asked (and received a working answer to) here.

How can I translate this into DQL? The docs on JOINs is a bit confusing to me.

---

EDIT:

I'm using Doctrine with Symfony2, and have the following entities:

Question:

/**
 * @ORM\Entity
 * @ORM\Table(name="Question", indexes={@ORM\Index(name="id_idx", columns={"id"})})
 */

class Question
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

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

    /**
     * @var array scores
     *
     * @ORM\OneToMany(targetEntity="Score", mappedBy="question")
     */
    private $scores;

    // getters and setters
}

Score:

/**
 * @ORM\Entity
 * @ORM\Table(name="Score", indexes={@ORM\Index(name="id_idx", columns={"id"})})
 */

class Score
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @var integer $question
     *
     * @ORM\ManyToOne(targetEntity="Question", inversedBy="scores")
     * @ORM\JoinColumn(name="question_id", referencedColumnName="id")
     */
    private $question;

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

    // getters and setters
}

I've used the following query:

$query = $em->createQuery('SELECT q AS question, AVG(s.score) AS average FROM CMSBundle:Question q JOIN q.scores s GROUP BY q.id ORDER BY q.id ASC');

$questions = $query->getResult();

However, with that query, $questions contains 0 elements. I'm not getting any errors, either (at least, none that PhpStorm can find in its debugger).

With a lack of feedback on why I'm getting literally nothing from my query, I'm at a bit of a loss. Any help would be appreciated.

Answer 1

I remember coming across this exactly problem just last week. I spent a long time figuring out how do this and I managed to come up with the following DQL. Add a new method to your Question's repository class.

I've had to tweak my own code to match the question so no guarantee it work work but give it a try.

<?php

namespace Acme\CMSBundle\Entity;

use Doctrine\ORM\EntityRepository;

class QuestionRepository extends EntityRepository
{
    /**
     * @return array
     */
    public function findAverageScoresPerQuestion()
    {
        $dql = <<<SQL
SELECT
    q question,
    AVG(s.score) average
FROM
    Acme\CMSBundle\Entity\Question q,
    Acme\CMSBundle\Entity\Score s
WHERE
    s.question = q.id
GROUP BY q.id
ORDER BY q.id ASC
SQL;

        $q = $this->_em->createQuery($dql);

        return $q->getResult();
    }
}

When rendering the result with Twig from the controller, the question properties are nested one level deeper

public function averageScoresAction()
{
    $em = $this->getDoctrine()->getManager();
    $questions = $em->getRepository('AcmeCMSBundle:Question')->findAverageScoresPerQuestion();

    return $this->render('AcmeCMSBundle:Question:questions.html.twig', array(
        'questions' => $questions
    ));
}

In questions.html.twig

  <table>
    <thead>
      <tr>
        <th>Question
        <th>Average Score
      </tr>
    </thead>
    <tbody>
    {% for q in questions %}
      <tr>
        <td>{{ q.question.question }}
        <td>{{ q.average }}
      </tr>
    {% else %}
      <tr>
        <td colspan="2">No results found
      </tr>
    {% endfor %}
    </tbody>
  </table>