Symfony2 Using Query in Custom Repository Class
12,807
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();
Author by
smartius
Updated on June 04, 2022Comments
-
smartius almost 2 years
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(); } }