Bonjour à tous,
Je suis en train d'implémenter une fonction de recherche par mots-clés sur un site web écrit en PHP/Doctrine/Mysql et utilisant les design patterns Controller/DTO/Factory/DAO/Service.
J'utilise une requête SELECT ... LIKE ... UNION ALL SELECT ... LIKE ... sur 4 tables que j'exécute dans une fonction executeQuery() de Doctrine :
Ce qui donne à l'affichage des chaînes de requête :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33 $query_01 = "SELECT c.idContenu, c.Menu_idMenu, c.nom_page, c.titre, c.illustration, c.illustration_alt, c.texte, " . "null AS c.idAccueilMedia, null AS c.titre, null AS ac.ccroche, null AS c.periode, null as c.lieu, null AS c.illustration, null AS c.illustration_alt, null AS c.texte, null AS c.date_publication, " . "null AS c.idAccueilTelechargements, null AS c.texte, null AS c.lien, null AS c.lien_titre, " . "null AS c.idActualites, null AS c.titre, null AS c.accroche, null AS c.periode, null AS c.lieu, null AS c.illustration, null AS c.illustration_alt, null AS c.texte, null AS c.date_publication " . "FROM Contenu c " . "WHERE c.titre OR c.illustration_alt OR c.texte " . "LIKE '%$needle%'"; $query_02 = "SELECT null AS m.idContenu, null AS m.Menu_idMenu, null AS m.nom_page, null AS m.titre, null AS m.illustration, null AS m.illustration_alt, null AS m.texte, " . "m.idAccueilMedia, m.titre, m.accroche, m.periode, m.lieu, m.illustration, m.illustration_alt, m.texte, m.date_publication, " . "null AS m.idAccueilTelechargements, null AS m.texte, null AS m.lien, null AS m.lien_titre, " . "null AS m.idActualites, null AS m.titre, null AS m.accroche, null AS m.periode, null AS m.lieu, null AS m.illustration, null AS m.illustration_alt, null AS m.texte, null AS m.date_publication " . "FROM Accueil_media m " . "WHERE (m.titre OR m.accroche OR m.periode OR m.lieu OR m.illustration_alt OR m.texte OR m.date_publication " . "LIKE '%$needle%') AND (`date_expiration` > '$currentTimestamp' AND `date_publication`< '$currentTimestamp')"; $query_03 = "SELECT null AS t.idContenu, null AS t.Menu_idMenu, null AS t.nom_page, null AS t.titre, null AS t.illustration, null AS t.illustration_alt, null AS t.texte, " . "null AS t.idAccueilMedia, null AS t.titre, null AS t.accroche, null AS t.periode, null AS t.lieu, null AS t.illustration, null AS t.illustration_alt, null AS t.texte, null AS t.date_publication, " . "t.idAccueilTelechargements, t.texte, t.lien, t.lien_titre, " . "null AS t.idActualites, null AS t.titre, null AS t.accroche, null AS t.periode, null AS t.lieu, null AS t.illustration, null AS t.illustration_alt, null AS t.texte, null AS t.date_publication " . "FROM Accueil_telechargements t " . "WHERE t.texte OR t.lien_titre " . "LIKE '%$needle%'"; $query_04 = "SELECT null AS a.idContenu, null AS a.Menu_idMenu, null AS a.nom_page, null AS a.titre, null AS a.illustration, null AS a.illustration_alt, null AS a.texte, " . "null AS a.idAccueilMedia, null AS a.titre, null AS a.accroche, null AS a.periode, null AS a.lieu, null AS a.illustration, null AS a.illustration_alt, null AS a.texte, null AS a.date_publication, " . "null AS a.idAccueilTelechargements, null AS a.texte, null AS a.lien, null AS a.lien_titre, " . "a.idActualites, a.titre, a.accroche, a.periode, a.lieu, a.illustration, a.illustration_alt, a.texte, a.date_publication " . "FROM Actualites a " . "WHERE (a.titre OR a.accroche OR a.periode OR a.lieu OR a.illustration_alt OR a.texte OR a.date_publication " . "LIKE '%$needle%') AND (`date_expiration` > '$currentTimestamp' AND `date_publication`< '$currentTimestamp')"; return $this->executeQuery ("$query_01 UNION ALL $query_02 UNION ALL $query_03 UNION ALL $query_04");
Au retour d'exécution, j'obtiens l'erreur suivante :
Code : Sélectionner tout - Visualiser dans une fenêtre à part SELECT c.idContenu, c.Menu_idMenu, c.nom_page, c.titre, c.illustration, c.illustration_alt, c.texte, null AS c.idAccueilMedia, null AS c.titre, null AS ac.ccroche, null AS c.periode, null as c.lieu, null AS c.illustration, null AS c.illustration_alt, null AS c.texte, null AS c.date_publication, null AS c.idAccueilTelechargements, null AS c.texte, null AS c.lien, null AS c.lien_titre, null AS c.idActualites, null AS c.titre, null AS c.accroche, null AS c.periode, null AS c.lieu, null AS c.illustration, null AS c.illustration_alt, null AS c.texte, null AS c.date_publication FROM Contenu c WHERE c.titre OR c.illustration_alt OR c.texte LIKE '%mot%'
1ère question : Comment utiliser ce type de requête via Doctrine ?
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 Exception is : exception 'Doctrine\ORM\Query\QueryException' with message '[Syntax Error] line 0, col 101: Error: Expected IdentificationVariable | ScalarExpression | AggregateExpression | FunctionDeclaration | PartialObjectExpression | "(" Subselect ")" | CaseExpression, got 'null'' in /var/www/upgrade/devel/lib/doctrine-2.2.0/Doctrine/ORM/Query/QueryException.php:42 Stack trace: #0 /var/www/upgrade/devel/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(380): Doctrine\ORM\Query\QueryException::syntaxError('line 0, col 101...') #1 /var/www/upgrade/devel/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(1941): Doctrine\ORM\Query\Parser->syntaxError('IdentificationV...', Array) #2 /var/www/upgrade/devel/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(1048): Doctrine\ORM\Query\Parser->SelectExpression() #3 /var/www/upgrade/devel/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(758): Doctrine\ORM\Query\Parser->SelectClause() #4 /var/www/upgrade/devel/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(727): Doctrine\ORM\Query\Parser->SelectStatement() #5 /var/www/upgrade/devel/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(213): Doctrine\ORM\Query\Parser->QueryLanguage() #6 /var/www/upgrade/devel/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(288): Doctrine\ORM\Query\Parser->getAST() #7 /var/www/upgrade/devel/lib/doctrine-2.2.0/Doctrine/ORM/Query.php(230): Doctrine\ORM\Query\Parser->parse() #8 /var/www/upgrade/devel/lib/doctrine-2.2.0/Doctrine/ORM/Query.php(241): Doctrine\ORM\Query->_parse() #9 /var/www/upgrade/devel/lib/doctrine-2.2.0/Doctrine/ORM/AbstractQuery.php(595): Doctrine\ORM\Query->_doExecute() #10 /var/www/upgrade/devel/lib/doctrine-2.2.0/Doctrine/ORM/AbstractQuery.php(420): Doctrine\ORM\AbstractQuery->execute(Array, 1) #11 /var/www/upgrade/devel/models/dao/AbstractDao.class.php(27): Doctrine\ORM\AbstractQuery->getResult() #12 /var/www/upgrade/devel/models/dao/SearchDao.class.php(100): AbstractDao->executeQuery('SELECT c.idCont...') #13 /var/www/upgrade/devel/controllers/FrontController.class.php(375): SearchDao->readInAll('mot') #14 /var/www/upgrade/devel/index.php(58): FrontController->setContent() #15 {main} Fatal error: Uncaught exception 'Doctrine\DBAL\ConnectionException' with message 'There is no active transaction.' in /var/www/upgrade/devel/lib/doctrine-2.2.0/Doctrine/DBAL/ConnectionException.php:42 Stack trace: #0 /var/www/upgrade/devel/lib/doctrine-2.2.0/Doctrine/DBAL/Connection.php(947): Doctrine\DBAL\ConnectionException::noActiveTransaction() #1 /var/www/upgrade/devel/lib/doctrine-2.2.0/Doctrine/ORM/EntityManager.php(251): Doctrine\DBAL\Connection->rollback() #2 /var/www/upgrade/devel/models/dao/AbstractDao.class.php(29): Doctrine\ORM\EntityManager->rollback() #3 /var/www/upgrade/devel/models/dao/SearchDao.class.php(100): AbstractDao->executeQuery('SELECT c.idCont...') #4 /var/www/upgrade/devel/controllers/FrontController.class.php(375): SearchDao->readInAll('mot') #5 /var/www/upgrade/devel/index.php(58): FrontController->setContent() #6 {main} thrown in /var/www/upgrade/devel/lib/doctrine-2.2.0/Doctrine/DBAL/ConnectionException.php on line 42
2ème question : Auriez-vous un schéma d'architecture simple de recherche par mots-clés multi-tables en design pattern ?
3ème question : Le résultat de la requête ainsi obtenu doit-il être stocké dans une DTO (alors qu'il n'est lié à aucune table existante) ou dans un autre type d'objet (couche service, autre pattern) ? J'aimerais respecter le plus possible les standards pour des questions de clarté, maintenabilité et évolutivitié du code.
Merci par avance de votre aide et de l'intérêt que vous portez à mes questions.
Partager