Doctrine et requête multicritères

**Shao17** · 13/04/2020, 16h06

Bonjour,

J'utilise PHP 7 et Doctrine 2.2.0.
Je souhaiterais obtenir le résultat d'une recherche multi-critères (WHERE ... LIKE ... OR ... LIKE ...) basée sur un tableau de mots-clés. La recherche s'exécute sur certains champs, dans plusieurs tables (mais pas toutes, ce n'est donc pas une recherche sur toute la base de données que je souhaite).

Actuellement, j'obtiens l'erreur :"

Exception is : Doctrine\ORM\Query\QueryException: Invalid parameter format, : given, but : or ? expected. in /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query/QueryException.php:72
Stack trace:
#0 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query/AST/InputParameter.php(46): Doctrine\ORM\Query\QueryException::invalidParameterFormat(':')
#1 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(2834): Doctrine\ORM\Query\AST\InputParameter->__construct(':')
#2 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(2226): Doctrine\ORM\Query\Parser->LikeExpression()
#3 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(2145): Doctrine\ORM\Query\Parser->SimpleConditionalExpression()
#4 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(2121): Doctrine\ORM\Query\Parser->ConditionalPrimary()
#5 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(2089): Doctrine\ORM\Query\Parser->ConditionalFactor()
#6 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(2064): Doctrine\ORM\Query\Parser->ConditionalTerm()
#7 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(1212): Doctrine\ORM\Query\Parser->ConditionalExpression()
#8 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(760): Doctrine\ORM\Query\Parser->WhereClause()
#9 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(727): Doctrine\ORM\Query\Parser->SelectStatement()
#10 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(213): Doctrine\ORM\Query\Parser->QueryLanguage()
#11 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(288): Doctrine\ORM\Query\Parser->getAST()
#12 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query.php(230): Doctrine\ORM\Query\Parser->parse()
#13 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query.php(241): Doctrine\ORM\Query->_parse()
#14 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/AbstractQuery.php(595): Doctrine\ORM\Query->_doExecute()
#15 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/AbstractQuery.php(420): Doctrine\ORM\AbstractQuery->execute(Array, 1)
#16 /var/www/devel.stephane-herve-art.local/models/dao/AbstractDao.class.php(69): Doctrine\ORM\AbstractQuery->getResult()
#17 /var/www/devel.stephane-herve-art.local/models/dao/SearchDao.class.php(105): AbstractDao->executeQuery('SELECT c.id,c.r...')
#18 /var/www/devel.stephane-herve-art.local/models/dao/SearchDao.class.php(110): SearchDao->setSearchResults()
#19 /var/www/devel.stephane-herve-art.local/controllers/FrontController.class.php(223): SearchDao->setResult()
#20 /var/www/devel.stephane-herve-art.local/controllers/FrontController.class.php(63): FrontController->setContent()
#21 /var/www/devel.stephane-herve-art.local/index.php(60): FrontController->__construct()
#22 {main}

Fatal error: Uncaught Doctrine\DBAL\ConnectionException: There is no active transaction. in /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/DBAL/ConnectionException.php:42 Stack trace: #0 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/DBAL/Connection.php(947): Doctrine\DBAL\ConnectionException::noActiveTransaction() #1 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/EntityManager.php(251): Doctrine\DBAL\Connection->rollback() #2 /var/www/devel.stephane-herve-art.local/models/dao/AbstractDao.class.php(71): Doctrine\ORM\EntityManager->rollback() #3 /var/www/devel.stephane-herve-art.local/models/dao/SearchDao.class.php(105): AbstractDao->executeQuery('SELECT c.id,c.r...') #4 /var/www/devel.stephane-herve-art.local/models/dao/SearchDao.class.php(110): SearchDao->setSearchResults() #5 /var/www/devel.stephane-herve-art.local/controllers/FrontController.class.php(223): SearchDao->setResult() #6 /var/www/devel.stephane-herve-art.local/controllers/FrontController.cla in /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/DBAL/ConnectionException.php on line 42

Pour l'instant, je teste les critères de recherche sur plusieurs champs d'une seule table car je me dis que traiter tout de suite avec plusieurs tables ça va augmenter mes difficultés.

La requête passée avec mon code

SELECT c.id,c.ref,c.title,c.filename,c.year,c.publicationDate,c.modificationDate,c.expiryDate FROM shart\models\dto\ArtworkDto c WHERE c.title LIKE :"David Bowie" OR c.title LIKE :"Spider-man" OR c.title LIKE :"Groot" OR c.title LIKE :"Wonder woman" OR c.title LIKE :"Green Arrow" ' ORDER BY c.publicationDate DESC

Voici le code utilisé :

FrontController.class.php

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
// Getting the array of keywords
$this->keywords = $this->searchServices->getArrayOfKeywords();
// Creation of the search queries with the keywords
$this->searchDao->setQueries($this->keywords, $this->artworkDto);
$this->searchQuery = $this->searchDao->getQuery();
$this->searchDao->setResult();
$this->searchResults = $this->searchDao->getResult();
 
echo '<pre>';
    var_dump($this->searchResults);
echo '</pre>';
exit;

SearchDao.class.php

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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
 
class SearchDao extends AbstractDao
{
    private $searchString;
    private $arrayOfKeywords;
    private $query;
    private $result;
 
    /**
     * constructor
     */
    public function __construct () {}
    private function setQuery ($keywords, $table, $fields)
    {
        // Creation of the criteria
        $criteria = "";
        for ($i=0;$i<count($keywords);$i++)
        {
            if ($i === 0) { $criteria .= "c.$fields LIKE :\"$keywords[$i]\""; }
            else { $criteria .= " OR c.$fields LIKE :\"$keywords[$i]\""; }
        }
 
        // definition of the current time
        $currentTimestamp = date ('Y-m-d H:i:s');
        // Ordering them from the later to the older
        $this->query .= 'SELECT c.id,c.ref,c.title,c.filename,c.year,c.publicationDate,c.modificationDate,c.expiryDate FROM shart\models\dto\ArtworkDto c'
                      . " WHERE $criteria '"
                      . " ORDER BY c.publicationDate DESC"
                      ;
    }
 
    /*
     * Creating the search queries
     * 
     * The search goes through the references, labels, titles, authors, descriptions and texts
     * in the DTOs
     * @param string $keywords the needle to find
     * @param DTO $dto the DTO haystack to search in
     * @return void
     */
    public function setQueries ($keywords, $dto)
    {
        // Searching in ...
        // ... artwork
        $this->setQuery($keywords, 'shart\models\dto\ArtworkDto', 'title');
    }
 
    /*
     * Setting the results of the search query
     */
    public function setResult ()
    {
        echo $this->query;
        $this->result = $this->executeQuery ($this->query);
    }
 
    /*
     * Getting the search query
     */
    public function getQuery ()
    {
        return $this->query;
    }
 
 
 
    /*
     * Getting the result of the search
     */
    public function getResult ()
    {
        return $this->result;
    }
}

AbstractDao.class.php

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
34
35
36
37
38
39
 
abstract class AbstractDao implements DaoInterface
{
    protected $entityManager;
 
    /**
    ** Create the entity manager
    ** @param void
    ** @return void
    */
    public function createEntityManager ()
    {
        $this->entityManager = new Bootstrap();
        $this->entityManager = $this->entityManager->createEntityManager();
    }
 
    /**
    ** Executing a query to the DB
    ** @param string $dql the SQL query
    ** @return array of Dto $dto - NULL if failure
    */
    public function executeQuery ($dql, $max=5000)
    {
        $this->createEntityManager();
 
        // getting the data from the DB
        try
        {
            $query = $this->entityManager->createQuery ($dql);
            $query->setMaxResults($max);
            $dto = $query->getResult();
    	}
        catch (Exception $e) { echo '<pre>Exception is : '.$e.'</pre>'; $this->entityManager->rollback(); $dto = null; }
 
        $this->entityManager->close();
 
        return $dto;
    }
}

Voici mes questions :
- Que dois-je corriger pour que ça fonctionne ?
- Que puis-je changer pour améliorer le code ?

**Shao17** · 13/04/2020, 16h15

J'ai oublié d'indiquer qu'initialement j'avais essayé avec le "WHERE c.title LIKE %...%" sans plus de succès.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
for ($i=0;$i<count($keywords);$i++)
{
    if ($i === 0) { $criteria .= "c.$fields LIKE \"%$keywords[$i]\"%"; }
    else { $criteria .= " OR c.$fields LIKE \"%$keywords[$i]\"%"; }
}

générant la requête

SELECT c.id,c.ref,c.title,c.filename,c.year,c.publicationDate,c.modificationDate,c.expiryDate FROM shart\models\dto\ArtworkDto c WHERE c.title LIKE "%David Bowie"% OR c.title LIKE "%Spider-man"% OR c.title LIKE "%Groot"% OR c.title LIKE "%Wonder woman"% OR c.title LIKE "%Green Arrow"% ' ORDER BY c.publicationDate DESC

donnait l'erreur

Exception is : Doctrine\ORM\Query\QueryException: [Syntax Error] line 0, col 148: Error: Expected Doctrine\ORM\Query\Lexer::T_STRING, got '"' in /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query/QueryException.php:42
Stack trace:
#0 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(380): Doctrine\ORM\Query\QueryException::syntaxError('line 0, col 148...')
#1 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(255): Doctrine\ORM\Query\Parser->syntaxError('Doctrine\\ORM\\Qu...')
#2 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(2836): Doctrine\ORM\Query\Parser->match(3)
#3 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(2226): Doctrine\ORM\Query\Parser->LikeExpression()
#4 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(2145): Doctrine\ORM\Query\Parser->SimpleConditionalExpression()
#5 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(2121): Doctrine\ORM\Query\Parser->ConditionalPrimary()
#6 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(2089): Doctrine\ORM\Query\Parser->ConditionalFactor()
#7 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(2064): Doctrine\ORM\Query\Parser->ConditionalTerm()
#8 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(1212): Doctrine\ORM\Query\Parser->ConditionalExpression()
#9 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(760): Doctrine\ORM\Query\Parser->WhereClause()
#10 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(727): Doctrine\ORM\Query\Parser->SelectStatement()
#11 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(213): Doctrine\ORM\Query\Parser->QueryLanguage()
#12 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query/Parser.php(288): Doctrine\ORM\Query\Parser->getAST()
#13 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query.php(230): Doctrine\ORM\Query\Parser->parse()
#14 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/Query.php(241): Doctrine\ORM\Query->_parse()
#15 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/AbstractQuery.php(595): Doctrine\ORM\Query->_doExecute()
#16 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/AbstractQuery.php(420): Doctrine\ORM\AbstractQuery->execute(Array, 1)
#17 /var/www/devel.stephane-herve-art.local/models/dao/AbstractDao.class.php(69): Doctrine\ORM\AbstractQuery->getResult()
#18 /var/www/devel.stephane-herve-art.local/models/dao/SearchDao.class.php(105): AbstractDao->executeQuery('SELECT c.id,c.r...')
#19 /var/www/devel.stephane-herve-art.local/models/dao/SearchDao.class.php(110): SearchDao->setSearchResults()
#20 /var/www/devel.stephane-herve-art.local/controllers/FrontController.class.php(223): SearchDao->setResult()
#21 /var/www/devel.stephane-herve-art.local/controllers/FrontController.class.php(63): FrontController->setContent()
#22 /var/www/devel.stephane-herve-art.local/index.php(60): FrontController->__construct()
#23 {main}

Fatal error: Uncaught Doctrine\DBAL\ConnectionException: There is no active transaction. in /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/DBAL/ConnectionException.php:42 Stack trace: #0 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/DBAL/Connection.php(947): Doctrine\DBAL\ConnectionException::noActiveTransaction() #1 /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/ORM/EntityManager.php(251): Doctrine\DBAL\Connection->rollback() #2 /var/www/devel.stephane-herve-art.local/models/dao/AbstractDao.class.php(71): Doctrine\ORM\EntityManager->rollback() #3 /var/www/devel.stephane-herve-art.local/models/dao/SearchDao.class.php(105): AbstractDao->executeQuery('SELECT c.id,c.r...') #4 /var/www/devel.stephane-herve-art.local/models/dao/SearchDao.class.php(110): SearchDao->setSearchResults() #5 /var/www/devel.stephane-herve-art.local/controllers/FrontController.class.php(223): SearchDao->setResult() #6 /var/www/devel.stephane-herve-art.local/controllers/FrontController.cla in /var/www/devel.stephane-herve-art.local/lib/doctrine-2.2.0/Doctrine/DBAL/ConnectionException.php on line 42

**Shao17** · 13/04/2020, 22h31

Bonsoir,

Une petite bêtise de ma part, j'ai utilisé le double quote pour encadrer mes mots-clés au lieu du simple quote. En reprenant avec les % tel que dans SQL, je n'ai donc plus

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
for ($i=0;$i<count($keywords);$i++)
{
    if ($i === 0) { $criteria .= "c.$fields LIKE \"%$keywords[$i]%\""; }
    else { $criteria .= " OR c.$fields LIKE \"%$keywords[$i]%\""; }
}

mais ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
for ($i=0;$i<count($keywords);$i++)
{
    if ($i === 0) { $criteria .= "c.$fields LIKE '%$keywords[$i]%'"; }
    else { $criteria .= " OR c.$fields LIKE '%$keywords[$i]%'"; }
}

Ca fonctionne comme attendu : je récupère bien ainsi la valeur des champs correspondants à la requête.

Doctrine et requête multicritères [PHP 7]

Langage PHP

Discussions similaires

Partager

Partager