aPHPéro Lille 29/10/2024 - Faire du vrai SQL avec Doctrine

Transcript

1. Veuillez patienter…
2. Faire du vrai SQL avec Doctrine aka Widop passion Ping-Pong SQL saison 2
3. Faire du vrai SQL avec Doctrine aka Widop passion Ping-Pong SQL saison 2
4. Thibault RICHARD Architecte Web Bénévole AFUP depuis plus de 2ans Agence spécialisée Symfony depuis 2010 Expertise PHP/Symfony, Sylius, VueJS, AWS 18 personnes Basé à Lille
5. Widop passion Ping-Pong
6. Widop passion Ping-Pong
7. Widop passion Ping-Pong
8. Widop passion Ping-Pong
9. Widop passion Ping-Pong
10. Widop passion Ping-Pong
11. Ping-Pong Manager
12. Pourquoi les ORM ?
13. select game.id, game.date, score.points, player.name from game join score on game.id = score.game_id join player on score.player_id = player.id order by date desc
14. from game join score on game.id = score.game_id join player on score.player_id = player.id select game.id, game.date, score.points, player.name order by date desc
15. select game.id, game.date, score.points, player.name from game join score on game.id = score.game_id join player on score.player_id = player.id order by date desc
16. order by date desc select game.id, game.date, score.points, player.name from game join score on game.id = score.game_id join player on score.player_id = player.id
17. select game.id, game.date, score.points, player.name from game join score on game.id = score.game_id join player on score.player_id = player.id order by date desc
18. game.id game.date score.points player.name 27936 2024-10-25 13:52:18 3 Raclo 27936 2024-10-25 13:52:18 11 Riri 27935 2024-10-25 13:16:09 11 Riri 27935 2024-10-25 13:16:09 7 Enide
19. game.id game.date score.points player.name 27936 2024-10-25 13:52:18 3 Raclo 27936 2024-10-25 13:52:18 11 Riri 27935 2024-10-25 13:16:09 11 Riri 27935 2024-10-25 13:16:09 7 Enide
20. Game 27936 id = 27936 date = "2024-10-25 13:52:18" Game 27935 id = 27935 date = "2024-10-25 13:16:09" Score Riri 27935 id = 1 points = 11 Score Enide 27935 id = 2 points = 7 Score Raclo 27936 id = 3 points = 3 Score Riri 27936 id = 4 points = 11 Player Riri id = 1 name = "Riri" Player Raclo id = 2 name = "Raclo" Player Enide id = 3 name = "Enide"
21. Game 27936 id = 27936 date = "2024-10-25 13:52:18" Game 27935 id = 27935 date = "2024-10-25 13:16:09" Score Riri 27935 id = 1 points = 11 Score Enide 27935 id = 2 points = 7 Score Raclo 27936 id = 3 points = 3 Score Riri 27936 id = 4 points = 11 Player Riri id = 1 name = "Riri" Player Raclo id = 2 name = "Raclo" Player Enide id = 3 name = "Enide"
22. Game 27936 id = 27936 date = "2024-10-25 13:52:18" Game 27935 id = 27935 date = "2024-10-25 13:16:09" Score Riri 27935 id = 1 points = 11 Score Enide 27935 id = 2 points = 7 Score Raclo 27936 id = 3 points = 3 Score Riri 27936 id = 4 points = 11 Player Riri id = 1 name = "Antho" Player Raclo id = 2 name = "Raclo" Player Enide id = 3 name = "Enide"
23. Le Doctrine Query Language (DQL)
24. Doctrine Query Language Notre meilleur allié, notre pire ennemi… $query = $em->createQuery('SELECT p FROM App\Entity\Player p JOIN p.scores s WHERE s.points > 11'); $players = $query->getResult();
25. Doctrine Query Language Notre meilleur allié, notre pire ennemi… $qb = $em->createQueryBuilder(); $players = $qb ->addSelect('p') ->from('App\Entity\Player', 'p') ->join('p.scores', 's') ->where('s.points > 11') ->getQuery() ->getResult();
26. Doctrine Query Language Notre meilleur allié, notre pire ennemi… ->getQuery() $qb = $em->createQueryBuilder(); $players = $qb ->addSelect('p') ->from('App\Entity\Player', 'p') ->join('p.scores', 's') ->where('s.points > 11') ->getResult();
27. Doctrine Query Language Notre meilleur allié, notre pire ennemi… $qb = $em->createQueryBuilder(); $players = $qb ->addSelect('p') ->from('App\Entity\Player', 'p') ->join('p.scores', 's') ->where('s.points > 11') ->getQuery() ->getResult();
28. Doctrine Query Language Notre meilleur allié, notre pire ennemi… $qb = $em->createQueryBuilder(); $avgPoints = $qb ->addSelect('AVG(s.points)') ->from('App\Entity\Player', 'p') ->join('p.scores', 's') ->where('s.points > 11') ->getQuery() ->getSingleScalarResult();
29. Doctrine Query Language Notre meilleur allié, notre pire ennemi… ->addSelect('AVG(s.points)') $qb = $em->createQueryBuilder(); $avgPoints = $qb ->from('App\Entity\Player', 'p') ->join('p.scores', 's') ->where('s.points > 11') ->getQuery() ->getSingleScalarResult();
30. Doctrine Query Language Notre meilleur allié, notre pire ennemi… $qb = $em->createQueryBuilder(); $avgPoints = $qb ->addSelect('AVG(s.points)') ->from('App\Entity\Player', 'p') ->join('p.scores', 's') ->where('s.points > 11') ->getQuery() ->getSingleScalarResult();
31. Cas d’usage Syntaxe compatible avec le DQL (SELECT, UPDATE ou DELETE) Limité aux capacités du DQL (fonctions ou opérateurs disponibles par exemple)
32. Les fonctions DQL personnalisées
33. Choisir un joueur au hasard
34. Choisir un joueur au hasard RANDOM() : random double-precision floating point number in the range 0.0 <= x < 1.0
35. Choisir un joueur au hasard RANDOM() : random double-precision floating point number in the range 0.0 <= x < 1.0 SELECT * FROM player ORDER BY RANDOM() LIMIT 1;
36. namespace App\Doctrine; use Doctrine\ORM\Query\AST\Functions\FunctionNode; use Doctrine\ORM\Query\Lexer; final class Random extends FunctionNode { public function parse(\Doctrine\ORM\Query\Parser $parser): void { $parser->match(Lexer::T_IDENTIFIER); $parser->match(Lexer::T_OPEN_PARENTHESIS); $parser->match(Lexer::T_CLOSE_PARENTHESIS); } public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker): string { return 'RANDOM()'; } }
37. # config/packages/doctrine.yaml doctrine: # ... orm: dql: numeric_functions: rand: App\Doctrine\Random
38. $user = $qb ->addSelect('p') ->from('App\Entity\Player', 'p') ->orderBy('RAND()') ->setMaxResults(1) ->getQuery() ->getOneOrNullResult();
39. Cas d’usage Syntaxe compatible avec le DQL (SELECT, UPDATE ou DELETE) Fonctions SQL "simple" Voir aussi https://github.com/beberlei/DoctrineExtensions https://github.com/oroinc/doctrine-extensions https://github.com/opsway/doctrine-dbal-postgresql https://github.com/martin-georgiev/postgresql-for-doctrine https://github.com/ScientaNL/DoctrineJsonFunctions …
40. Utiliser la connexion DBAL
41. SELECT p.name, COUNT(*) as nb_games, AVG(s.points) as avg_points FROM player p JOIN score s ON p.id = s.player_id GROUP BY p.name ORDER BY nb_games DESC
42. $em->getConnection()->fetchAllAssociative(<<<SQL SELECT p.name, COUNT(*) as nb_games, AVG(s.points) as avg_points FROM player p JOIN score s ON p.id = s.player_id GROUP BY p.name ORDER BY nb_games DESC SQL );
43. array:9 [ 0 => array:3 [ "name" => "Pablo" "nb_games" => 2300 "avg_points" => "9.61" ] 1 => array:3 [ "name" => "JD" "nb_games" => 1641 "avg_points" => "10.214503351614869" ] 2 => array:3 [ "name" => "Tim" "nb_games" => 1085 "avg_points" => "9.6387096774193548" ] ....
44. On peut faire la même en DQL Mais comme avant, on est limité par ce que permet de faire DQL…
45. public function getStats(): array { return $this->createQueryBuilder('score') ->select('player.name') ->addSelect('COUNT(score) as numberOfGames') ->addSelect('COUNT(score.points) as averagePoints') ->leftJoin('score.player', 'player') ->groupBy('player.name') ->orderBy('numberOfGames', 'DESC') ->getQuery() ->getResult(); }
46. array:9 [ 0 => array:3 [ "name" => "Pablo" "numberOfGames" => 2300 "averagePoints" => "9.61" ] 1 => array:3 [ "name" => "JD" "numberOfGames" => 1641 "averagePoints" => "10.214503351614869" ] 2 => array:3 [ "name" => "Tim" "numberOfGames" => 1085 "averagePoints" => "9.6387096774193548" ] ....
47. Cas d’usage Syntaxe incompatible avec le DQL Volonté de ne pas hydrater des objets (performance) Besoin de construire la requête soi-même (optimisation) 👎 Tableaux associatifs et typage insatisfaisant
48. Hydrater des DTO
49. namespace App\Model; class Stats { public function __construct( public string $name, public int $numberOfGames, public float $averagePoints ) {} }
50. public function getStats(): array { return $this->createQueryBuilder('score') ->select('NEW App\Model\Stats(player.name, COUNT(score), AVG(score.points))') ->leftJoin('score.player', 'player') ->groupBy('player.name') ->orderBy('COUNT(score)', 'DESC') ->getQuery() ->getResult(); }
51. ->select('NEW App\Model\Stats(player.name, COUNT(score), AVG(score.points))') public function getStats(): array { return $this->createQueryBuilder('score') ->leftJoin('score.player', 'player') ->groupBy('player.name') ->orderBy('COUNT(score)', 'DESC') ->getQuery() ->getResult(); }
52. public function getStats(): array { return $this->createQueryBuilder('score') ->select('NEW App\Model\Stats(player.name, COUNT(score), AVG(score.points))') ->leftJoin('score.player', 'player') ->groupBy('player.name') ->orderBy('COUNT(score)', 'DESC') ->getQuery() ->getResult(); }
53. array:9 [ 0 => App\Model\Stats^ {#800 +name: "Pablo" +numberOfGames: 2300 +averagePoints: 9.61 } 1 => App\Model\Stats^ {#771 +name: "JD" +numberOfGames: 1641 +averagePoints: 10.214503351614869 } 2 => App\Model\Stats^ {#508 +name: "Tim" +numberOfGames: 1085 +averagePoints: 9.6387096774193548 } ....
54. Cas d’usage Uniquement des scalaires dans le constructeur Pas d’entités Pas de DTO imbriqués Limité à ce qu’on peut faire en DQL, encore…
55. Le DQL c’est souvent cool Mais parfois c’est meh…
56. Requêter des vues
57. SELECT p.name, COUNT(*) as nb_games, AVG(s.points) as avg_points FROM player p JOIN score s ON p.id = s.player_id GROUP BY p.name ORDER BY nb_games;
58. CREATE VIEW stats AS SELECT p.name, COUNT(*) as nb_games, AVG(s.points) as avg_points FROM player p JOIN score s ON p.id = s.player_id GROUP BY p.name ORDER BY nb_games; SELECT * FROM stats;
59. #[ORM\Entity(repositoryClass: StatsRepository::class, readOnly: true)] #[ORM\Table(name: 'stats')] class Stats { #[ORM\Id] #[ORM\Column(length: 255)] public string $name; #[ORM\Column(name: 'nb_games')] public int $numberOfGames; #[ORM\Column(name: 'avg_points')] public float $averagePoints; }
60. #[ORM\Entity(repositoryClass: StatsRepository::class, readOnly: true)] #[ORM\Table(name: 'stats')] class Stats { #[ORM\Id] #[ORM\Column(length: 255)] public string $name; #[ORM\Column(name: 'nb_games')] public int $numberOfGames; #[ORM\Column(name: 'avg_points')] public float $averagePoints; }
61. #[ORM\Column(length: 255)] public string $name; #[ORM\Entity(repositoryClass: StatsRepository::class, readOnly: true)] #[ORM\Table(name: 'stats')] class Stats { #[ORM\Id] #[ORM\Column(name: 'nb_games')] public int $numberOfGames; #[ORM\Column(name: 'avg_points')] public float $averagePoints; }
62. #[ORM\Column(name: 'nb_games')] public int $numberOfGames; #[ORM\Column(name: 'avg_points')] public float $averagePoints; #[ORM\Entity(repositoryClass: StatsRepository::class, readOnly: true)] #[ORM\Table(name: 'stats')] class Stats { #[ORM\Id] #[ORM\Column(length: 255)] public string $name; }
63. #[ORM\Entity(repositoryClass: StatsRepository::class, readOnly: true)] #[ORM\Table(name: 'stats')] class Stats { #[ORM\Id] #[ORM\Column(length: 255)] public string $name; #[ORM\Column(name: 'nb_games')] public int $numberOfGames; #[ORM\Column(name: 'avg_points')] public float $averagePoints; }
64. ^ array:10 [ 0 => App\Entity\Stats^ {#526 +name: "Pablo" +numberOfGames: 2300 +averagePoints: 9.61 } 1 => App\Entity\Stats^ {#528 +name: "JD" +numberOfGames: 1641 +averagePoints: 10.214503351614 } 2 => App\Entity\Stats^ {#529 +name: "Tim" +numberOfGames: 1085 +averagePoints: 9.63870967741935 } ....
65. CREATE VIEW stats AS SELECT p.name, COUNT(*) as nb_games, AVG(s.points) as avg_points FROM player p JOIN score s ON p.id = s.player_id GROUP BY p.name ORDER BY nb_games; SELECT * FROM stats;
66. CREATE MATERIALIZED VIEW stats AS SELECT p.name, COUNT(*) as nb_games, AVG(s.points) as avg_points FROM player p JOIN score s ON p.id = s.player_id GROUP BY p.name ORDER BY nb_games; SELECT * FROM stats;
67. CREATE MATERIALIZED VIEW stats AS SELECT p.name, COUNT(*) as nb_games, AVG(s.points) as avg_points FROM player p JOIN score s ON p.id = s.player_id GROUP BY p.name ORDER BY nb_games; SELECT * FROM stats;
68. SELECT p.name, COUNT(*) as nb_games, AVG(s.points) as avg_points CREATE MATERIALIZED VIEW stats AS FROM player p JOIN score s ON p.id = s.player_id GROUP BY p.name ORDER BY nb_games; SELECT * FROM stats;
69. CREATE MATERIALIZED VIEW stats AS SELECT p.name, COUNT(*) as nb_games, AVG(s.points) as avg_points FROM player p JOIN score s ON p.id = s.player_id GROUP BY p.name ORDER BY nb_games; SELECT * FROM stats;
70. SELECT * FROM stats; INSERT INTO score ...; SELECT * FROM stats; <-- même résultat REFRESH MATERIALIZED VIEW stats; SELECT * FROM stats; <-- nouveau résultat
71. SELECT * FROM stats; INSERT INTO score ...; SELECT * FROM stats; <-- même résultat REFRESH MATERIALIZED VIEW stats; SELECT * FROM stats; <-- nouveau résultat
72. INSERT INTO score ...; SELECT * FROM stats; SELECT * FROM stats; <-- même résultat REFRESH MATERIALIZED VIEW stats; SELECT * FROM stats; <-- nouveau résultat
73. SELECT * FROM stats; <-- même résultat SELECT * FROM stats; INSERT INTO score ...; REFRESH MATERIALIZED VIEW stats; SELECT * FROM stats; <-- nouveau résultat
74. REFRESH MATERIALIZED VIEW stats; SELECT * FROM stats; INSERT INTO score ...; SELECT * FROM stats; <-- même résultat SELECT * FROM stats; <-- nouveau résultat
75. SELECT * FROM stats; <-- nouveau résultat SELECT * FROM stats; INSERT INTO score ...; SELECT * FROM stats; <-- même résultat REFRESH MATERIALIZED VIEW stats;
76. SELECT * FROM stats; INSERT INTO score ...; SELECT * FROM stats; <-- même résultat REFRESH MATERIALIZED VIEW stats; SELECT * FROM stats; <-- nouveau résultat
77. $em->getConnection()->executeStatement('REFRESH MATERIALIZED VIEW stats');
78. CREATE VIEW stats AS SELECT p.name, COUNT(s.id) as nb_games, AVG(s.points) as avg_points FROM player p LEFT JOIN score s ON p.id = s.player_id GROUP BY p.name ORDER BY nb_games;
79. CREATE VIEW stats AS SELECT p.id, COUNT(s.id) as nb_games, AVG(s.points) as avg_points FROM player p LEFT JOIN score s ON p.id = s.player_id GROUP BY p.id ORDER BY nb_games;
80. #[ORM\Entity(repositoryClass: StatsRepository::class, readOnly: true)] #[ORM\Table(name: 'stats')] class Stats { #[ORM\Id] #[ORM\OneToOne(targetEntity: Player::class, fetch: 'EAGER')] #[ORM\JoinColumn(name: 'id')] public Player $player; #[ORM\Column(name: 'nb_games')] public int $numberOfGames; #[ORM\Column(name: 'avg_points')] public float $averagePoints; }
81. #[ORM\OneToOne(targetEntity: Player::class, fetch: 'EAGER')] #[ORM\JoinColumn(name: 'id')] public Player $player; #[ORM\Entity(repositoryClass: StatsRepository::class, readOnly: true)] #[ORM\Table(name: 'stats')] class Stats { #[ORM\Id] #[ORM\Column(name: 'nb_games')] public int $numberOfGames; #[ORM\Column(name: 'avg_points')] public float $averagePoints; }
82. #[ORM\Entity(repositoryClass: StatsRepository::class, readOnly: true)] #[ORM\Table(name: 'stats')] class Stats { #[ORM\Id] #[ORM\OneToOne(targetEntity: Player::class, fetch: 'EAGER')] #[ORM\JoinColumn(name: 'id')] public Player $player; #[ORM\Column(name: 'nb_games')] public int $numberOfGames; #[ORM\Column(name: 'avg_points')] public float $averagePoints; }
83. ^ array:11 [ 0 => App\Entity\Stats^ {#537 +player: App\Entity\Player^ {#535 -name: "Pablo" -currentPlayerSeason: ? ?App\Entity\PlayerSeason -id: 5 } +numberOfGames: 2300 +averagePoints: 9.61 } ....
84. Ne pas oublier de créer la vue dans une migration Doctrine Ne pas oublier de filtrer le nom de la vue pour éviter les conflits avec Doctrine migration, schema:validate , etc dbal: schema_filter: ~^(?!stats)~
85. Cas d’usage Syntaxe compatible avec les vues SQL (lecture) Cette vue se comporte comme une entité ✔️ Hydratation ✔️ Relations avec d’autres entités (LAZY ou EAGER) ✔️ Possible de la requêter en DQL (pour filtrer le résultat par exemple) ❌ Pas de modifications (read-only)
86. NativeQuery et ResultSetMapping
87. WITH first_player AS ( SELECT player.id, player.current_player_season_id FROM player JOIN player_season on player.current_player_season_id = player_season.id ORDER BY player_season.elo DESC LIMIT 1 ) SELECT DISTINCT p.name FROM player p JOIN score s1 ON p.id = s1.player_id JOIN game g ON s1.game_id = g.id JOIN score s2 ON g.id = s2.game_id AND s1.points > s2.points JOIN first_player ON first_player.id = s2.player_id WHERE g.season_id = 4
88. WITH first_player AS ( SELECT player.id, player.current_player_season_id FROM player JOIN player_season on player.current_player_season_id = player_season.id ORDER BY player_season.elo DESC LIMIT 1 ) SELECT DISTINCT p.name FROM player p JOIN score s1 ON p.id = s1.player_id JOIN game g ON s1.game_id = g.id JOIN score s2 ON g.id = s2.game_id AND s1.points > s2.points JOIN first_player ON first_player.id = s2.player_id WHERE g.season_id = 4
89. SELECT DISTINCT p.name FROM player p JOIN score s1 ON p.id = s1.player_id JOIN game g ON s1.game_id = g.id JOIN score s2 ON g.id = s2.game_id AND s1.points > s2.points JOIN first_player ON first_player.id = s2.player_id WHERE g.season_id = 4 WITH first_player AS ( SELECT player.id, player.current_player_season_id FROM player JOIN player_season on player.current_player_season_id = player_season.id ORDER BY player_season.elo DESC LIMIT 1 )
90. JOIN score s2 ON g.id = s2.game_id AND s1.points > s2.points WITH first_player AS ( SELECT player.id, player.current_player_season_id FROM player JOIN player_season on player.current_player_season_id = player_season.id ORDER BY player_season.elo DESC LIMIT 1 ) SELECT DISTINCT p.name FROM player p JOIN score s1 ON p.id = s1.player_id JOIN game g ON s1.game_id = g.id JOIN first_player ON first_player.id = s2.player_id WHERE g.season_id = 4
91. JOIN score s2 ON g.id = s2.game_id AND s1.points > s2.points JOIN first_player ON first_player.id = s2.player_id WITH first_player AS ( SELECT player.id, player.current_player_season_id FROM player JOIN player_season on player.current_player_season_id = player_season.id ORDER BY player_season.elo DESC LIMIT 1 ) SELECT DISTINCT p.name FROM player p JOIN score s1 ON p.id = s1.player_id JOIN game g ON s1.game_id = g.id WHERE g.season_id = 4
92. WITH first_player AS ( SELECT player.id, player.current_player_season_id FROM player JOIN player_season on player.current_player_season_id = player_season.id ORDER BY player_season.elo DESC LIMIT 1 ) SELECT DISTINCT p.name FROM player p JOIN score s1 ON p.id = s1.player_id JOIN game g ON s1.game_id = g.id JOIN score s2 ON g.id = s2.game_id AND s1.points > s2.points JOIN first_player ON first_player.id = s2.player_id WHERE g.season_id = 4
93. public function getFirstPlayerWinners(): array { $rsmBuilder = new ResultSetMappingBuilder($this->_em); $rsmBuilder->addRootEntityFromClassMetadata(Player::class, 'p'); $selectClause = $rsmBuilder->generateSelectClause(['p']); $query = $this->_em->createNativeQuery(<<<SQL WITH first_player AS ( SELECT player.id, player.current_player_season_id FROM player JOIN player_season on player.current_player_season_id = player_season.id ORDER BY player_season.elo DESC LIMIT 1 ) SELECT $selectClause FROM player p JOIN score s1 ON p.id = s1.player_id JOIN game g ON s1.game_id = g.id JOIN score s2 ON g.id = s2.game_id AND s1.points > s2.points JOIN first_player ON first_player.id = s2.player_id WHERE g.season_id = 2 SQL, $rsmBuilder); return $query->getResult(); }
94. $query = $this->_em->createNativeQuery(<<<SQL WITH first_player AS ( SELECT player.id, player.current_player_season_id FROM player JOIN player_season on player.current_player_season_id = player_season.id ORDER BY player_season.elo DESC LIMIT 1 ) SELECT $selectClause FROM player p JOIN score s1 ON p.id = s1.player_id JOIN game g ON s1.game_id = g.id JOIN score s2 ON g.id = s2.game_id AND s1.points > s2.points JOIN first_player ON first_player.id = s2.player_id WHERE g.season_id = 2 SQL, $rsmBuilder); public function getFirstPlayerWinners(): array { $rsmBuilder = new ResultSetMappingBuilder($this->_em); $rsmBuilder->addRootEntityFromClassMetadata(Player::class, 'p'); $selectClause = $rsmBuilder->generateSelectClause(['p']); return $query->getResult(); }
95. $rsmBuilder = new ResultSetMappingBuilder($this->_em); $rsmBuilder->addRootEntityFromClassMetadata(Player::class, 'p'); SQL, $rsmBuilder); public function getFirstPlayerWinners(): array { $selectClause = $rsmBuilder->generateSelectClause(['p']); $query = $this->_em->createNativeQuery(<<<SQL WITH first_player AS ( SELECT player.id, player.current_player_season_id FROM player JOIN player_season on player.current_player_season_id = player_season.id ORDER BY player_season.elo DESC LIMIT 1 ) SELECT $selectClause FROM player p JOIN score s1 ON p.id = s1.player_id JOIN game g ON s1.game_id = g.id JOIN score s2 ON g.id = s2.game_id AND s1.points > s2.points JOIN first_player ON first_player.id = s2.player_id WHERE g.season_id = 2 return $query->getResult(); }
96. $selectClause = $rsmBuilder->generateSelectClause(['p']); SELECT $selectClause public function getFirstPlayerWinners(): array { $rsmBuilder = new ResultSetMappingBuilder($this->_em); $rsmBuilder->addRootEntityFromClassMetadata(Player::class, 'p'); $query = $this->_em->createNativeQuery(<<<SQL WITH first_player AS ( SELECT player.id, player.current_player_season_id FROM player JOIN player_season on player.current_player_season_id = player_season.id ORDER BY player_season.elo DESC LIMIT 1 ) FROM player p JOIN score s1 ON p.id = s1.player_id JOIN game g ON s1.game_id = g.id JOIN score s2 ON g.id = s2.game_id AND s1.points > s2.points JOIN first_player ON first_player.id = s2.player_id WHERE g.season_id = 2 SQL, $rsmBuilder); return $query->getResult(); }
97. public function getFirstPlayerWinners(): array { $rsmBuilder = new ResultSetMappingBuilder($this->_em); $rsmBuilder->addRootEntityFromClassMetadata(Player::class, 'p'); $selectClause = $rsmBuilder->generateSelectClause(['p']); $query = $this->_em->createNativeQuery(<<<SQL WITH first_player AS ( SELECT player.id, player.current_player_season_id FROM player JOIN player_season on player.current_player_season_id = player_season.id ORDER BY player_season.elo DESC LIMIT 1 ) SELECT $selectClause FROM player p JOIN score s1 ON p.id = s1.player_id JOIN game g ON s1.game_id = g.id JOIN score s2 ON g.id = s2.game_id AND s1.points > s2.points JOIN first_player ON first_player.id = s2.player_id WHERE g.season_id = 2 SQL, $rsmBuilder); return $query->getResult(); }
98. array:8 [ 0 => App\Entity\Player^ {#527 -name: "Mickey" -currentPlayerSeason: App\Entity\PlayerSeason^ {#568 -player: ? App\Entity\Player -season: ? App\Entity\Season -elo: ? int -id: 7 } -id: 12 } ....
99. public function getLadder(Season $season) { $rsmBuilder = new ResultSetMappingBuilder($this->_em); $rsmBuilder->addRootEntityFromClassMetadata(PlayerSeason::class, 'ps'); $rsmBuilder->addScalarResult('rank', 1); $select = $rsmBuilder->generateSelectClause(['ps']); $query = $this->_em->createNativeQuery(<<<SQL SELECT $select, rank() OVER (ORDER BY elo DESC) FROM player_season ps WHERE ps.season_id = :season SQL, $rsmBuilder); $query->setParameter(':season', $season->getId()); return $query->getResult(); }
100. $rsmBuilder = new ResultSetMappingBuilder($this->_em); $rsmBuilder->addRootEntityFromClassMetadata(PlayerSeason::class, 'ps'); $rsmBuilder->addScalarResult('rank', 1); public function getLadder(Season $season) { $select = $rsmBuilder->generateSelectClause(['ps']); $query = $this->_em->createNativeQuery(<<<SQL SELECT $select, rank() OVER (ORDER BY elo DESC) FROM player_season ps WHERE ps.season_id = :season SQL, $rsmBuilder); $query->setParameter(':season', $season->getId()); return $query->getResult(); }
101. $query = $this->_em->createNativeQuery(<<<SQL SELECT $select, rank() OVER (ORDER BY elo DESC) FROM player_season ps WHERE ps.season_id = :season SQL, $rsmBuilder); $query->setParameter(':season', $season->getId()); public function getLadder(Season $season) { $rsmBuilder = new ResultSetMappingBuilder($this->_em); $rsmBuilder->addRootEntityFromClassMetadata(PlayerSeason::class, 'ps'); $rsmBuilder->addScalarResult('rank', 1); $select = $rsmBuilder->generateSelectClause(['ps']); return $query->getResult(); }
102. public function getLadder(Season $season) { $rsmBuilder = new ResultSetMappingBuilder($this->_em); $rsmBuilder->addRootEntityFromClassMetadata(PlayerSeason::class, 'ps'); $rsmBuilder->addScalarResult('rank', 1); $select = $rsmBuilder->generateSelectClause(['ps']); $query = $this->_em->createNativeQuery(<<<SQL SELECT $select, rank() OVER (ORDER BY elo DESC) FROM player_season ps WHERE ps.season_id = :season SQL, $rsmBuilder); $query->setParameter(':season', $season->getId()); return $query->getResult(); }
103. array:10 [ 0 => array:2 [ 0 => App\Entity\PlayerSeason^ {#609 -player: App\Entity\Player^ {#607 -name: "Dam" -currentPlayerSeason: App\Entity\PlayerSeason^ {#609} -id: 8 } -season: App\Entity\Season^ {#521 -number: 1 -createdAt: DateTimeImmutable @1498056655 {#519 date: 2017-06-21 14:50:55.0 UTC (+00:00) } -id: 2 } -elo: 1106 -id: 10 } 1 => 1 ] ....
104. 0 => App\Entity\PlayerSeason^ {#609 -player: App\Entity\Player^ {#607 -name: "Dam" -currentPlayerSeason: App\Entity\PlayerSeason^ {#609} -id: 8 } -season: App\Entity\Season^ {#521 -number: 1 -createdAt: DateTimeImmutable @1498056655 {#519 date: 2017-06-21 14:50:55.0 UTC (+00:00) } -id: 2 } -elo: 1106 -id: 10 } array:10 [ 0 => array:2 [ 1 => 1 ] ....
105. 1 => 1 array:10 [ 0 => array:2 [ 0 => App\Entity\PlayerSeason^ {#609 -player: App\Entity\Player^ {#607 -name: "Dam" -currentPlayerSeason: App\Entity\PlayerSeason^ {#609} -id: 8 } -season: App\Entity\Season^ {#521 -number: 1 -createdAt: DateTimeImmutable @1498056655 {#519 date: 2017-06-21 14:50:55.0 UTC (+00:00) } -id: 2 } -elo: 1106 -id: 10 } ] ....
106. array:10 [ 0 => array:2 [ 0 => App\Entity\PlayerSeason^ {#609 -player: App\Entity\Player^ {#607 -name: "Dam" -currentPlayerSeason: App\Entity\PlayerSeason^ {#609} -id: 8 } -season: App\Entity\Season^ {#521 -number: 1 -createdAt: DateTimeImmutable @1498056655 {#519 date: 2017-06-21 14:50:55.0 UTC (+00:00) } -id: 2 } -elo: 1106 -id: 10 } 1 => 1 ] ....
107. Cas d’usage SQL complexe (utile avec SELECT ou avec RETURNING ) Réhydrater des vraies entités Possible de mixer entités, scalaires et DTO Vraiment intéressant quand la requête est complexe mais que le résultat reste une entité Utilisé en interne par Doctrine pour mapper les résultats des requêtes SQL aux entités
108. Récapitulatif 1. Doctrine Query Language (DQL) 2. Fonctions DQL personnalisées 3. Utilisation de la connexion DBAL 4. Hydratation de DTO 5. Requêter des vues 6. NativeQuery et ResultSetMapping
109. Merci pour votre attention Des questions ?