CODEWITHSUNDEEP
symfonyjoindoctrinedqldoctrine-query
user3735247
user3735247

Reputation: 11

Need help for performing join Query with QueryBuilder

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 !

Upvotes: 0

Views: 161

Answers (3)

Rufinus
Rufinus

Reputation: 30721

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

Upvotes: 0

user3735247
user3735247

Reputation: 11

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

Upvotes: 0

Francisco
Francisco

Reputation: 265

Proof with:

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

Upvotes: 0

Related Questions