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