How to turn this SQL query into Doctrine Query

Question

I'm using this native SQL query on symfony (Which is a really bad practice) but I'm struggling to get it running with DQL without triggering several errors.

SELECT Image.path, Image.name 
FROM Image
LEFT JOIN ImageVoters ON Image.id = ImageVoters.image_id
WHERE ImageVoters.voter_id =1
AND ImageVoters.action =  'favourite'
ORDER BY Image.created_at ASC

This is what I've got so far

    public function findMyFavourites()
    {
       return $this->getEntityManager()
       ->createQuery('SELECT p.path,p.name 
       FROM GabrielUploadBundle:Image p 
       LEFT JOIN GabrielUploadBundle:ImageVoters v 
       ON p.id  = v.image_id 
       WHERE v.action ="favourite" 
       ORDER BY p.created_at ASC')
       ->getResult();
    }

It throws this error for some reason

Error: Expected Doctrine\ORM\Query\Lexer::T_WITH, got 'ON'

This is the query that works

$em = $this->getDoctrine()->getManager();
$query = $em->getRepository('GabrielUploadBundle:Image')->createQueryBuilder('p')
    ->select(array('p.path', 'p.name','p.id','p.upvotes','p.imageowner','p.createdAt','p.updatedAt'))
    ->leftJoin('GabrielUploadBundle:ImageVoters', 'v', 'WITH', 'p.id = v.image_id')
    ->where("v.action = 'favourite'")
    ->andWhere("v.voter_id = 1")
    ->orderBy('p.createdAt', 'ASC')
    ->getQuery();

already tried that got [Syntax Error] line 0, col 204: Error: Expected Literal, got '"' — user3531149, Apr 17 '14 at 18:33

Javad · Accepted Answer · 2014-04-17T19:00:27.543

2

If you change ON to WITH your query will be OK.
You can also try

$query = $em->getRepository('GabrielUploadBundle:Image')->createQueryBuilder('p')
   ->select(array('p.path', 'p.image'))
   ->leftJoin('GabrielUploadBundle:ImageVoters', 'v', 'WITH', 'p.id = v.image_id')
   ->where("v.action = 'favourite'")
   ->orderBy('p.createdAt', 'ASC')
   ->getQuery();
$result = $query->getResult();

edited Apr 17 '14 at 19:00

answered Apr 17 '14 at 18:30

Javad

4,339
3
21
36

your query does have a lot of syntax errors, but it sure helped me get it done, check the "fixed" version on the edited answer – user3531149 Apr 17 '14 at 18:51
im selecting a few more fields tho. – user3531149 Apr 17 '14 at 18:55
You're right but those error which you say are because I don't know what do you have in your mapping for fields so I used the column names, and I thought in your mapping there is a relation between `Image` and `ImageVoters`; anyway I updated my answer, too :) – Javad Apr 17 '14 at 18:59
I think you forgot the "WHERE ImageVoters.voter_id =1" part sir – user3531149 Apr 17 '14 at 20:05

score 0 · Answer 2 · answered Apr 17 '14 at 23:16

0

pIn addition, if you have a relation between your Image model and ImageVoters model in Image Entity class, you can just do:

->leftJoin('p.imageVoters', 'v')

Doctrine will do the rest for you.

answered Apr 17 '14 at 23:16

Bartłomiej Wach

1,968
1
11
17

How to turn this SQL query into Doctrine Query

2 Answers2