0

I'm trying to make an 2 addSelect query in relates Entities where only some relation should be added by a where condition.

I have the Entities:

  • Maquina => a Server.
  • Fisica => extends from Server (it will be a Physical Server)
  • Particion => extends from Server (it will be a Virtual Server and have one Physical Server)
  • Instancia => products installed in a Server. A Server could have many Instancias and a Instancia could only have one Server.
class Maquina
{
    public function __construct() {
        $this->instancias = new ArrayCollection();
        $this->particiones = new ArrayCollection();
    }

    /**
     * @ORM\OneToMany(targetEntity="App\Entity\Instancia", mappedBy="maquina")
     */
    private $instancias;
    
    /**
     * @ORM\OneToMany(targetEntity="App\Entity\Particion", mappedBy="maquina")
     */
    private $particiones;
}

class Fisica extends Maquina{}

class Particion extends Maquina
{
    public function __construct() {
        $this->instancias = new ArrayCollection();
    }

    /**
     * @ORM\OneToMany(targetEntity="App\Entity\Instancia", mappedBy="maquina")
     */
    private $instancias;
    
    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Maquina", inversedBy="particiones")
     * @ORM\JoinColumn(name="contenedor", referencedColumnName="id")
     */
    private $contenedor;
}

class Instancia
{
   /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Maquina", inversedBy="instancias")
     * @ORM\JoinColumn(name="maquina", referencedColumnName="id", nullable=false))
     */
    private $maquina;
}

What I'm triying to do is a Query to return al the Physycal Server (Fisica) that have a specific product (with their id) or that have any Virtual Server with the specific product (with their id). For example, I need the following results:

/*A Physical Server with the specific product. The virtuals Server of this Pshysical Server doesn't have the specific products*/
Array ( [id] => 1877 [nombre] => physicalA [discriminador] => fisica [instancias] => Array ( [0] => Array ( [id] => 28000 ) ) [particiones] => Array ( ) )

/*A Physical Server without the specific prodcuto. The virtuals Server of this Physical Server have the specific products*/
Array ( [id] => 1925 [nombre] => physicalB [discriminador] => fisica [instancias] => Array ( ) [particiones] => Array ( [0] => Array ( [id] => 9183 [nombre] => virtualB [discriminador] => particion [instancias] => Array ( [0] => Array ( [id] => 27847 ) ) ) ) ) 

So I do the Query:

$qb = $this->em->createQueryBuilder();
    $qb->select('partial F.{id, nombre}')
        ->from('App\Entity\Fisica', 'F')             
        ->leftJoin('F.instancias', 'I')->addSelect('partial I.{id}')
        ->where('I.producto = :producto AND I.edicion = :edicion')
        ->leftJoin('F.particiones', 'MV')->addSelect('partial MV.{id, nombre}')
        ->leftJoin('MV.instancias', 'IMV')->addSelect('partial IMV.{id}')
        ->orWhere('IVM.producto = :producto AND IVM.edicion = :edicion')
        ->setparameter('producto', $producto)
        ->setparameter('edicion', $edicion);  
    $fisicas = $qb->getQuery()->getArrayResult();

The problem is that when the product is found only in the Virtual Servers, the Physical Server returns all products even if they are not specified.

I thinks there is some problems with the where/orWhere, but I don't know how to do it.

Any idea to get it? Thanks!

EDITED: Try do filter only results with Instances (does not works because the groupBy show only first appearance of Particion)

$qb = $this->em->createQueryBuilder();
    $qb->select('partial F.{id, nombre, clase, coresTotales}')
        ->from('App\Entity\Fisica', 'F')             
        ->leftJoin('F.instancias', 'I', 'WITH', 'I.producto = :producto AND I.edicion = :edicion')->addSelect('partial I.{id}')
        ->leftJoin('F.particiones', 'MV')->addSelect('partial MV.{id, nombre, clase, coresVirtuales}')
        ->innerJoin('MV.instancias', 'IMV', 'WITH', 'IVM.producto = :producto AND IVM.edicion = :edicion')->addSelect('partial IMV.{id}')
        ->setparameter('producto', $productoLicenciable)
        ->setparameter('edicion', $edicion)
        ->setParameter('sinCoste', '%Sin Coste%')
        ->groupBy('F.id')->having('COUNT(I) > 0 OR COUNT(IMV) > 0');
Angel
  • 1,970
  • 4
  • 21
  • 30
  • There was a typo in my original answer which should have caused an exception in your query, you need to change `IVM.producto = :producto AND IVM.edicion = :edicion` to `IMV.producto = :producto AND IMV.edicion = :edicion` – Will B. Jul 24 '20 at 18:16
  • Does this answer your question? [INNER JOIN ON vs WHERE clause](https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause) – Will B. Jul 25 '20 at 02:45
  • 1
    In your `Maquina` class, I think the 2 properties you list should have visibility set to `protected`, not `private`. – ehymel Jul 26 '20 at 00:16

0 Answers0