Doctrine 2 INNER JOIN with Subquery

Question

I’m migrating my appointments to Doctrine using the Querybuilder. And I have a consultation with Inner Join with a subquery, would like to know how to do this using Doctrine.

        SELECT p.*
        FROM produtos p
        INNER JOIN
        (
            SELECT e.*, d.id_deposito, d.quantidade, d.qtde_temp, d.valor_produto, d.valor, d.valor_representante_produto, d.valor_representante, d.id_erp
            FROM produtos_estoque e
            INNER JOIN depositos_produtos_estoque d ON e.id_estoque = d.id_estoque
            WHERE e.inativo = '0' AND e.excluido = '0' AND d.excluido = '0' AND d.id_deposito = '1' AND d.inativo = 0 AND d.valor != '0.00'
        ) e ON p.id_produto = e.id_produto

Transferring to Doctrine I thought about the following solution, but it does not work as follows, I have researched but I have not found a solution that works in the Doctrine 2.

        $qb_estoque = $this->entityManager->createQueryBuilder();
        $qb_estoque->select("e, d1.id_deposito, d.quantidade, d.qtde_temp, d.valor_produto, d.valor, d.valor_representante_produto, d.valor_representante, d.id_erp")
            ->from(\model\entity\Produtos_estoque::get_class_name(), "e")
            ->innerJoin(\model\entity\Depositos_produtos_estoque::get_class_name(), "d", Join::WITH, "e.id_estoque = d.produtos_estoque")
            ->innerJoin("d.depositos", "d1")
            ->where($qb_estoque->expr()->andX(
                $qb_estoque->expr()->eq("e.inativo", 0),
                $qb_estoque->expr()->eq("e.excluido", 0),
                $qb_estoque->expr()->eq("d.inativo", 0),
                $qb_estoque->expr()->eq("d.excluido", 0),
                $qb_estoque->expr()->eq("d1.id_deposito", 1),
                $qb_estoque->expr()->neq("d.valor", 0)
            ));

        $qb = $this->entityManager->createQueryBuilder();
        $qb->select("p")
            ->from(\model\entity\Produtos::get_class_name(), "p")
            ->innerJoin(sprintf("(%s)", $qb_estoque->getQuery()->getSQL()).")", 'e', Join::WITH, 'p.id_produto = e.produtos');

Note: Yes, it should be done using subquery, as the rest of the query needs to use information that is contained in subquery.

Answer 1

Evaluating your query, I cannot see a reason to use subQuery. The data that is contained in subQuery is just the merge with the other data filter. Therefore, this query should bring the necessary entities:

$qb
  ->select("p")
  ->from(\model\entity\Produtos::get_class_name(), "p")
  ->join('p.estoques', 'estoques')
   -join('estoques.depositos', 'depositos')
  ->where('estoques.inativo = :estoqueInativo')
  ->andWhere('estoques.excluido = :estoqueExcluido')
  ->andWhere('depositos.excluido = :depositoExcluido')
  ->andWhere('depositos.id = :idDeposito')
  ->andWhere('depositos.inativo = :depositoInativo')
  ->andWhere($qb->expr()->neq('depositos.valor', '0'))
  ->setParameter('estoqueInativo', false)
  ->setParameter('estoqueExcluido', false)
  ->setParameter('depositoExcluido', false)
  ->setParameter('idDeposito', 1)
  ->setParameter('depositoInativo', false);

What may actually be a problem here is the case that you have not correctly related the entities. For this specific case, you need the entity Produto is related to the entity Estoque.

An example of how the three entities can be built. Product:

use Doctrine\ORM\Mapping as ORM;

class Produto
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue(strategy="IDENTITY")
     * @ORM\Column(name="id", type="integer", nullable=false)
     */
    private $id;

    /**
     * @ORM\OneToMany(targetEntity="Estoque", mappedBy="produto")
     */
    private $estoques;
}

Stockpile:

use Doctrine\ORM\Mapping as ORM;

class Estoque
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue(strategy="IDENTITY")
     * @ORM\Column(name="id", type="integer", nullable=false)
     */
    private $id;

    /**
     * @ORM\ManyToOne(targetEntity="Produto", inversedBy="estoques")
     * @ORM\JoinColumn(name="id", referencedColumnName="id", nullable=false)
     */
    private $produto;

    /**
     * @ORM\OneToMany(targetEntity="Deposito", mappedBy="estoque")
     */
    private $depositos;
}

Deposit

use Doctrine\ORM\Mapping as ORM;
class Deposito
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue(strategy="IDENTITY")
     * @ORM\Column(name="id", type="integer", nullable=false)
     */
    private $id;

    /**
     * @ORM\ManyToMany(targetEntity="Estoque", inversedBy="depositos")
     * @ORM\JoinColumn(name="id", referencedColumnName="id", nullable=false)
     */
    private $estoque;
}