[MySQL] Recherche par mots-clés et design patterns

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 :

Code:

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

Ce qui donne à l'affichage des chaînes de requête :

Code:

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%'

Au retour d'exécution, j'obtiens l'erreur suivante :

Code:

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

1ère question : Comment utiliser ce type de requête via Doctrine ?
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.