How to use BETWEEN in symfony2?

Question

I am working on a project using symfony2. I have a date_started and date_ended and I am planning to use BETWEEN instead of >= and <= . I just want to ask any idea on how should I implement it using symfony2? Since my work doesn't work. Here is my sample code:

Repository :

public function getAllTransaction($rNumber,$gNumber,$senderId,$receiverId,$docType,$direction,$startDate,$endDate,$page = 1, $limit = 10) {
         $em = $this->getEntityManager();
         $query = $em->createQuery(
           'SELECT partial a.{supplierTransactionDetailId, rNumber, pNumber, invNumber, anNumber, adNumber, amount}, 
            partial b.{supplierTransactionId, transactionDate, senderId, receiverId, isaNumber, gsNumber, fileName}, 
            partial c.{supplierDocTypeId, docType, direction} 
            FROM SupplierBundle:SupplierTransactionDetail a 
            JOIN a.supplierTransaction b 
            JOIN b.supplierDocType c
            WHERE (a.rNumber LIKE :rNumber OR a.pNumber LIKE :rNumber 
                  OR a.invNumber LIKE :rNumber 
                  OR a.anNumber LIKE :rNumber 
                  OR a.adNumber LIKE :rNumber )
            AND b.gsNumber LIKE :gsNumber
            AND b.senderId LIKE :senderId
            AND b.receiverId LIKE :receiverId
            AND c.docType LIKE :docType
            AND c.direction LIKE :direction
            AND b.transactionDate BETWEEN :startDate AND :endDate



            ORDER BY b.transactionDate desc'
          )
          ->setParameter('rNumber', "%$rNumber%")
          ->setParameter('gsNumber', "%$gsNumber%")
          ->setParameter('senderId', "%$senderId%")
          ->setParameter('receiverId' , "%$receiverId%")
          ->setParameter('docType',"%$docType%")
          ->setParameter('direction', "%$direction%")
          ->setParameter('startDate', "%$startDate%")
          ->setParameter('endDate', "%$endDate%")
          ->setFirstResult(($page - 1) *$limit )
          ->setMaxResults($limit);

         $paginator = new Paginator ($query, $fetchJoinCollection = false );
         $paginator->setUseOutputWalkers(false);

         return $paginator;
      }

This code display all the transaction with pagination and can also search, but then when I put the last part the transactionDate with Between it doesn't display nothing. .

Controller :

public function trackAction(Request $request,$page){
        $rNumber = $request->query->get('rNumber');
        $gsNumber = $request->query->get('gsNumber');
        $senderId = $request->query->get('senderId');
        $receiverId = $request->query->get('receiverId');
        $docType = $request->query->get('docType');
        $direction = $request->query->get('direction');
        $startDate = $request->query->get('startDate');
        $endDate = $request->query->get('endDate');

        $max_records = 10;
        $em = $this->getDoctrine()->getManager();
        $trans = $em->getRepository('SupplierBundle:SupplierTransactionDetail')->getAllTransaction($rNumber,$gsNumber,$senderId,$receiverId,$docType,$direction,$startDate,$endDate,$page , 10);

        $count = $trans->count();
        $pagination = array(
            'page' => $page,
            'route' => 'supplier_tracking',
            'route_params' => array()
        );

        if ($max_records > 0 ){
            $pagination['pages_count'] = max(ceil($count / $max_records), 1);
        }

        return $this->render('SupplierBundle:Supplier:tracking.html.twig',array('trans' => $trans, 'pagination' => $pagination));


    }

UPDATE

In my database transactionDate is formatted something like this :

2015-05-18 18:10:00 for it is datetime. and in my form ,,, startdate and enddate is input type of date.

Answer 1

Now that you have removed the LIKE for the BETWEEN syntax, as per @EdCottrell answer. I think you also need to change these 2 lines to remove the % used in the LIKE syntax.

->setParameter('startDate', "$startDate")
->setParameter('endDate', "$endDate")

Also do you actually need everything LIKE'd like that. It all seems a bit wooly. As most of those fields appear to be numbers I doubt that the LIKE syntax is actually required, although I am only guessing that they are numbers from their names.

Re your comment about numbers

Try this then, removing all the likes and passing the values as numbers and not strings.

public function getAllTransaction(
                                 $rNumber,$gNumber,$senderId,$receiverId,
                                 $docType,$direction,$startDate,$endDate,
                                 $page = 1, $limit = 10
                                ) 
{
     $em = $this->getEntityManager();
     $query = $em->createQuery(
       'SELECT    partial a.{supplierTransactionDetailId, rNumber, pNumber, invNumber, anNumber, adNumber, amount}, 
                  partial b.{supplierTransactionId, transactionDate, senderId, receiverId, isaNumber, gsNumber, fileName}, 
                  partial c.{supplierDocTypeId, docType, direction} 
        FROM SupplierBundle:SupplierTransactionDetail a 
           JOIN a.supplierTransaction b 
           JOIN b.supplierDocType c
        WHERE (  a.rNumber = :rNumber 
              OR a.pNumber = :rNumber 
              OR a.invNumber = :rNumber 
              OR a.anNumber = :rNumber 
              OR a.adNumber = :rNumber )
          AND b.gsNumber = :gsNumber
          AND b.senderId = :senderId
          AND b.receiverId = :receiverId
          AND c.docType = :docType
          AND c.direction = :direction
          AND b.transactionDate BETWEEN :startDate AND :endDate
        ORDER BY b.transactionDate desc'
      )
      ->setParameter('rNumber', $rNumber)
      ->setParameter('gsNumber', $gsNumber)
      ->setParameter('senderId', $senderId)
      ->setParameter('receiverId' , $receiverId)
      ->setParameter('docType', $docType)
      ->setParameter('direction', $direction)
      ->setParameter('startDate', $startDate)
      ->setParameter('endDate', $endDate)
      ->setFirstResult(($page - 1) *$limit )
      ->setMaxResults($limit);

     // add this debug code to check the generated query
     // you could try and run this using phpMyadmin or
     // whatever mysql tools you use. If taht gets errors
     // come back here an mod the generator

     echo $query->getSQL();

     $paginator = new Paginator ($query, $fetchJoinCollection = false );
     $paginator->setUseOutputWalkers(false);

     return $paginator;
  }

