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 ?