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.

[augustomurri]

Main entity Conferimenti.php

/**
 * Conferimenti
 *
 * @ORM\Table(name="conferimenti")
 * @ORM\Entity(repositoryClass="App\Repository\ConferimentiRepository")
 */
class Conferimenti
{
	/**
	 * @var int
	 *
	 * @ORM\Column(name="id", type="bigint", nullable=false)
	 * @ORM\Id
	 * @ORM\GeneratedValue(strategy="SEQUENCE")
	 * @ORM\SequenceGenerator(sequenceName="conferimenti_id_seq", allocationSize=1, initialValue=1)
	 */
	private $id;

	/**
	 * @ORM\ManyToOne(targetEntity="Infocomune")
	 * @ORM\JoinColumn(name="idcomune", referencedColumnName="id")
	 */
	private $idcomune;

	/**
	 * @ORM\ManyToOne(targetEntity="Inforifiuti")
	 * @ORM\JoinColumns({
	 *   @ORM\JoinColumn(name="tiporifiuto", referencedColumnName="codicerifiuto"),
	 *   @ORM\JoinColumn(name="idcomune", referencedColumnName="idcomune")
	 * })
	 */

	private $tiporifiuto;

Inforifiuti.php

/**
 * Inforifiuti
 *
 * @ORM\Table(name="inforifiuti")
 * @ORM\Entity
 */
class Inforifiuti
{
	/**
	 * @var int
	 *
	 * @ORM\Column(name="codicerifiuto", type="integer", nullable=false)
	 * @ORM\Id
	 * @ORM\GeneratedValue(strategy="NONE")
	 */
	private $codicerifiuto;

	/**
	 * @var int
	 *
	 * @ORM\Column(name="idcomune", type="integer", nullable=false)
     * @ORM\Id
	 * @ORM\GeneratedValue(strategy="NONE")
	 */
	private $idcomune;

	/**
	 * @var string
	 *
	 * @ORM\Column(name="descrizione", type="string", length=80, nullable=false)
	 */
	private $descrizione;

Infocomune.php

/**
 * Infocomune
 *
 * @ORM\Table(name="infocomune")
 * @ORM\Entity
 */
class Infocomune
{
	/**
	 * @var int
	 *
	 * @ORM\Column(name="id", type="integer", nullable=false)
	 * @ORM\Id
	 * @ORM\GeneratedValue(strategy="SEQUENCE")
	 * @ORM\SequenceGenerator(sequenceName="infocomune_id_seq", allocationSize=1, initialValue=1)
	 */
	private $id;

