Doctrine 2: Find by entity with composite primary key

Question

I have two entity classes, Product and OrderEntry, defined like this (some annotations left out for compactness):

class Product {

   /**
    * @Id
    */
   protected $id;

   /**
    * @Column()
    * @Id
    */
   protected $prodNumber;

   /**
    * @Column()
    * @Id
    */
   protected $group;

   // more cols here
}

class OrderEntry {

   // more cols here

   /**
    * @ManyToOne(targetEntity="Product")
    * @JoinColumns({
    *      @JoinColumn(name="prodNumber", referencedColumnName="prodNumber"),
    *      @JoinColumn(name="group", referencedColumnName="group")
    * })
    */
   protected $Product;
}

Now I want to find an OrderEntry by its associated Product with the query builder. The most logical thing for me would be this:

class OrderEntryRepository extends EntityRepository  {

    public function findByProduct($product) {
       $qb = $this->getQueryBuilder('o');
       $qb->where($qb->expr()->eq('o.Product', '?1')
          ->setParameter(1, $product)
          ->setMaxResults(1);
       return $qb->getQuery()->execute(); 
    }
}

However, this throws an exception that says

A single-valued association path expression to an entity with a composite primary key is not supported. Explicitly name the components of the composite primary key in the query.

How do I name the components explicitly? I know I could do it with a JOIN, but I have no use for the Product in this case and it would just make the query more expensive.

There is a spelling error in @ManyToOne. It should be "Product" not "Produkt". Is this intentional? — kufi, Apr 13 '12 at 17:47
No, I've corrected it. It's not the source of the error, though. — chiborg, Apr 13 '12 at 18:01
Yes I do (have added them in the text). Have tried to remove them , but that did also not work. — chiborg, Apr 13 '12 at 21:54

score 1 · Answer 1 · answered Apr 21 '12 at 09:32

You can't avoid joining the products table with the current mapping:

public function findByProduct($product) {
   $qb = $this->getQueryBuilder('o');
   $qb
      ->join('o.Product', 'p')
      ->where('p.prodNumber = ?1')
      ->setParameter(1, $product->getProdNumber())
      ->andWhere('p.group = ?2')
      ->setParameter(2, $product->getGroup())
   ;
   return $qb->getQuery()->getOneOrNullResult(); 
}

You can add separate properties to OrderEntry, which would use the same columns as the JoinColumns, e.g.:

/**
 * @Column(name="prodNumber")
 */
protected $prodNumber;

And then you can use them in conditions:

... 
->where('o.prodNumber = ?1')
->setParameter(1, $product->getProdNumber()
...

Doctrine 2: Find by entity with composite primary key

1 Answers1