CODEWITHSUNDEEP
zend-frameworkdoctrine-ormdoctrinedoctrine-1.2doctrine-query
DonOfDen
DonOfDen

Reputation: 4088

Doctrine Datetime and where condition

I am writing a Doctrine 2.3 Query and I am facing some issues:

The SQL Query which I am reflecting was:

SELECT * 
FROM  `entry` 
WHERE  `plate` LIKE  '%'
AND  `recognition_datetime` BETWEEN '2013-03-13 22:20:18'
AND  '2013-03-13 22:20:20';

I am getting the out put with the rows selected.

Doctrine which I am trying:

Opt 1:

$qry = $this->manager()->createQueryBuilder()
                    ->from($this->entity, 'e')
                    ->select('e');
$qry->where('e.plate like :plate');
$qry->setParameter('plate', $plate);
$qry->add('where', "e.datetime between '2013-03-13 22:20:18' and '2013-03-13 22:20:20'");
$qry->setMaxResults( $limit );

It out puts only the first where condition:

SELECT e FROM Myproject\Domain\result e WHERE e.plate like '%'

Opt 2:

$qry = $this->manager()->createQueryBuilder()
                    ->from($this->entity, 'e')
                    ->select('e');
$qry->where('e.plate like :plate');
$qry->setParameter('plate', $plate);
$qry->andWhere('e.datetime BETWEEN :monday AND :sunday')
   ->setParameter('monday', $fromdate->format('Y-m-d H:i:s'))
   ->setParameter('sunday', $todate->format('Y-m-d H:i:s'));

It prints only the second where as query. Can some one help me how to write multiple where/And/or condition?

Upvotes: 1

Views: 1052

Answers (1)

DonOfDen
DonOfDen

Reputation: 4088

After some Research I found few thing which solved my problems:

Query Generation:

//CREATING QUERY BUILDER
$qry = $this->manager()->createQueryBuilder()
        ->from('YOUR_ENTITY_HERE', 'e')
        ->select('e');

//LIKE 
$ex1 = $qry->expr()->like('e.user', "'".$user."'");

//BETWEEN 
$ex2 = $qry->expr()->between('e.datetime',"'".$datetime."'","'".$dateto."'");

//IN 
$ex3 = $qry->expr()->in('e.country', $country);


//ADDING ALL EXPRESSION TO ONE INBETWEEN EXPRESSION "AND" OPERATION
$Query = $qry->expr()->andX($ex1,$ex2,$ex3);

//ADDING ALL EXPRESSION TO ONE INBETWEEN EXPRESSION "OR" OPERATION
$Query = $qry->expr()->orX($ex1,$ex2,$ex3);

//ADDING TOTAL EXPRESSIONS TO WHERE
$qry->where($Query); 

//GENERATE QUERY
$qry->getQuery();

//FINAL OPERATIONS
//EXECUTE
$qry->execute();

//GET ARRAY OUT PUT
$qry->getArrayResult();

//GET DB OBJECT
$qry->getResult();

Other Methods for Providing IN Operations:

OTHER WAY TO USE IN OPERATION:

$country=array('Us','UK','IND','BE');

$exp = $this->manager()->getExpressionBuilder();
$qry = $this->manager()->createQueryBuilder()
        ->select('e')
        ->from('YOUR_ENTITY_HERE', 'e')
        ->add('where', $exp->in('e.country', $country));

Upvotes: 4

Related Questions