CODEWITHSUNDEEP
symfonydoctrine-orm
whitebear
whitebear

Reputation: 12433

How to ge the data rows which is belonging to one date

I have table like.

ID | startdatetime
1  | 2013-08-30 22:30:00
2  | 2013-08-29 12:00:00
3  | 2013-08-29 13:30:00
4  | 2013-08-27 11:30:00
5  | 2013-08-27 13:30:00
6  | 2013-08-26 09:30:00

I want to get the the data which are belonging to a date (for example rows 2 and 3 are belonging to 2013-08-29 )

How could I write in Doctrine?

$em = $this->getDoctrine()->getEntityManager();

$items = $em->getRepository("UserBundle:Table")->findBy('startdatetime' => '*****'));

Upvotes: 0

Views: 53

Answers (2)

A.L
A.L

Reputation: 10503

Another solution:

Add a getDay() function in your Table repository:

public function getDay($date)
{
    $qb = $this->createQueryBuilder('t');

    $q = $qb
        ->add('where',
        $qb->expr()->like('t.startdatetime', 
            $qb->expr()->literal($date.'%')))
        ->getQuery();

    return $q->getResult();
}

Then just call this function:

$items = $em->getRepository("UserBundle:Table")
    ->getDay('2013-08-29');

Upvotes: 0

Udan
Udan

Reputation: 5609

You have two options:

1 - the quick and dirty one is to use the MySQL SUBSTRING to get the date out of the datetime:

WHERE SUBSTRING(dateTimeField,1,10) = '2013-08-29'

2 - Use DoctrineExtensions and then you can select data like this:

$q->select('p')
  ->where('YEAR(startdatetime) = :year')
  ->andWhere('MONTH(startdatetime) = :month')
  ->andWhere('DAY(startdatetime) = :day');

$q->setParameter('year', $year)
  ->setParameter('month', $month)
  ->setParameter('day', $day);

Upvotes: 2

Related Questions