Detect DOCTRINE date collisions

Question

Detect DOCTRINE date collisions

Navigation

#1 by (1 votes)

3

I have a table TAREAS formed in the following way:

+--------+------------+---------------------+---------------------+
| id     | informe_id | inicio              | fin                 |
+--------+------------+---------------------+---------------------+
| 569811 |     266667 | 2017-07-17 09:10:00 | 2017-07-17 14:50:00 |
| 569810 |     266667 | 2017-07-17 00:00:00 | 2017-07-17 01:10:00 |
| 569809 |     266664 | 2017-07-12 00:00:00 | 2017-07-13 09:50:00 |
| 569808 |     266663 | 2017-07-12 00:00:00 | 2017-07-13 05:50:00 |
| 569807 |     266662 | 2017-07-11 19:00:00 | 2017-07-11 20:00:00 |
| 569806 |     266661 | 2017-07-10 16:00:00 | 2017-07-10 17:30:00 |
|    ... |        ... |                 ... |                 ... |   
+--------+------------+---------------------+---------------------+

There is more data, I have only put one part.

It turns out that, among the data, there are cases in which a task in a report coincides or, rather, "collides" among other tasks in other reports. The tasks overlap each other. I have been asked to indicate which reports have tasks that collide with other reports .

To do so, I have come to this query:

SELECT a.id, a.informe_id as informe_a, a.inicio as inicio_a, a.fin as fin_a, b.id, b.informe_id as informe_c, b.inicio as inicio_b, b.fin as fin_b 
FROM tareas a, tareas b 
WHERE a.informe_id <> b.informe_id
AND a.id <> b.id
AND (
    ( a.inicio between b.inicio and b.fin )
OR  ( a.fin    between b.inicio and b.fin )
OR  ( b.inicio between a.inicio and a.fin )
OR  ( b.fin    between a.inicio and a.fin )  
)

That shows as a result:

+--------+-----------+---------------------+---------------------+--------+-----------+---------------------+---------------------+
| id     | informe_a | inicio_a            | fin_a               | id     | informe_c | inicio_b            | fin_b               |
+--------+-----------+---------------------+---------------------+--------+-----------+---------------------+---------------------+
|  52881 |     27351 | 2015-02-26 08:30:00 | 2015-02-26 16:30:00 |  52921 |     27671 | 2015-02-26 08:30:00 | 2015-02-26 13:30:00 |
|  52921 |     27671 | 2015-02-26 08:30:00 | 2015-02-26 13:30:00 |  52881 |     27351 | 2015-02-26 08:30:00 | 2015-02-26 16:30:00 |
|  54141 |     28241 | 2015-03-02 08:45:00 | 2015-03-02 15:00:00 |  54371 |     28341 | 2015-03-02 08:30:00 | 2015-03-02 10:30:00 |
|  54371 |     28341 | 2015-03-02 08:30:00 | 2015-03-02 10:30:00 |  54141 |     28241 | 2015-03-02 08:45:00 | 2015-03-02 15:00:00 |
|    ... |       ... |                 ... |                 ... |    ... |       ... |                 ... |                 ... | 
+--------+-----------+---------------------+---------------------+--------+-----------+---------------------+---------------------+

I find the collisions that interest me. (there are more data, as in the previous case).

The problem I have is that I can not find a way to "convert" the query as Doctrine , since it is a product of tables without JOIN , and I do not see in the documentation how to do it.

As an alternative, I tried to do the following:

$informe = $this->getDoctrine()->getManager()->getRepository("AppBundle:Informe")->findById($informe_id);

$inicio = $informe->getTareas()->first()->getInicio();
$fin    = $informe->getTareas()->last()->getFin();

$list = $this->getEntityManager()
    ->createQueryBuilder()
    ->select('o')
    ->from('AppBundle:Informe', 'o')
    ->innerJoin('o.tareas', 't')
    ->where('(t.inicio BETWEEN :inicio AND :fin ) OR (t.fin BETWEEN :inicio AND :fin)')
    ->setParameter('inicio', $inicio)
    ->setParameter('fin', $fin)
    ->andWhere('o <> :informe')
    ->setParameter('informe', $informe)
    ->groupBy('o')
    ->getQuery()
    ->getResult()

But this, although it seems to resolve the issue, returns all collisions, including those that are not.

There are cases of the type:

+--------+------------+---------------------+---------------------+
| id     | informe_id | inicio              | fin                 |
+--------+------------+---------------------+---------------------+
| 569811 |     266667 | 2017-07-17 09:00:00 | 2017-07-17 10:00:00 |
| 569810 |     266667 | 2017-07-17 10:00:00 | 2017-07-17 20:00:00 |
| 569809 |     266664 | 2017-07-12 20:00:00 | 2017-07-13 21:00:00 |
+--------+------------+---------------------+---------------------+

