(Symfony 3.1) PHP Doctrine Group entities by datetime

Question

I am stack trying to group my entities by datetime. every entity has the following columns: id, device, start, stop, ipaddress.

I want to be able to get entities within a range of date and group them by date(day) following with how many hours each days has.

This is what i have so far

public function findAllGroupedByDate($from = null, $to = null)
{
    $from = isset($from) && !is_null($from) ? $from : date('Y-m-d', time());
    $to = isset($to) && !is_null($to) ? $to : date('Y-m-d', time());

    $from = new \DateTime("-3 days");
    $to = new \DateTime("1 days");

    $from = date("Y-m-d", $from->getTimestamp());
    $to = date("Y-m-d", $to->getTimestamp());

    $q = $this->getEntityManager()->createQueryBuilder()
        ->select("timelog")
        ->from("AppBundle:Timelog", "timelog")
        ->where("timelog.start BETWEEN :from AND :to")
        ->setParameter("from", $from)
        ->setParameter("to", $to)
        ->orderBy("timelog.stop")
        ->getQuery();
    return $q->getResult();
}

public function findFromToday()
{
    $q = $this->createQueryBuilder('t')
            ->select('t.id', 't.start', 't.stop', 't.stop')
            ->where("t.start >= :today")
            ->setParameter("today", date('Y-m-d', time()))
            ->groupBy("t.id", "t.stop")
            ->orderBy("t.start", "asc")
            ->getQuery();

    return $q->getResult();
}

This is the code for my repository class.

and the code for from my controller looks like this:

$timelogsRepo = $this->getDoctrine()->getRepository('AppBundle:Timelog');

// Grab logs from today
$timelogsCollection = $timelogsRepo->findFromToday();
$tmpLogs = $timelogsRepo->findAllGroupedByDate();

// THIS SECTION IS FOR CALCULATING HOURS & MINUTES
    $minutes = 0;
    $hours = 0;
    foreach($timelogsCollection as $log)
    {
        $time1 = $log['start'];
        $time2 = $log['stop'];
        $interval = date_diff($time1, $time2);

        $hours += $interval->h;
        $minutes += $interval->i;

    }


    $minutes = $minutes >59 ? $minutes/60 : $minutes;


    return $this->render(':Timelog:index.html.twig', [
        'timelogs' => $logsOut,
        'hours' => $hours,
        'minutes' => $minutes
    ]);

So far i was able to calculate total spent time for a given day(only one day). Now i would like to get alle entities, group them by same date(day) and return data with interval.

Example DB table looks like this[id, device_id, start, stop, ipaddress]

1 1 2016-08-09 09:00:06 2016-08-09 12:00:06 127.0.0.1
2 1 2016-08-08 07:00:00 2016-08-08 13:00:00 127.0.0.1
3 1 2016-08-08 13:10:00 2016-08-08 15:05:00 127.0.0.1

So in this case my output would be something like:

[
    0 => ["date" => "2016-08-09", "hours" => 9.00, "ipaddress" =>      "127.0.0.1"],
    1 => ["date" => "2016-08-09", "hours" => 1.45, "ipaddress" => "127.0.0.1"]
]

the interval depends on start and stop both are type of DateTime

I have tried using doctrine-extensions: oro/doctrine-extensions but now i am getting exception error:

[2/2] QueryException: [Syntax Error] line 0, col 50: Error: Expected Unit is not valid for TIMESTAMPDIFF function. Supported units are: "MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR", got 'stop'

My repository method looks like this:

public function findByToday()
{
    $fields = array(
        'DATE(stop) as stop',
        'TIME(SUM(TIMESTAMPDIFF(stop, start))) AS tdiff',
        'device_id',
        'ipaddress'
    );

    $q = $this->createQueryBuilder('t')
        ->select($fields)
        ->where('DATE(stop) = :today')
        ->setParameter('today', date('Y-m-d', time()))
        ->groupBy('device_id')
        ->getQuery();

    return $q->getResult();

}

