php - From Raw query (with subqueries) To doctrine query builder

Question

I have to Entities Nota (Mark) and Asignatura(Subject):

class Nota
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\Column(type="integer")
     */
    private $n_convocatoria;

    /**
     * @ORM\Column(type="date")
     */
    private $fecha;

    /**
     * @ORM\Column(type="float")
     */
    private $nota;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Alumno", inversedBy="notas")
     * @ORM\JoinColumn(nullable=false)
     */
    private $alumno;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Asignatura", inversedBy="notas")
     * @ORM\JoinColumn(nullable=false)
     */
    private $asignatura;
...

class Asignatura
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\Column(type="integer", unique=true)
     */
    private $codigo;

    /**
     * @ORM\Column(type="string", length=255)
     */
    private $nombre;

    /**
     * @ORM\Column(type="string", length=255)
     */
    private $nombre_ingles;

    /**
     * @ORM\Column(type="integer", nullable=true)
     */
    private $credects;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Grado", inversedBy="asignaturas")
     * @ORM\JoinColumn(nullable=false)
     */
    private $grado;

    /**
     * @ORM\ManyToMany(targetEntity="App\Entity\Alumno", mappedBy="asignaturas")
     */
    private $alumnos;

    /**
     * @ORM\OneToMany(targetEntity="App\Entity\Nota", mappedBy="asignatura")
     */
    private $notas;

    public function __construct()
    {
        $this->alumnos = new ArrayCollection();
        $this->notas = new ArrayCollection();
    }

What I'm trying to get is Last note from each Subject(Asignatura) from specific Student (alumno). I have a Raw query working, but I need to create it with the query builder because I need the entities Nota and Asignatura.

The raw query is:

SELECT 
    *
FROM
    (SELECT 
        id, nota, alumno_id, fecha, asignatura_id, n_convocatoria
    FROM
        nota n
    WHERE
        fecha IN (SELECT 
                MAX(fecha)
            FROM
                nota
            GROUP BY asignatura_id
            HAVING id = n.id)
    AND alumno_id = 1) AS temp
    JOIN
    asignatura a ON temp.asignatura_id = a.id
    ;

And what I'm trying to achieve is:

$qb  = $this->_em->createQueryBuilder('temp');
        $subFechas = $qb;
        $subFechas->select('MAX(s.fecha) ')
            ->from('App\Entity\Nota', 's')
            ->groupBy('s.asignatura')
            ->having('n.id=s.id')
            ->andHaving('s.alumno = ?val')
            ->setParameter('val', $id);
        //dd($subFechas->getDQL()); //"SELECT MAX(fecha) as temp FROM nota s GROUP BY asignatura HAVING id=s.id AND alumno = :val"
        $qb2  = $this->_em->createQueryBuilder();

        $qb2->select('n.id, n.nota, n.alumno, n.fecha, n.asignatura, n.n_convocatoria')
            ->from('App\Entity\Nota' , 'n')
            ->join('App\Entity\Asignatura','a')
            ->where($qb->expr()->in('n.fecha',$subFechas->getDQL()));

        $qb3 = $this->_em->createQueryBuilder('nota3');
        $qb3->select('note, a')
            ->from('App\Entity\Nota' , 'note')
            ->where($qb2->getDQL());
        dd($qb3->getDQL());

//"SELECT note, a FROM App\Entity\Nota note WHERE SELECT n.id, n.nota, n.alumno, n.fecha, n.asignatura, n.n_convocatoria FROM App\Entity\Nota n INNER JOIN App\Entity\Asignatura a WHERE n.fecha IN(SELECT MAX(s.fecha)  FROM App\Entity\Nota s GROUP BY s.asignatura HAVING n.id=s.id AND s.alumno = ?val) "

But when I execute:

$qb3->getQuery()->getSQL();

I get:

[Syntax Error] line 0, col 47: Error: Expected Literal, got 'SELECT'

What I'm doing wrong? DQL Query looks good...thanks

标签： phpdoctrine-ormdoctrinesymfony4query-builder

Answer 1

最后 Query 稍微容易一点，我可以做到这一点：

$sub = $this->createQueryBuilder('n');

        $sub->select('MAX(n.fecha)')
            ->where('n.alumno = :id')
            ->groupBy('n.asignatura');

        $qb  = $this->_em->createQueryBuilder('main');
        $qb->select('mm')
            ->from('App:Nota', 'mm')
            ->where($qb->expr()->In('mm.fecha', $sub->getDQL())
            );

        $qb->setParameter('id', $id);
        $query  = $qb->getQuery();

        return $query->getResult();