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

The query as a string is pretty useless unless you're thinking about appending to it as a string which is ugly since Doctrine has so much easy to use functionality on dynamically building SQL queries. — Mihai Stancu, Apr 25 '14 at 07:56
if it was useless they wouldn't have created the getDQL() method (see answer) — user3531149, Apr 25 '14 at 10:14
It's useless for you and me -- the beneficiary/user of the library -- it is useful for lower level work. — Mihai Stancu, Apr 25 '14 at 10:18
Maybe for you, but I need to pass the getDQL() method to a bundle im using for pagination — user3531149, Apr 25 '14 at 10:20
That's a lower level integration. It's pretty stupid that the 3rd party bundle needs the DQL... why would it need a string if they could use the object directly and they could obtain the string from the object themselves? — Mihai Stancu, Apr 25 '14 at 10:21
Another example of lower level integration: without getSQL you can't actually run the SQL queries against the database back-end (ex.: MySQL) because MySQLi / PDO take strings as parameters. Doctrine does this under the covers in `getResult` it fetches the SQL string and uses MySQLi / PDO to communicate with the database. — Mihai Stancu, Apr 25 '14 at 10:27

Mihai Stancu · Accepted Answer · 2014-04-25T10:30:52.060

3

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();
    }
}

edited Apr 25 '14 at 10:30

answered Apr 25 '14 at 07:41

Mihai Stancu

15,848
2
33
51

is there a way to get it from the controller without having to retype the string for every controller? in this case like $image->getSQL() or something? – user3531149 Apr 25 '14 at 07:44
Break it into two functions -- one builds the query and one calls it. You should be doing this in every repository you have so you can easily alter a query without having to copy-paste code. – Mihai Stancu Apr 25 '14 at 07:51
sir since the second function throws an exception trying to call itself, I wouldn't qualify it as a working answer however I see how your answer was helpful so just accept the edit and were done – user3531149 Apr 25 '14 at 10:29
1

I didn't see an edit request -- if I had I would have accepted it. Or if you would have pointed the comment out to me in a comment etc.. – Mihai Stancu Apr 25 '14 at 10:32
1

I am reviewing the edit request but it's replacing my answer with yours entirely. when the problem is the function name typo which causes endless recursion. – Mihai Stancu Apr 25 '14 at 10:35
1

I corrected the function name to call the query builder function. – Mihai Stancu Apr 25 '14 at 10:36
it seems like if you're using `createQueryBuilder()` you'll have to chain first with `$query->getQuery()->getSql()` – arvil Oct 29 '18 at 11:25

How to get the raw query from EntityRepository from the controller

1 Answers1