RE: I found this

This is the result of the echo $query->getSQL(); and is the query that QueryBuilder has generated from all your inputs.

I would copy/paste that into something like phpMyAdmin or whatever tool you use to look at your mysql database, replace the ? with the actual parameters that you were using and try and run it.

My guess is that because it is such a complex query you have created an impossible requirement and it is either failing in the compile phase or is just returning no results. You can then play with the query until you get it right and then change the PHP script accordingly.

SELECT e0_.supplier_transaction_detail_id AS supplier_transaction_detail_id0, 
       e0_.r_number AS r_number1, 
       e0_.p_number AS p_number2, 
       e0_.inv_number AS inv_number3, 
       e0_.an_number AS an_number4, 
       e0_.ad_number AS ad_number5, 
       e1_.supplier_transaction_id AS supplier_transaction_id7, 
       e1_.sender_id AS sender_id8, 
       e1_.receiver_id AS receiver_id9, 
       e1_.transaction_date AS transaction_date11,
       e1_.gs_number AS gs_number12,
       e2_.supplier_doc_type_id AS supplier_doc_type_id14,
       e2_.doc_type AS doc_type15, 
       e2_.direction AS direction16, 
       e0_.supplier_transaction_id AS supplier_transaction_id17, 
       e1_.supplier_doc_type_id AS supplier_doc_type_id18 
   FROM supplier_transaction_detail e0_ 
      INNER JOIN supplier_transaction e1_ ON e0_.supplier_transaction_id = e1_.supplier_transaction_id 
      INNER JOIN supplier_doc_type e2_ ON e1_.supplier_doc_type_id = e2_.supplier_doc_type_id 
   WHERE (e0_.r_number = ? OR 
          e0_.p_number = ? OR 
          e0_.inv_number = ? OR 
          e0_.an_number = ? OR 
          e0_.ad_number = ?)

      AND e1_.gs_number = ? 
      AND e1_.sender_id = ? 
      AND e1_.receiver_id = ? 
      AND e2_.doc_type = ? 
      AND e2_.direction = ? 
       OR e1_.transaction_date BETWEEN ? AND ? 
   ORDER BY e1_.transaction_date DESC LIMIT 10 OFFSET 0

Answer 2

Hey to setup correct query it have to look like this:

SELECT ..
FROM ..
WHERE (SELECT .. FROM ..) BETWEEN .. AND ..

I set up for you example based on your code. Remember to add more queries. So example for this query with sub query should look something similar to this:

$subQueryDQL = $qbv->select('a.parameter')
    ->from('SupplierBundle:SupplierTransactionDetail', 'a')
    ...
    ->getDQL();

$qb->andWhere($qb->expr()->between(
    sprintf('(%s)', $subQueryDQL),
    $filter->getStartDate()->getTimestamp(),
    $filter->getEndDate()->getTimestamp()
));

Your code would look something like: $em = $this->getEntityManager(); $qb = $em->createQueryBuilder();

$query = $qb->select('a.supplierTransactionDetailId, a.rNumber, a.pNumber, a.invNumber, a.anNumber, a.adNumber, a.amount,
b.supplierTransactionId, b.transactionDate, b.senderId, b.receiverId, b.isaNumber, b.gsNumber, b.fileName,
c.supplierDocTypeId, c.docType, c.direction}')
    ->from("SupplierBundle:SupplierTransactionDetail", 'a')
    ->innerJoin('a.supplierTransaction', 'b')
    ->innerJoin('b.supplierDocType', 'c')
    ->add('where', $qb->expr()->between(
        'b.transactionDate',
        ':startDate',
        ':endDate'
    )
    )
    ->andWhere(
        $qb->expr()->like('b.senderId', ':senderId')
    )
    ->setParameter('rNumber', "%$rNumber%")
    ->setParameter('gsNumber', "%$gsNumber%")
    ->setParameter('senderId', "%$senderId%")
    ->setParameter('receiverId', "%$receiverId%")
    ->setParameter('docType', "%$docType%")
    ->setParameter('direction', "%$direction%")
    ->setParameter('startDate', "%$startDate%")
    ->setParameter('endDate', "%$endDate%")
    ->setFirstResult(($page - 1) * $limit)
    ->setMaxResults($limit);

$paginator = new Paginator ($query, $fetchJoinCollection = false);
$paginator->setUseOutputWalkers(false);

return $paginator;

You will have to add more where by yourself.

Answer 3

You don't use LIKE in a BETWEEN clause; it's just BETWEEN x AND y:

SELECT a,b,c
FROM SupplierBundle:SupplierTransactionDetail a 
    JOIN a.supplierTransaction b 
    JOIN b.supplierDocType c
WHERE b.transactionDate 
    BETWEEN :startDate AND :endDate
ORDER BY b.transactionDate desc