Doctrine 2 INNER JOIN with Subquery

Question

Doctrine 2 INNER JOIN with Subquery

Navigation

#1 by (0 votes)

2

I'm migrating my queries to doctrine using QueryBuilder . And I have a inner join query with a subquery , I'd 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

Doctrine I thought of the following solution, but it did not work as follows, I already researched but I did not find a solution that worked in 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 because the rest of the query needs to use information that is contained in the subquery.

php mysql doctrine doctrine-2

asked by anonymous 10.11.2016 / 20:33

1 answer

Metrics of machine learning [closed] Read Arduino Serial with bluetooth 4.0 using Evothings

score 0 · Answer 1

By evaluating your query, I can not see a reason to use the subquery. The data that is contained in the subQuery is only the filtering of the other data. 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 can really be a problem here is if you have not correctly mapped the entities. For this specific case, you need the Produto entity to be related to the Estoque entity.

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;
}

Stock:

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;
}