Need help for performing join Query with QueryBuilder

Question

I got a working SQL query : select p.name, p.id from acv_project p join acv_product prod on prod.project_id = p.id where prod.weight <> 0 and p.green_user_id = 18

If i pass it into a `

$stmt = $em->getConnection()->prepare($rawSql);
        $stmt->execute([]);

        $projects = $stmt->fetchAll();

It works but i'd like to pass it by adding the "green_user_id" as a parameter and not always 18.

When i try with this code : `

$sql2 = "select p from ArtoAcvBundle:Project p join prod ArtoAcvBundle:Product on prod.project_id = p.id where prod.weight <> 0 and p.green_user_id =:userId";
        $query2 = $em->createQuery($sql2)->setParameters(
                array('userId' => $userId));
        
        $projects = $query2->getResult();

I get [Semantical Error] line 0, col 48 near 'ArtoAcvBundle:Product': Error: Identification Variable prod used in join path expression but was not defined before.

And with QueryBuilder, i tried lots of thing but fails to understand how to write it.

Here are some links to my 2 Doctrine entities :

Entity Product

Entity Project

Thanks for help !

score 0 · Answer 1 · answered Oct 31 '22 at 22:49

0

Proof with:

$sql2 = "select p from ArtoAcvBundle:Project p join ArtoAcvBundle:Product prod where prod.weight <> 0 and p.green_user_id =:userId";

answered Oct 31 '22 at 22:49

Francisco

194
2
9

score 0 · Answer 2 · answered Oct 31 '22 at 23:04

0

Yep, great thanks for having found this solution. I continued to search and find there existed a bindValue() method in Doctrine.

So i passed my parameter with the raw SQL modified and it works

answered Oct 31 '22 at 23:04

user3735247

11
2

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 05 '22 at 00:27

score 0 · Answer 3 · answered Nov 01 '22 at 12:43

Example with QueryBuilder

// select p from ArtoAcvBundle:Project p join prod ArtoAcvBundle:Product on prod.project_id = p.id where prod.weight <> 0 and p.green_user_id =:userId

$query = $this->getRepository(Project::class)->createQueryBuilder('p');
$query->join('p.products' , 'prod')
      ->andWhere('prod.weight <> 0')
      ->andWhere('p.greenUser = :user')
      ->addParameter('user', $youruserEntity);

return $query->getQuery()->getResult();

Need help for performing join Query with QueryBuilder

3 Answers3