How to get the raw query from EntityRepository from the controller

Question

I need to get the raw query as a string, something like this.

$query = 'SELECT p FROM GabrielUploadBundle:Image p WHERE p.upvotes > x ORDER BY p.createdAt ASC';

My custom "findAllNewestByVotes" method contains the query.

class ImageRepository extends EntityRepository
{
    public function findAllNewestByVotes($maxvotes)
    {
        return $this->getEntityManager()
        ->createQuery(
            'SELECT p FROM GabrielUploadBundle:Image p WHERE p.upvotes > '.$maxvotes.' ORDER BY p.createdAt ASC')
        ->getResult();
    }

}
/**
 * @Route("/world/front",name="world-front")
 * @Template()
 */
public function indexAction()
{
    $images = $this->get('doctrine')->getRepository('GabrielUploadBundle:Image')->findAllNewestByVotes(50);

    ladybug_dump($images);
    return $this->render('GabrielLayoutBundle:Worldpage:index.html.twig',array('images'=>$images));
}

What I need is something like $images->getRawQuery() // returns query as a string

---

Solution (reference to best answer)

    /**
     * ImageRepository
     *
     * This class was generated by the Doctrine ORM. Add your own custom
     * repository methods below.
     */
    class ImageRepository extends EntityRepository
    {
        public function findAllNewestByVotes($maxvotes)
        {
            return $this->getEntityManager()
            ->createQuery(
                'SELECT p FROM GabrielUploadBundle:Image p WHERE p.upvotes > '.$maxvotes.' ORDER BY p.createdAt ASC');
        } 
    }

> create image repository

    $images = $this->get('doctrine')->getRepository('GabrielUploadBundle:Image')->findAllNewestByVotes(50);

 return the raw query as string like this

    $images->getDQL()

 return objects like this

    $images->getResult();

Answer 1

The raw query can be retrieved using:

$this->getEntityManager()
    ->createQuery('
        SELECT p
            FROM GabrielUploadBundle:Image p
            WHERE p.upvotes > '.$maxvotes.'
            ORDER BY p.createdAt ASC
    ')
    ->getSQL();

But this is a simple query, why not use DQL and add the parameters separately (using prepared statements which are safe from SQL inject attacks)?

$this->getEntityManager()
    ->createQueryBuilder()

    ->select('p')
    ->from('GabrielUploadBundle:Image')

    ->where('p.upvotes > :maxvotes')
    ->setParameter('maxvotes', $maxvotes)

    ->orderBy('p.createdAt', 'ASC')

    ->getSQL();

To be able to get the query (object) or query builder (object) from the controller you need to break the repository logic into 2 functions, one which builds the query and another which calls the query with parameters:

class ImageRepository extends EntityRepository
{
    public function findAllNewestByVotesQuery($maxvotes)
    {
        return $this->getEntityManager()
            ->createQueryBuilder()

            ->select('p')
            ->from('GabrielUploadBundle:Image')

            ->where('p.upvotes > :maxvotes')
            ->setParameter('maxvotes', $maxvotes)

            ->orderBy('p.createdAt', 'ASC');
    }

    public function findAllNewestByVotes($maxvotes)
    {
        return $this
            ->findAllNewestByVotesQuery($maxvotes)
            ->getQuery()
            ->getResult();
    }
}