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
– user3531149, Commented Apr 17, 2014 at 18:33

Javad · Accepted Answer · 2014-04-17 19:00:27Z

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, 2014 at 19:00

answered Apr 17, 2014 at 18:30

Javad

4,4073 gold badges23 silver badges36 bronze badges

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

4 Comments

user3531149 Over a year ago

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 Over a year ago

im selecting a few more fields tho.

Javad Over a year ago

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 :)

user3531149 Over a year ago

I think you forgot the "WHERE ImageVoters.voter_id =1" part sir

Bartłomiej Wach · Accepted Answer · 2014-04-17 23:16:40Z

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, 2014 at 23:16

Bartłomiej Wach

1,9861 gold badge11 silver badges17 bronze badges

Collectives™ on Stack Overflow

How to turn this SQL query into Doctrine Query

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related