	/**
	 * @var string
	 *
	 * @ORM\Column(name="nome", type="string", length=80, nullable=false)
	 */
	private $nome;

I am tryng to execute this query (working on Postgres) but can't get it working with doctrine ORM :(

SELECT r.descrizione, SUM(c.peso) as totale
INNER JOIN inforifiuti AS r ON r.codicerifiuto = c.tiporifiuto AND r.idcomune = c.idcomune
WHERE c.idcomune = :id_comune AND c.dataora >= '2019-01-01' AND c.dataora <= '2019-10-30'
GROUP BY c.tiporifiuto, r.descrizione

Querybuilder

$query = $qb
	->select(array('r.descrizione','SUM(c.peso) as totale'))
	->innerJoin('c.tiporifiuto','r', Expr\Join::WITH, $qb->expr()->andX(
		$qb->expr()->eq('c.tiporifiuto', 'r.codicerifiuto'),
		$qb->expr()->eq('c.idcomune', 'r.idcomune')
	))
	->where('c.idcomune = :id_comune')
	->andWhere($qb->expr()->between('c.dataora', ':date_from', ':date_to'))
	->setParameter('id_comune', $id_comune)
	->setParameter('date_from', $date_from, \Doctrine\DBAL\Types\Type::DATETIME)
	->setParameter('date_to', $date_to, \Doctrine\DBAL\Types\Type::DATETIME)
	->groupBy('c.tiporifiuto')
	->addGroupBy('r.descrizione')
	->getQuery();

I get this error

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.

tryed to replace ->GroupBy with but same error

->add('groupBy', new Expr\GroupBy(['c.tiporifiuto', 'r.descrizione']))

[augustomurri]

UPDATE Tryed using direct DQL but same identical error

$query = $this->getEntityManager()->createQuery(
	'SELECT r.descrizione, SUM(c.peso) as totale
	 FROM App\Entity\conferimenti AS c
	 INNER JOIN App\Entity\inforifiuti AS r
	 WHERE c.idcomune = :id_comune AND c.dataora >= :date_from AND c.dataora <= :date_to
	 GROUP BY c.tiporifiuto, r.descrizione')
	->setParameter('id_comune', $id_comune)
	->setParameter('date_from', $date_from, \Doctrine\DBAL\Types\Type::DATETIME)
	->setParameter('date_to', $date_to, \Doctrine\DBAL\Types\Type::DATETIME);

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.

[fyrye]

The mapping for your relationships does not appear to be valid.

Without knowing your database table schema, to me it seems like you're incorrectly assigning conferimenti.idcomune to two separate table associations. Where Inforifiuti should have a ManyToOne relationship of the Infocomune entity instead of an Column(type="integer") data-type. Removing the Conferimenti::$idcomune property, as it is indirectly referenced by @ORM\JoinColumn(name="idcomune", referencedColumnName="idcomune").

For Example

$conferimenti = $em->find(Conferimenti::class, 1);
$conferimenti->getTiporifiuto()->getIdcomune()->getId();

I suggest you reevaluate the entity mappings.

---

However you also appear to have general issues with your DQL statements.

You have to keep in mind that DQL is working with entity mappings and NOT the database table column names. Which doctrine processes the entity mappings within the DQL into the specified column name references for use in an SQL query.

For a DQL WHERE condition to be valid, you need to specify the entity properties that reference columns, instead of specifying the entity properties that reference entity associations.

For Example: WHERE App\Entity\Infocomune = :id_comune is not valid, which is what your current statements translate to.

Based on your current mapping, the criteria for WHERE c.idcomune = :id_comune and GROUP BY c.tiporifiuto, are not correctly referenced, as c.idcomune is an App\Entity\Infocomune entity and c.tiporifiuto is an App\Entity\Inforifiuti entity.

However, as I mentioned, your association for Conferimenti::$tiporifiuto does not appear to be valid.

DQL

SELECT r.descrizione, SUM(c.peso) as totale
INNER JOIN c.idcomune AS i
WITH i.id = :id_comune
INNER JOIN c.tiporifiuto AS r
WHERE c.dataora >= :date_from 
AND c.dataora <= :date_to
GROUP BY r.codicerifiuto, r.descrizione

Additionally the QueryBuilder example is redefining a relationship that is already specified in your Entity mappings, as you do not need to add the JOIN conditions for the associations. You also need to refine the JOIN condition to account for the c.idcomune = :id_comune clause.

$expr = $qb->expr();
$qb->select(array('r.descrizione','SUM(c.peso) as totale'))
    ->innerJoin('c.idcomune', 'i', Expr\Join::WITH, $expr->eq('i.id', ':id_comune'))
    ->innerJoin('c.tiporifiuto', 'r')
    ->where($expr->between('c.dataora', ':date_from', ':date_to'))
    ->groupBy('r.codicerifiuto')
    ->addGroupBy('r.descrizione')
    ->setParameter('id_comune', $id_comune)
    ->setParameter('date_from', $date_from, \Doctrine\DBAL\Types\Type::DATETIME)
    ->setParameter('date_to', $date_to, \Doctrine\DBAL\Types\Type::DATETIME);

Result (albeit I am not aware if this is valid for PostgreSQL or your database table schema)

SQL

SELECT r.descrizione, SUM(c.peso) AS totale
FROM conferimenti AS c
INNER JOIN infocomune AS i
ON i.idcomune = c.idcomune
AND i.id = :id_comune
INNER JOIN inforifiuti AS r 
ON c.tiporifiuto = r.codicerifiuto
AND c.idcomune = r.idcomune
WHERE c.dataora >= :date_from
AND c.dataora <= :date_to
GROUP BY r.codicerifiuto, r.descrizione

Alternatively you can use the IDENTITY function in the WHERE clause, to retrieve the column references. However, IDENTITY does not work for the GROUP BY clause. Requiring you to GROUP BY r.codicerifiuto instead of GROUP BY IDENTITY(c.tiporifiuto, 'codicerifiuto').

DQL

SELECT r.descrizione, SUM(c.peso) as totale
INNER JOIN c.tiporifiuto AS r
WHERE IDENTITY(c.tiporifiuto, 'idcomune') = :id_comune
AND c.dataora >= :date_from 
AND c.dataora <= :date_to
GROUP BY r.codicerifiuto, r.descrizione

Results in

SQL

SELECT r.descrizione, SUM(c.peso) AS totale
FROM conferimenti AS c
INNER JOIN inforifiuti AS r 
ON c.tiporifiuto = r.codicerifiuto
AND c.idcomune = r.idcomune
WHERE c.idcomune = :id_comune
AND c.dataora >= :date_from
AND c.dataora <= :date_to
GROUP BY r.codicerifiuto, r.descrizione

---

Lastly the DQL statement that references the entity class directly, does require specification of the JOIN criteria, as it is the equivalent to specifying the table name in SQL.

For Example:

DQL

INNER JOIN App\Entity\inforifiuti AS r

Results in

SQL

INNER JOIN inforifiuti AS r

Instead you would want to use.

DQL

INNER JOIN App\Entity\Inforifiuti AS r
WITH IDENTITY(c.tiporifiuto, 'codicerifiuto') = r.codicerifiuto
AND IDENTITY(c.idcomune) = r.idcomune

Results in

SQL

INNER JOIN inforifiuti AS r
ON c.tiporifiuto = r.codicerifiuto
AND c.idcomune = r.idcomune

[iggyster]

I have reproduced the same issue within a separate repository. I tried to find a solution in Doctrine documentation but it's quite limited with examples of composite PK usage.

I didn't find a clear answer. Maybe someone like @Ocramius or anybody else can clarify the solution to me?

Thank you in advance.

[fyrye]

@iggyster It appears you have an incomplete reference in your DQL query.

As explained in the last section of my prior comment; "Lastly the DQL statement that references the entity class directly, does require specification of the JOIN criteria, as it is the equivalent to specifying the table name in SQL."

->from(Order::class, 'o')
->join(User::class, 'user', Join::WITH, 'o.user = user') // <-- incomplete column reference
->where('user.id = :id')
->setParameter('id', $userId)
->getQuery()

---

Solution

Without knowing more as to why you are manually wiring the relationship in the DQL, instead allow Doctrine to parse the relationships, where the above query would typically look like the following:

->select('o')
->from(Order::class, 'o')
->join('o.user', 'user', Join::WITH, 'user.id = :id') // <--- Doctrine reference of o.user (Order::$user) mapping with additional criteria appended
->setParameter('id', $userId)
->getQuery()

SQL

SELECT o.*
FROM orders AS o
INNER JOIN users AS user
ON o.user_id = user.id
AND o.user_status = user.status
AND user.id = :id

---

Alternative

Otherwise to manually wire the relationship in the DQL, the full criteria needs to be specified verbosely,

->select('o')
->from(Order::class, 'o')
->join(User::class, 'user', Join::WITH, "IDENTITY(o.user, 'status') = user.status AND IDENTITY(o.user, 'id') = user.id AND user.id = :id")
->setParameter('id', $userId)
->getQuery()

For clarification IDENTITY(o.user, 'status'), checks the name value from the JoinColumn(referencedColumn="status") definition of your Order::$user entity property, to be parsed as o.user_status in SQL, but since Order::$user_status is not a property of your Order entity it cannot be used directly as o.user_status in the DQL statement.

SQL

SELECT o.*
FROM orders AS o
INNER JOIN users AS user
ON o.user_status = user.status
AND o.user_id = user.id
AND user.id = :id

You may even be able to use the following DQL to improve query performance.

->select('o')
->from(Order::class, 'o')
->join(User::class, 'user', Join::WITH, "IDENTITY(o.user, 'status') = user.status AND IDENTITY(o.user, 'id') = user.id")
->where("IDENTITY(o.user, 'id') = :id")
->setParameter('id', $userId)
->getQuery()

SQL

SELECT o.*
FROM orders AS o
INNER JOIN users AS user
ON o.user_status = user.status
AND o.user_id = user.id
WHERE o.user_id = :id

[iggyster]

@fyrye Thank you for the explanation)

P.S. The issue can be closed.