CODEWITHSUNDEEP
symfonydoctrine-orm
Ardavan Kalhori
Ardavan Kalhori

Reputation: 1698

Semantical Error - Doctrine 2.0

These are my tables:

    Table Gift:
        -id 
        -price
        ... 



    Table Couple:
         -id
         -name 
        ... 



    table registry:  //provide a many-many relation between gifts and couples 
         -id 
         -coupleId 
         -giftId 



    table purchase:
         -amount
         -registryId


I already wrote a sql query to get all the gift info for a specific couple

    $qb = $this->createQueryBuilder('g') //gift
    ->from('\BBB\GiftBundle\Entity\Registry', 'reg')
    ->select('g.id , g.price')
    ->where('reg.gift = g.id')
    ->andWhere('reg.couple = :coupleID')
    ->orderBy('reg.id','DESC')
    ->setParameter('coupleID', $coupleID);


OR 

     SELECT g.id , g.price,
    FROM gift g, registry reg       
    WHERE reg.gift_id = g.id AND reg.couple_id = 1



I also want to get the total amount that for the gifts that have been bought (if any)
EX. SUM(purchase.amount) as totalContribute

I have tried:

    $qb = $this->createQueryBuilder('g')
    ->from('\BBB\GiftBundle\Entity\Purchase', 'p')
    ->from('\BBB\GiftBundle\Entity\Registry', 'reg')
    ->select('g.id ,  g.price')
    ->addSelect('SUM(p.amount) as totalContribute')
    ->leftJoin('p','pp', 'ON','reg.id = pp.registry')
    ->where('reg.gift = g.id')
    ->andWhere('reg.couple = :coupleID')
    ->orderBy('reg.id','DESC')
    ->setParameter('coupleID', $coupleID);

but it gives me the following error:

    [Semantical Error] line 0, col 145 near 'pp ON reg.id': Error: Identification Variable p used in join path expression but was not defined before.

Upvotes: 0

Views: 1569

Answers (1)

Cyprian
Cyprian

Reputation: 11374

First of all, you should define join condition in your SQL statements after joins, not in WHERE clause. The reason is that it's really not efficient. So the query shoul look like:

 SELECT g.id , g.price,
 FROM gift g JOIN registry reg ON reg.gift_id = g.id
 WHERE reg.couple_id = 1

But about your Doctrine query, You get error because you're defining joins in wrong way. Your query should more like:

 $qb = $this->createQueryBuilder('g') // You don't have put "from" beacuse I assume you put this into GiftRepository and then Doctrine knows that should be \BBB\GiftBundle\Entity\Gift
    ->select('g.id ,  g.price')
    ->addSelect('SUM(p.amount) as totalContribute')
    ->join('g.purchase','p')          // pay attention for this line: you specify relation basing on entity property - I assume that is "$purchase" for this example        
    ->leftJoin('p.registry', 'reg')   // here you join with \BBB\GiftBundle\Entity\Purchase
    ->where('reg.couple = :coupleID')
    ->orderBy('reg.id','DESC')
    ->setParameter('coupleID', $coupleID);

Please treat this as pseudocode - I didn't check it works but it should like more like this.

One more thing - if your repository method returns object(s) of X entity you should put this method to XRepository.

Upvotes: 2

Related Questions