Doctrine et ses 4 façons de requêter la base de données

DQL

$query = $this->em->createQuery(
    "
    SELECT p
    FROM AppBundle\Entity\RedditPost p
    WHERE p.id > :id
    "
)->setParameter('id', 50);

$data = $query->getResult();

Mais on ne peut splitter la requête simplement, alors qu’avec le query builder c’est possible:

// it is still DQL, but now it is painful

var $someConditional = false;

if ($someConditional === true) {
    $query = $this->em->createQuery(
        "
        SELECT p
        FROM AppBundle\Entity\RedditPost p
        WHERE p.id > :id
        "
    )->setParameter('id', 50);
} else {
    $query = $this->em->createQuery(
    "
        SELECT p
        FROM AppBundle\Entity\RedditPost p
    "
    );
}

$data = $query->getResult();

Query Builder

C’est mieux avec le query builder, plus facile de paramétrer sa requête

// using Doctrine's Query Builder

var $someConditional = false;

$query = $this->getDoctrine()->getRepository('AppBundle:RedditPost')->createQueryBuilder('p');

if ($someConditional === true) {
    $query
        ->where('p.id > :id')
        ->setParameter('id', 50)
    ;
} 

$data = $query->getQuery()->getResult();

https://codereviewvideos.com/course/doctrine-databasics/video/dql-vs-doctrine-query-builder

$query = $this->getDoctrine()
    ->getRepository('AppBundle:RedditPost')
    ->createQueryBuilder('p')

createQueryBuilder('p') implicitly creates the equivalent:

    SELECT p
    FROM AppBundle\Entity\RedditPost p

C’est la raison pour laquelle on n’a pas besoin de SELECT et FROM explicite

Native Query

Ressemble beaucoup au SQL, la notion de resultSetMapping est centrale.

use Doctrine\ORM\Query\ResultSetMapping;

$rsm = new ResultSetMapping();
// build rsm here

$query = $entityManager->createNativeQuery('SELECT id, name, discr FROM users WHERE name = ?', $rsm);
$query->setParameter(1, 'romanb');

$users = $query->getResult();

https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/reference/native-sql.html

On peut utiliser le resultSetMappingBuilder pour construire des requêtes Native Query

use Doctrine\ORM\Query\ResultSetMappingBuilder;

$sql = "SELECT u.id, u.name, a.id AS address_id, a.street, a.city " . 
       "FROM users u INNER JOIN address a ON u.address_id = a.id";

$rsm = new ResultSetMappingBuilder($entityManager);
$rsm->addRootEntityFromClassMetadata('MyProject\User', 'u');
$rsm->addJoinedEntityFromClassMetadata('MyProject\Address', 'a', 'u', 'address', array('id' => 'address_id'));

Raw SQL

On met du pur SQL dans un repository Doctrine

    public function getEvoplusStatusCountByMonthByCommercial(int $idUser, $start, $end)
    {
        $conn = $this->getEntityManager()->getConnection();

        $sql = "select count(*) as count, M.ID_ETAT_MATRICE, EM.LIBELLE_ETAT_MATRICE from MATRICE M 
inner join ETAT_MATRICE EM on 
M.ID_ETAT_MATRICE = EM.ID_ETAT_MATRICE 
where 
CONTRAT_DATE_SIGNATURE BETWEEN '$start' AND '$end'
AND USR_ID = $idUser GROUP by M.ID_ETAT_MATRICE; ";

        $stmt = $conn->prepare($sql);
        $stmt->execute();
        $res = $stmt->fetchAll();

        return $res;
    }

Vous aimerez aussi...