Symfony2 Using Query in Custom Repository Class

Question

I have a Repository Class with a method to call a custom Query. When I try to call findAllWithRating() from inside a controller I got the following exception:

[2/2] QueryException: [Syntax Error] line 0, col 156: Error: Unexpected 'NULL'

If I try to call the query within phpmyadmin the query works great!

Any Idea?

<?php
namespace Anchorbrands\Bundle\MessageBundle\Entity;

use Doctrine\ORM\EntityRepository;

class MessageRepository extends EntityRepository
{

    public function findAllWithRating() {
        return $this->getEntityManager()
            ->createQuery("SELECT id, user_id, title, slug, content, question, image_name, created_at, updated_at,
                    MAX(CASE WHEN rating = '1' THEN totalCount ELSE NULL END) 'Rating 1',
                    MAX(CASE WHEN rating = '2' THEN totalCount ELSE NULL END) 'Rating 2',
                    MAX(CASE WHEN rating = '3' THEN totalCount ELSE NULL END) 'Rating 3'
            FROM
            (
                SELECT  a.id, a.user_id, a.title, a.slug , a.content, a.question, a.image_name, a.created_at, a.updated_at,
                        rating, COUNT(*) totalCount
                FROM    AnchorbrandsMessageBundle:Message a
                        LEFT JOIN AnchorbrandsMessageBundle:Rating b
                            ON a.id = message_id
                GROUP BY a.id, a.user_id, a.title, a.slug, a.content, a.question, a.image_name, a.created_at, a.updated_at, rating
            ) r
            GROUP BY id, user_id, title, slug, content, question, image_name, created_at, updated_at")->getResult();
    }

}

Topper Harley · Accepted Answer · 2019-01-11 19:28:45Z

9

You seem to mix DQL and SQL. You can have a look at what is possible with DQL here.

Looking at your query, I suggest that you use SQL instead of DQL.

An example of how to do a SQL query inside a repository:

$sql = 'SELECT * FROM table WHERE id = :id';
$params = array(
    'id' => 4,
);

return $this->getEntityManager()->getConnection()->executeQuery($sql, $params)->fetchAll();

edited Jan 11, 2019 at 19:28

Topper Harley

12.5k12 gold badges83 silver badges130 bronze badges

answered Jan 18, 2013 at 17:27

AdrienBrault

7,7454 gold badges33 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

smartius Over a year ago

I am using it like it is described here: symfony.com/doc/current/book/… the point is that i cant use raw sql cause doctrine wont map the propierties in this case.

Mohammad Fareed Over a year ago

Can we use parameter using bind .?

Collectives™ on Stack Overflow

Symfony2 Using Query in Custom Repository Class

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related