ap2i/sql/schema-extensions/001-view_public_events.sql

CREATE OR REPLACE VIEW view_public_events
AS
    SELECT
        b.uuid, b.organization_id AS organizationId, b.name, b.description, b.url, b.datetimeStart, b.datetimeEnd,
        p.name AS placeName, p.description AS placeDescription, p.floorSize AS placeFloorSize, p.capacity AS placeCapacity,
        ap.addressCity AS city, ap.postalCode,
        TRIM(BOTH ' ' FROM CONCAT( if(ap.streetAddress is null,'',ap.streetAddress), ' ', if(ap.streetAddressSecond is null,'',ap.streetAddressSecond), ' ', if(ap.streetAddressThird is null,'',ap.streetAddressThird))) AS streetAddress,
        ap.longitude, ap.latitude,
        r.name AS roomName, r.description AS roomDescription, r.localisation AS roomLocalisation, r.capacity AS roomCapacity, r.floorSize AS roomFloorSize,
        If(b.image_id is not null, CONCAT('https://api.opentalent.fr/app.php/_internal/secure/files/', b.image_id, '/raw'), null) AS imageUrl,
        If(b.image_id is not null, CONCAT('https://api.opentalent.fr/app.php/_internal/secure/files/', b.image_id, '/0x120'), null) AS thumbnailUrl,
        (SELECT GROUP_CONCAT(CONCAT(f.code))
         FROM event_categories AS ec
            LEFT JOIN Categories AS cs ON(cs.id = ec.categories_id)
            LEFT JOIN Familly AS f ON(f.id = cs.familly_id)
         WHERE ec.event_id = b.id
        ) AS categories, 'opentalent' as origin, b.id as entityId
    FROM Booking AS b
             INNER JOIN Organization o ON o.id = b.organization_id
             INNER JOIN Parameters par ON par.id = o.parameters_id
             LEFT JOIN Place AS p ON (p.id = b.place_id)
             LEFT JOIN AddressPostal AS ap ON (ap.id = p.addressPostal_id)
             LEFT JOIN Room AS r ON (r.id = b.room_id)
    WHERE b.discr = 'event' AND b.datetimeEnd >= NOW() AND b.visibility = 'PUBLIC_VISIBILITY' AND b.isCanceled = 0
UNION
    SELECT
        aw.uuid, null AS organizationId, aw.name, aw.description, aw.deepLink AS url, aw.datetimeStart, aw.datetimeEnd,
        aw.place AS placeName, NULL AS placeDescription, NULL AS placeFloorSize, NULL AS placeCapacity,
        aw.city, aw.postalCode, aw.streetAddress, aw.longitude, aw.latitude,
        NULL AS roomName, NULL AS roomDescription, NULL AS roomLocalisation, NULL AS roomCapacity, NULL AS roomFloorSize,
        aw.largeimage AS imageUrl, aw.mediumimage as thumbnailUrl, aw.categories AS categories, 'awin' as origin, aw.id as entityId
    FROM  AwinProduct as aw
    WHERE
        aw.datetimeEnd >= NOW() AND aw.datetimeStart IS NOT NULL;