That is, one of the reports has two tasks, one from 9 to 10h and another from 20 to 21h, and the other one from 10am to 8pm, and it should not be a collision. I understand that this result has its meaning, since I am taking the start dates of the first task of a report, and the end of the last task of a part, but it is not the one I am looking for, and I do not know how to solve it either. p>

Is there any way to do the query in another way, or do you see any failure in it?

mysql sql php doctrine2

asked by Jakala 20.07.2017 в 10:59

source

1 answer

IF conditional in PHP AJAX does not send data to PHP

score 1 · Answer 1

I guess you already have it fixed. I'll also leave my answer here in case someone has served him.

In the query you do a JOIN and you can use those fields from the JOIN table to create the condition.

Suppose the table is called tasks and we have the following data

+----+------------+---------------------+---------------------+
| id | informe_id | inicio              | fin                 |
+----+------------+---------------------+---------------------+
|  1 |     266667 | 2017-07-17 09:10:00 | 2017-07-17 14:50:00 |
|  2 |     266667 | 2017-07-17 00:00:00 | 2017-07-17 01:10:00 |
|  3 |     266664 | 2017-07-12 00:00:00 | 2017-07-13 09:50:00 |
|  4 |     266663 | 2017-07-12 00:00:00 | 2017-07-13 05:50:00 |
|  5 |     266662 | 2017-07-11 19:00:00 | 2017-07-11 20:00:00 |
|  6 |     266663 | 2017-07-10 16:00:00 | 2017-07-10 17:30:00 |
|  7 |         25 | 2017-07-12 00:00:00 | 2017-07-13 09:50:00 |
+----+------------+---------------------+---------------------+

Doing the following consultation

SELECT 
T1.id, T1.informe_id, T1.inicio, T1.fin, 
T2.id coli_id, T2.informe_id coli_inf, T2.inicio coli_inicio, T2.fin coli_fin 
FROM tareas T1
INNER JOIN tareas T2 ON  T1.id <> T2.id
and (
    ( T1.inicio between T2.inicio and T2.fin )
OR  ( T1.fin    between T2.inicio and T2.fin )
)

We get something like this:

+----+------------+---------------------+---------------------+---------+----------+---------------------+---------------------+
| id | informe_id | inicio              | fin                 | coli_id | coli_inf | coli_inicio         | coli_fin            |
+----+------------+---------------------+---------------------+---------+----------+---------------------+---------------------+
|  4 |     266663 | 2017-07-12 00:00:00 | 2017-07-13 05:50:00 |       3 |   266664 | 2017-07-12 00:00:00 | 2017-07-13 09:50:00 |
|  7 |         25 | 2017-07-12 00:00:00 | 2017-07-13 09:50:00 |       3 |   266664 | 2017-07-12 00:00:00 | 2017-07-13 09:50:00 |
|  3 |     266664 | 2017-07-12 00:00:00 | 2017-07-13 09:50:00 |       4 |   266663 | 2017-07-12 00:00:00 | 2017-07-13 05:50:00 |
|  7 |         25 | 2017-07-12 00:00:00 | 2017-07-13 09:50:00 |       4 |   266663 | 2017-07-12 00:00:00 | 2017-07-13 05:50:00 |
|  3 |     266664 | 2017-07-12 00:00:00 | 2017-07-13 09:50:00 |       7 |       25 | 2017-07-12 00:00:00 | 2017-07-13 09:50:00 |
|  4 |     266663 | 2017-07-12 00:00:00 | 2017-07-13 05:50:00 |       7 |       25 | 2017-07-12 00:00:00 | 2017-07-13 09:50:00 |
+----+------------+---------------------+---------------------+---------+----------+---------------------+---------------------+

If we try to pass it to doctrine queryBuilder it might look something like this

<?php

$queryBuilder
->select(
'T1.id', 'T1.informe_id', 'T1.inicio', 'T1.fin',
'T2.id coli_id', 'T2.informe_id coli_inf', 'T2.inicio coli_inicio', 'T2.fin coli_fin'
)
->from('tareas', 'T1')
->innerJoin('T1', 'tareas', 'T2', 
    'T1.id <> T2.id
    and (
        ( T1.inicio between T2.inicio and T2.fin )
    OR  ( T1.fin    between T2.inicio and T2.fin )
    )'
);

Another option could be to create a view, to make things easier, since this query will be static.