From SQL to DQL

Question

I want to change this SQL query : (i'm using a bundle) (Evenement is an entity and evenement_evenement is the result of self-referencing many to many of evenement)

SELECT * 
FROM   evenement e 
       natural JOIN evenement_evenement ee 
WHERE  e.id = ee.evenement_source 
       AND e.id = 3

Into DQL. For now I have this :

public function findAllEventAssociateByEvent($idEvent){
    $qb = $this->createQueryBuilder('e');
    $qb->add('select', 'e');
    $qb->from('Bundle:Evenement', 'e2');
    $qb->where('e = :evenement');
    $qb->andWhere('e2 in e.evenements');
    $qb->setParameter('evenement', $idEvent);
    return $qb;
    //select * from evenement e NATURAL join evenement_evenement ee where e.id = ee.evenement_source and e.id = $idEvent
}

And i have this :

$eventAssocies = $em->getRepository('Bundle:Evenement')->findAllEventAssociateByEvent($id)->getQuery()->getResult();

But it's not working, i have an error in my "andWhere", but I don't know why...

Possible duplicate of [SQL do DQL converting](https://stackoverflow.com/questions/48724916/sql-do-dql-converting) — Preciel, Mar 26 '18 at 12:39
What does "I have an error" mean? Is there any error message you forgot to share? — Nico Haase, Mar 26 '18 at 12:40
Yes, I've made some research, didn't help. And my error for this code is : [Syntax Error] line 0, col 104: Error: Expected Doctrine\ORM\Query\Lexer::T_OPEN_PARENTHESIS, got 'e' — Morgan Tartreau, Mar 26 '18 at 12:58

Gregoire Ducharme · Answer 1 · 2018-03-26T13:16:33.000

1

I think you misunderstood some stuff, I reckon you query should more look like this:

public function findAllEventAssociateByEvent($idEvent){
    $qb = $this->createQueryBuilder('e')
       ->join('e.evenement_evenement', 'e2')
       ->where('e = :evenement')
       ->setParameter('evenement', $idEvent);
    return $qb;
}

The join will do what you were trying to do with your AndWhere I reckon

edited Mar 26 '18 at 13:16

answered Mar 26 '18 at 12:39

Gregoire Ducharme

1,095
12
24

I have this query with your code "SELECT e FROM Bundle\Entity\Evenement e INNER JOIN e.evenement e2 WHERE e = :evenement", i need both condition :/ And this error : [Semantical Error] line 0, col 69 near 'e2 WHERE e =': Error: Class Bundle\Entity\Evenement has no association named evenement – Morgan Tartreau Mar 26 '18 at 12:51
I assumed you have a related entity mapped by a field called `evenement` but you have to adapt to your situation. – Gregoire Ducharme Mar 26 '18 at 12:59
Using join on your relating field will do your second condition `->join('e.relatingField, 'e2', 'WITH', 'e2.relatingField = id')` that's pretty much what it mean – Gregoire Ducharme Mar 26 '18 at 13:00

score 0 · Answer 2 · answered Mar 26 '18 at 12:57

0

You cannot use andWhere like that, you have to treat it like you treated your where clause. Declare a parameter, and later on, set it. From what I understand, you'd need to use a subquery for that e.evenements part. Whatever you expect to be in e.evenements part, extract it to a variable, e.g: $evenements and do the following:

$qb->andWhere('e2 IN (:evenements)')
   ->setParameter('evenements', $evenements, Doctrine\DBAL\Connection::PARAM_INT_ARRAY);

Assuming $evenements is an array. If it's a string, you can explode() it for example.

If you are looking for a quick solution, try this:

$qb = $this->createQueryBuilder('e')
           ->select('e.id')
           ->where('e.id = :evenement')
           ->setParameter('evenement', $idEvent);
 $evenementsIds = $qb->getQuery()->getResult(); // This will get you an array of ID's

$qb2 = $this->createQueryBuilder('e2')
            ->where('e2.id IN (:evenements)')
            ->setParameter('evenements', $evenementsIds, Doctrine\DBAL\Connection::PARAM_INT_ARRAY);

$result = $qb2->getQuery()->getResult();

answered Mar 26 '18 at 12:57

domagoj

906
1
8
20

What is result (what do you get) and what is a good result (what do you need to get)? Please, double check that you have something inside your database. Try to re-create query with a MySQL client to see whether you are actually querying with good parameters. – domagoj Mar 26 '18 at 13:13
I get 1 result, but I need to have 2 results (i have to results in my database). I get 2 result in phpmyadmin with that : select * from evenement e NATURAL join evenement_evenement ee where e.id = ee.evenement_source and e.id = 1 where 1 is the id of 1 evenement – Morgan Tartreau Mar 26 '18 at 13:17
If that's the case, double check your parameters and database entries. I did my best with the DQL syntax and since you get 'something' I think your question, which was related to the error, is answered. Now what's going on with your parameters and results, I cannot tell you anything else without more details. – domagoj Mar 26 '18 at 13:23
In my database : evenement : id, date... (with my entity Evenement) evenement_evenement = evenement_origin, evenement_target (generated from entity Evenement with manytomany) – Morgan Tartreau Mar 26 '18 at 13:37
evenement and evenement_evenement are two tables in my database – Morgan Tartreau Mar 26 '18 at 13:43

From SQL to DQL

2 Answers2