gai_do_refresh_reputation_desc_album_community_stacked

gai_do_refresh_reputation_desc_album_community_stacked.sql

CREATE FUNCTION gai_do_refresh_reputation_desc_album_community_stacked(INTEGER DEFAULT NULL, INTEGER DEFAULT NULL) RETURNS BOOLEAN AS $$
DECLARE
    lock    RECORD;
BEGIN
    --Lock
    SELECT id
    FROM object_display_order_types
    WHERE id = get_object_display_order_type('reputation_desc', get_huddler_constant('CONTENT_TYPE_GALLERY_ALBUM')::integer)
    INTO lock FOR UPDATE;

    --Update single album
    IF ($1 IS NOT NULL AND $2 IS NOT NULL) THEN
        CREATE TEMPORARY TABLE temp_object_display_order_refresh_gai_rep_ca_stacked ON COMMIT DROP AS (
            WITH community_gallery_albums AS (
                SELECT
                    object_type AS object_type,
                    object_id   AS object_id
                FROM gallery_albums
                WHERE object_type = $1
                AND   object_id   = $2
                GROUP BY object_type, object_id
            )
            SELECT object_type, object_id, get_object_display_order_type('reputation_desc', get_huddler_constant('CONTENT_TYPE_GALLERY_ALBUM')::integer) AS type,
            ARRAY(
                SELECT gallery_albums.id
                    FROM gallery_albums
                    LEFT JOIN object_statistics_rollups ON
                    (
                       object_statistics_rollups.type        = get_object_statistics_rollup_types('rating')                AND
                       object_statistics_rollups.object_type = get_huddler_constant('CONTENT_TYPE_GALLERY_ALBUM')::integer AND
                       gallery_albums.id                     = object_statistics_rollups.object_id
                    )
                    WHERE gallery_albums.object_type = community_gallery_albums.object_type
                    AND   gallery_albums.object_id   = community_gallery_albums.object_id
                    AND   (gallery_albums.status & get_huddler_constant('STATUS_BIT_VIEWABLE')::int)::bool
                    AND   gallery_albums.gallery_image_count > 0
                    ORDER BY object_statistics_rollups.value DESC NULLS LAST, gallery_albums.last_uploaded_at DESC, gallery_albums.id DESC
            ) AS new_object_display_order
            FROM community_gallery_albums
            GROUP BY object_type, object_id
        );

    --Update all albums
    ELSE
        CREATE TEMPORARY TABLE temp_object_display_order_refresh_gai_rep_ca_stacked ON COMMIT DROP AS (
            WITH community_gallery_albums AS (
                SELECT
                    object_type AS object_type,
                    object_id   AS object_id
                FROM gallery_albums
                WHERE object_type = get_huddler_constant('CONTENT_TYPE_GALLERY_ALBUM')::int
                AND   object_id   IS NOT NULL
                GROUP BY object_type, object_id
            )
            SELECT object_type, object_id, get_object_display_order_type('reputation_desc', get_huddler_constant('CONTENT_TYPE_GALLERY_ALBUM')::integer) AS type,
            ARRAY(
                SELECT gallery_albums.id
                    FROM gallery_albums
                    LEFT JOIN object_statistics_rollups ON
                    (
                       object_statistics_rollups.type        = get_object_statistics_rollup_types('rating')                AND
                       object_statistics_rollups.object_type = get_huddler_constant('CONTENT_TYPE_GALLERY_ALBUM')::integer AND
                       gallery_albums.id                     = object_statistics_rollups.object_id
                    )
                    WHERE gallery_albums.object_type = community_gallery_albums.object_type
                    AND   gallery_albums.object_id   = community_gallery_albums.object_id
                    AND   (gallery_albums.status & get_huddler_constant('STATUS_BIT_VIEWABLE')::int)::bool
                    AND   gallery_albums.gallery_image_count > 0
                    ORDER BY object_statistics_rollups.value DESC NULLS LAST, gallery_albums.last_uploaded_at DESC, gallery_albums.id DESC
            ) AS new_object_display_order
            FROM community_gallery_albums
            GROUP BY object_type, object_id
        );
    END IF;

    UPDATE object_display_order
    SET display_order_object_id = new_object_display_order::int[]
    FROM temp_object_display_order_refresh_gai_rep_ca_stacked
    WHERE object_display_order.object_type               = temp_object_display_order_refresh_gai_rep_ca_stacked.object_type AND
          object_display_order.object_id                 = temp_object_display_order_refresh_gai_rep_ca_stacked.object_id   AND
          object_display_order.display_order_object_type = temp_object_display_order_refresh_gai_rep_ca_stacked.type        AND
          object_display_order.display_order_object_id  <> temp_object_display_order_refresh_gai_rep_ca_stacked.new_object_display_order::int[];

    -- insert the new
    INSERT INTO object_display_order (object_type, object_id, display_order_object_type, display_order_object_id)
    SELECT T.object_type, T.object_id, T.type AS display_order_object_type, T.new_object_display_order AS display_order_object_id
    FROM temp_object_display_order_refresh_gai_rep_ca_stacked AS T
    LEFT JOIN object_display_order ON (
        object_display_order.object_type               = T.object_type AND
        object_display_order.object_id                 = T.object_id   AND
        object_display_order.display_order_object_type = T.type
    )
    WHERE object_display_order.object_type IS NULL;

    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;