how to manipulate dates in doctrine

Question

i have this date field (date_born) in a table in my bd, which is the date of birth of a person (type: date).

im using codeignater, and im also using doctrine.

the thing is that i want to count how many people are adults and how many are kids, so i built this:

public function countAdults(){
    $this->qb = $this->em->createQueryBuilder();
    $this->qb->select('count(u)')
            ->from($this->tbl_name, 'u')
            ->where(
                $this->qb->expr()->eq("u.date_born >'1995-01-01'")
                );

    $query = $this->qb->getQuery();
    return $query->getSingleScalarResult();
}

this is clearly, returning an error.

How can i rebuild this, in an optimus way, to count the people who was born before 1995? (now 18)

Answer 1

I recommend the following

• Use the lte expression to compare the dates instead of evaluating with eq
• Provide an instance of PHP's DateTime as Doctrine will happily accept that, and in general prefers objects over anything else.
• To make this instance of DateTime automatically calculate when 18 years ago was, so that you don't have to change it each year.
• To bind this object with the setParameters() method.

Method:

public function countAdults()
{
    $eighteenYearsAgo = new \DateTime();
    $eighteenYearsAgo->modify('-18 years');

    $this->qb = $this->em->createQueryBuilder();
    $this->qb->select('count(u)')
        ->from($this->tbl_name, 'u')
        ->where(
            $qb->expr()->lte('u.date_born', ':date_born'))
        )
        ->setParameters(array(
            'date_born' => $eighteenYearsAgo,
        ))
    ;

    return $this->qb->getQuery()->getSingleScalarResult();
}