Doctrine 2 date in where-clause (querybuilder)

Question

I've the following problem: I want a where-clause that checks if a user is active AND if he has the right date.

A user contains the following:

• State
• Startdate
• Enddate

So, State should stay on 1, then he should look for state = 1 AND the current date is between the start and enddate. I've the following right now, and it works fine. But the start and enddate is not required. So it could be NULL. How can i get a query like:

SELECT * 
FROM user/entity/user 
WHERE 
  state = 1 
  AND (
    (startdate <= CURRENT_DATE AND enddate >= CURRENT_DATE) 
    OR startdate == NULL 
    OR enddate == NULL
  )

, so i get all my active users, and not only the temporary users.

I've set up the following code right now: Repository:

        public function searchUser($columns, $order_by, $order)
        {
            //Create a Querybuilder
            $qb = $this->_em->createQueryBuilder();
            
            //andx is used to build a WHERE clause like (expression 1 AND expression 2)
            $or = $qb->expr()->andx();
    
            //Select all from the User-Entity
            $qb->select('u')
               ->from('User\Entity\User', 'u');
            
            
            
            foreach($columns as $column => $name) 
            {                        
                if($column == 'state') 
                {
                    if($columns['state']['value'] == '1') {
                        $or = $this->betweenDate($qb, $or);
                        $or = $this->like($columns, $qb, $or);
                    } elseif($columns['state']['value'] == '2') {
                        $or = $this->like($columns, $qb, $or);
                    } elseif($columns['state']['value'] == '3') {
                        $or = $this->outOfDate($qb, $or);
                    }
                } else {
                    //Get a where clause from the like function
                    $or = $this->like($columns, $qb, $or);
                }
            }
            
            //Set the where-clause
            $qb->where($or);
    
            //When there is a order_by, set it with the given parameters
            if(isset($order_by)) {
                $qb->orderBy("u.$order_by", $order);
            }
             
            //Make the query
            $query = $qb->getQuery();
            
            /*echo('<pre>');
            print_r($query->getResult());
            echo('</pre>');*/
            
            //Return the result
            return $query->getResult();
     
        }
    
        public function betweenDate($qb, $or)
            {
                $or->add($qb->expr()->lte("u.startdate", ":currentDate"));
                $or->add($qb->expr()->gte("u.enddate", ":currentDate"));
                //$or->add($qb->expr()->orx($qb->expr()->eq("u.startdate", null)));
                $qb->setParameter('currentDate', new \DateTime('midnight'));
          
                return $or;
            }
        
    //This one works perfect
        public function like($columns, $qb, $or)
            {
                //Foreach column, get the value from the inputfield/dropdown and check if the value
                //is in the given label. 
                foreach($columns as $label=>$column){
                    $value = $column['value'];
                    $or->add($qb->expr()->like("u.$label", ":$label"));
                    $qb->setParameter($label, '%' . $value . '%');
                }
                
                return $or;
            }

I use this "usersearch" also for other fields. So it should pick all the data out of the database, only state is different because "out of date" is not in the database. So it has to check differently. Hope somebody can help.

Problem solved

    $startdate = $qb->expr()->gt("u.startdate", ":currentDate");
        $enddate = $qb->expr()->lt("u.enddate", ":currentDate");
        
        $or->add($qb->expr()->orX($startdate, $enddate));

Answer 1

That's how I would build the where clause:

    //CONDITION 1 -> STATE = 1
    $state = $qb->expr()->eq( 'state', ':state' );

    //CONDITION 2 -> startdate <= CURRENT_DATE AND enddate >= CURRENT_DATE
    $betweenDates = $qb->expr()->andX( 
        $qb->expr()->lte("u.startdate", ":currentDate"),
        $qb->expr()->gte("u.enddate", ":currentDate")
    );

    //CONDITION 3 -> startdate == NULL
    $startDateNull = $qb->expr()->isNull( 'startdate' );

    //CONDITION 4 -> enddate == NULL
    $endDateNull = $qb->expr()->isNull( 'enddate' );

    //CONDITION 5 -> <CONDITION 2> OR <CONDITION 3> OR <CONDITION 4>
    $dates = $qb->expr()->orX( $betweenDates, $startDateNull, $endDateNull );

    //CONDITION 6 -> <CONDITION 1> AND <CONDITION 5>
    $whereClause = $qb->expr()->andX( $state, $dates );