DQL query using oneToMany in WHERE

Question

This query does not work:

public function getByUnidadeAnoSemestre(int $unidadeId, int $ano, int $semestre) : array
{
    $query = $this->em->createQuery(
        'SELECT s,u,p FROM ' . 
            Servico::class . ' s ' .
        'INNER JOIN ' . 
            's.unidade u ' . 
        'INNER JOIN ' . 
            's.precificacoes p ' .
        'WHERE ' . 
            'u.id = :unidadeId ' . 
            'AND p.anoLetivo = :ano ' . 
            'AND p.semestre = :semestre'
    )->setParameters([
        "unidadeId" => $unidadeId,
        "anoLetivo" => $ano,
        "semestre" => $semestre
    ]);

    return $query->getResult();

}

The mappings in the Service class are like this:

/**
* @OneToMany(targetEntity="Precificacao", mappedBy="precificacoes", cascade={"persist"})
* @var Collection 
*/
private $precificacoes;

/**
* @ManyToOne(targetEntity="Unidade", inversedBy="id", cascade={"persist"})
* @JoinColumn(name="unidadeId", referencedColumnName="id")
* @var Unidade 
*/
private $unidade;

The error message in Slim is

"Type of Association must be one of *_TO_ONE or MANY_TO_MANY"

And the error in the Php console is

PHP Notice: Undefined index: precificacoes in D: Projetos Financeiroescolarapi vendor Doctrine Orm lib Doctrine ORM Query Sqlwalker.php on line 946

Can you help me?

Answer 1

I was able to figure out the problem. It was the One-To-Many Bidirectional mapping between Service and Pricing.

I left the Service class like this

/**
* @OneToMany(targetEntity="Precificacao", mappedBy="servico", cascade={"persist"})
* @var Collection 
*/
private $precificacoes;

And the Price class so

/**
 * @ManyToOne(targetEntity="Servico", inversedBy="precificacoes", cascade={"persist"})
 * @JoinColumn(name="servicoId", referencedColumnName="id")
 * @var Servico
 */
private $precificacoes;

Then I corrected the query

public function getByUnidadeAnoSemestre(int $unidadeId, int $ano, int $semestre) : array
{
    $query = $this->em->createQuery(
        'SELECT s,u,p FROM ' . 
            Servico::class . ' s ' .
        'INNER JOIN ' . 
            's.unidade u ' . 
        'INNER JOIN ' . 
            's.precificacoes p ' .
        'WHERE ' . 
            'u.id = :unidadeId ' . 
        'AND p.anoLetivo = :ano ' . 
            'AND p.semestre = :semestre'
        )->setParameters([
            "unidadeId" => $unidadeId,
            "ano" => $ano,
            "semestre" => $semestre
        ]);

    return $query->getResult();
}