Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation; with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts. A copy of the license is included in the section entitled “GNU Free Documentation License”.

Stats about event on entity in php world

It is necessary to be able to gather information about events for some entities:

  • when the event has been done;
  • who did it;

Those “infos” are not linked with right management, like describe in Timelines.

“infos” for some stats and info about an entity

Building an info means:

  • create an Entity, and map this entity to a SQL view (not a regular table);
  • use the framework to build this entity dynamically.

A framework api is built to be able to build multiple “infos” entities through “union” views:

  • use a command bin/console chill:db:sync-views to synchronize view (create view if it does not exists, or update views when new SQL parts are added in the UNION query. Internally, this command call a new ViewEntityInfoManager, which iterate over available views to build the SQL;
  • one can create a new “view entity info” by implementing a ViewEntityInfoProviderInterface
  • this implementation of the interface is free to create another interface for building each part of the UNION query. This interface is created for AccompanyingPeriodInfo: Chill\PersonBundle\Service\EntityInfo\AccompanyingPeriodInfoUnionQueryPartInterface

So, converting new “events” into rows for AccompanyingPeriodInfo is just implementing this interface!

Implementation for AccompanyingPeriod (AccompanyingPeriod/AccompanyingPeriodInfo)

A class is created for computing some statistical info for an AccompanyingPeriod: AccompanyingPeriod/AccompanyingPeriodInfo. This contains information about “something happens”, who did it and when.

Having those info in table answer some questions like:

  • when is the last and the first action (AccompanyingPeriodWork, Activity, AccompanyingPeriodWorkEvaluation, …) on the period;
  • who is “acting” on the period, and when is the last “action” for each user.

The AccompanyingPeriod info is mapped to a SQL view, not a table. The sql view is built dynamically (see below), and gather infos from ActivityBundle, PersonBundle, CalendarBundle, … It is possible to create custom bundle and add info on this view.

/**
 *
 * @ORM\Entity()
 * @ORM\Table(name="view_chill_person_accompanying_period_info") <==== THIS IS A VIEW, NOT A TABLE
 */
class AccompanyingPeriodInfo
{
  // ...
}

Why do we need this ?

For multiple jobs in PHP world:

  • moving the accompanying period to another steps when inactive, automatically;
  • listing all the users which are intervening on the action on a new “Liste des intervenants” page;
  • filtering on exports

Later, we will launch automatic anonymise for accompanying period and all related entities through this information.

How is built the SQL views which is mapped to “info” entities ?

The AccompanyingPeriodInfo entity is mapped by a SQL view (not a regular table).

The sql view is built dynamically, it is a SQL view like this, for now (April 2023):

create view view_chill_person_accompanying_period_info
            (accompanyingperiod_id, relatedentity, relatedentityid, user_id, infodate, discriminator, metadata) as
SELECT w.accompanyingperiod_id,
       'Chill\PersonBundle\Entity\AccompanyingPeriod\AccompanyingPeriodWork'::text AS relatedentity,
       w.id                                                                        AS relatedentityid,
       cpapwr.user_id,
       w.enddate                                                                   AS infodate,
       'accompanying_period_work_end'::text                                        AS discriminator,
       '{}'::jsonb                                                                 AS metadata
FROM chill_person_accompanying_period_work w
         LEFT JOIN chill_person_accompanying_period_work_referrer cpapwr ON w.id = cpapwr.accompanyingperiodwork_id
WHERE w.enddate IS NOT NULL
UNION
SELECT cpapw.accompanyingperiod_id,
       'Chill\PersonBundle\Entity\AccompanyingPeriod\AccompanyingPeriodWorkEvaluation'::text AS relatedentity,
       e.id                                                                                  AS relatedentityid,
       e.updatedby_id                                                                        AS user_id,
       e.updatedat                                                                           AS infodate,
       'accompanying_period_work_evaluation_updated_at'::text                                AS discriminator,
       '{}'::jsonb                                                                           AS metadata
FROM chill_person_accompanying_period_work_evaluation e
         JOIN chill_person_accompanying_period_work cpapw ON cpapw.id = e.accompanyingperiodwork_id
WHERE e.updatedat IS NOT NULL
UNION
SELECT cpapw.accompanyingperiod_id,
       'Chill\PersonBundle\Entity\AccompanyingPeriod\AccompanyingPeriodWorkEvaluation'::text AS relatedentity,
       e.id                                                                                  AS relatedentityid,
       cpapwr.user_id,
       e.maxdate                                                                             AS infodate,
       'accompanying_period_work_evaluation_start'::text                                     AS discriminator,
       '{}'::jsonb                                                                           AS metadata
FROM chill_person_accompanying_period_work_evaluation e
         JOIN chill_person_accompanying_period_work cpapw ON cpapw.id = e.accompanyingperiodwork_id
         LEFT JOIN chill_person_accompanying_period_work_referrer cpapwr ON cpapw.id = cpapwr.accompanyingperiodwork_id
WHERE e.maxdate IS NOT NULL
UNION
SELECT cpapw.accompanyingperiod_id,
       'Chill\PersonBundle\Entity\AccompanyingPeriod\AccompanyingPeriodWorkEvaluation'::text AS relatedentity,
       e.id                                                                                  AS relatedentityid,
       cpapwr.user_id,
       e.startdate                                                                           AS infodate,
       'accompanying_period_work_evaluation_start'::text                                     AS discriminator,
       '{}'::jsonb                                                                           AS metadata
FROM chill_person_accompanying_period_work_evaluation e
         JOIN chill_person_accompanying_period_work cpapw ON cpapw.id = e.accompanyingperiodwork_id
         LEFT JOIN chill_person_accompanying_period_work_referrer cpapwr ON cpapw.id = cpapwr.accompanyingperiodwork_id
UNION
SELECT cpapw.accompanyingperiod_id,
       'Chill\PersonBundle\Entity\AccompanyingPeriod\AccompanyingPeriodWorkEvaluationDocument'::text AS relatedentity,
       doc.id                                                                                        AS relatedentityid,
       doc.updatedby_id                                                                              AS user_id,
       doc.updatedat                                                                                 AS infodate,
       'accompanying_period_work_evaluation_document_updated_at'::text                               AS discriminator,
       '{}'::jsonb                                                                                   AS metadata
FROM chill_person_accompanying_period_work_evaluation_document doc
         JOIN chill_person_accompanying_period_work_evaluation e ON doc.accompanyingperiodworkevaluation_id = e.id
         JOIN chill_person_accompanying_period_work cpapw ON cpapw.id = e.accompanyingperiodwork_id
WHERE doc.updatedat IS NOT NULL
UNION
SELECT cpapw.accompanyingperiod_id,
       'Chill\PersonBundle\Entity\AccompanyingPeriod\AccompanyingPeriodWorkEvaluation'::text AS relatedentity,
       e.id                                                                                  AS relatedentityid,
       cpapwr.user_id,
       e.maxdate                                                                             AS infodate,
       'accompanying_period_work_evaluation_max'::text                                       AS discriminator,
       '{}'::jsonb                                                                           AS metadata
FROM chill_person_accompanying_period_work_evaluation e
         JOIN chill_person_accompanying_period_work cpapw ON cpapw.id = e.accompanyingperiodwork_id
         LEFT JOIN chill_person_accompanying_period_work_referrer cpapwr ON cpapw.id = cpapwr.accompanyingperiodwork_id
WHERE e.maxdate IS NOT NULL
UNION
SELECT w.accompanyingperiod_id,
       'Chill\PersonBundle\Entity\AccompanyingPeriod\AccompanyingPeriodWork'::text AS relatedentity,
       w.id                                                                        AS relatedentityid,
       cpapwr.user_id,
       w.startdate                                                                 AS infodate,
       'accompanying_period_work_start'::text                                      AS discriminator,
       '{}'::jsonb                                                                 AS metadata
FROM chill_person_accompanying_period_work w
         LEFT JOIN chill_person_accompanying_period_work_referrer cpapwr ON w.id = cpapwr.accompanyingperiodwork_id
UNION
SELECT activity.accompanyingperiod_id,
       'Chill\ActivityBundle\Entity\Activity'::text AS relatedentity,
       activity.id                                  AS relatedentityid,
       au.user_id,
       activity.date                                AS infodate,
       'activity_date'::text                        AS discriminator,
       '{}'::jsonb                                  AS metadata
FROM activity
         LEFT JOIN activity_user au ON activity.id = au.activity_id
WHERE activity.accompanyingperiod_id IS NOT NULL;

As you can see, the view gather multiple SELECT queries and bind them with UNION.

Each SELECT query is built dynamically, through a class implementing an interface: Chill\PersonBundle\Service\EntityInfo\AccompanyingPeriodInfoUnionQueryPartInterface, like here

To add new SELECT query in different UNION parts in the sql view, create a service and implements this interface: Chill\PersonBundle\Service\EntityInfo\AccompanyingPeriodInfoUnionQueryPartInterface.