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');