.. Copyright (C) 2014 Champs Libres Cooperative SCRLFS 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". .. _entity-info: 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 :ref:`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. .. code:: php /** * * @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): .. code:: sql 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``.