Skip to content

Trouve un container_id pour une user_activity sans container_id

La requête permet de retrouver un container_id pour une user_activity n'en ayant pas, en se basant sur les licences de l'utilisateur et les containers associés à ces licences.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
WITH user_activity_business AS (
    SELECT user_activity.* FROM user_activity
    JOIN activity ON activity.id = user_activity.activity_id
    WHERE activity.product_code = 'BUSINESS_ENGLISH'
    AND user_activity.container_id IS NULL
), license_with_container AS (
    SELECT organization_license.id AS license_id,
        organization_license.user_id,
        user_activity_business.id AS ua_id,
        container_mode_pivot.container_id
    FROM organization_license
    JOIN user_activity_business ON user_activity_business.user_id = organization_license.user_id
    JOIN container_mode_pivot ON container_mode_pivot.mode_id = user_activity_business.mode_id
)
SELECT
    license_with_container.ua_id,
    container_organization_license_pivot.container_id AS license_container_id
FROM container_organization_license_pivot
JOIN license_with_container 
    ON license_with_container.license_id = container_organization_license_pivot.organization_license_id
    AND license_with_container.container_id = container_organization_license_pivot.container_id
GROUP BY license_with_container.ua_id, license_container_id;

Exemple d'utilisation de la requête lors d'une régulation réalisée en production

Création d'une table temporaire pour stocker les résultats

1
2
3
4
CREATE TEMPORARY TABLE temp (
    ua_id bigint(10),
    license_container_id bigint(10)
);

Insertion des résultats dans la table temporaire

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
INSERT INTO temp
WITH user_activity_business AS (
    SELECT user_activity.*
    FROM user_activity
    JOIN activity ON activity.id = user_activity.activity_id
    WHERE activity.product_code = 'BUSINESS_ENGLISH'
    AND user_activity.container_id IS NULL
), license_with_container AS (
    SELECT organization_license.id AS license_id,
        organization_license.user_id,
        user_activity_business.id AS ua_id,
        container_mode_pivot.container_id
    FROM organization_license
    JOIN user_activity_business ON user_activity_business.user_id = organization_license.user_id
    JOIN container_mode_pivot ON container_mode_pivot.mode_id = user_activity_business.mode_id
)
SELECT
    license_with_container.ua_id,
    container_organization_license_pivot.container_id AS license_container_id
FROM container_organization_license_pivot
JOIN license_with_container
    ON license_with_container.license_id = container_organization_license_pivot.organization_license_id
    AND license_with_container.container_id = container_organization_license_pivot.container_id
GROUP BY license_with_container.ua_id, license_container_id;

Mise à jour de la table user_activity avec les container_id trouvés

1
2
3
UPDATE user_activity
JOIN temp ON temp.ua_id = user_activity.id
SET user_activity.container_id = temp.license_container_id;

Keywords

select user_activity container_id régulation