my DB table:

id  device_id   start   stop    ipaddress
5   1   2016-08-09 09:00:06 2016-08-09 12:00:06 127.0.0.1
6   1   2016-08-08 07:00:00 2016-08-08 13:00:00 127.0.0.1
7   1   2016-08-08 13:10:00 2016-08-08 15:05:00 127.0.0.1

BTW i am using Symfony 3, could that be the problem?

Answer 1

Based on your table above (if I got it right) I would try to get the data directly with MYSQL and then work my way to Doctrine.

If you have the below table:

CREATE TABLE `testdevices` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `device` INT(11) NULL DEFAULT NULL,
    `dstart` DATETIME NULL DEFAULT NULL,
    `dend` DATETIME NULL DEFAULT NULL,
    `ip` TINYTEXT NULL,
    PRIMARY KEY (`id`)
);

And populated with below test data (I've created two devices the second one was conveniently active for exact 3 hours on today's date):

mysql> select * from testdevices;
+----+--------+---------------------+---------------------+-----------+
| id | device | dstart              | dend                | ip        |
+----+--------+---------------------+---------------------+-----------+
|  1 |      1 | 2016-08-09 09:00:06 | 2016-08-09 12:00:06 | 127.0.0.1 |
|  2 |      1 | 2016-08-08 07:00:00 | 2016-08-08 13:00:00 | 127.0.0.1 |
|  3 |      1 | 2016-08-08 13:10:00 | 2016-08-11 22:14:46 | 127.0.0.1 |
|  4 |      2 | 2016-08-11 13:00:00 | 2016-08-11 14:00:00 | 127.0.0.1 |
|  5 |      2 | 2016-08-11 15:00:00 | 2016-08-11 16:00:00 | 127.0.0.1 |
|  6 |      2 | 2016-08-11 17:00:00 | 2016-08-11 18:00:00 | 127.0.0.1 |
+----+--------+---------------------+---------------------+-----------+
6 rows in set (0.00 sec)

Following MYSQL query will then I believe output the data you want:

SELECT DATE(dend) as dend, TIME(SUM(TIMEDIFF(dend, dstart))) AS tdiff, device, ip 
FROM testdevices WHERE DATE(dend) = '2016-08-11' 
GROUP BY device ;

And the result would look like this:

+------------+----------+--------+-----------+
| dend       | tdiff    | device | ip        |
+------------+----------+--------+-----------+
| 2016-08-11 | 81:04:46 |      1 | 127.0.0.1 |
| 2016-08-11 | 03:00:00 |      2 | 127.0.0.1 |
+------------+----------+--------+-----------+
2 rows in set (0.01 sec)

Note that second device time is correct. Now, the remaining question is how to do that in Doctrine. As far as I know the TIMEDIFF function is not a part of Doctrine yet so I would propose 2 different approaches:

1. Write your own function as in http://www.doctrine-project.org/2010/03/29/doctrine2-custom-dql-udfs.html#date-diff (There is example of DATEDIFF in this link so it is easily adjustable), or
2. Use https://packagist.org/packages/oro/doctrine-extensions

Your final repository function in Doctrine/Symfony2 with oro/doctrine-extensions installed would look something like this:

 $fields = array(
            'DATE(dend) as dend', 
            'SUM(TIMESTAMPDIFF(SECOND, dend, dstart)) AS tdiff',
            'device', 
            'ip'
        );

public function findFromToday()
{
    $q = $this->createQueryBuilder('t')
            ->select($fields)
            ->where('DATE(dend) = :today')
            ->setParameter('today', date('Y-m-d', time()))
            ->groupBy('device')
            ->getQuery();

    return $q->getResult();

I wrote this function without testing it at all and from the top of my head. You will probably find that it fails. This would give the time difference in seconds as the TIMESTAMPDIFF requires units to be specified. Install oro/doctrine-extensions with

composer require oro/doctrine-extensions