Reviews

Calculation review count periodically to avoid from realtime calculation

Calculate every 2 hours query example for company table

CREATE OR REPLACE FUNCTION update_companies_review_count()
RETURNS VOID AS
$$
BEGIN
    UPDATE company SET review_count = 0;

    UPDATE company c
    SET review_count = COALESCE(subquery.review_count, 0)
    FROM (
        SELECT review.company_id, COUNT(review.id) AS review_count
        FROM review
        LEFT JOIN review_user ON review.user_id = review_user.id
        WHERE (
            review.is_deleted = false AND review.status = 1 AND review.parent_id IS NULL
            AND (review_user.is_deleted IS NULL OR review_user.is_deleted = false)
        )
        GROUP BY company_id
    ) AS subquery
    WHERE c.id = subquery.company_id;
    RETURN;
END;
$$
LANGUAGE plpgsql;

write also to other tables that have review count, especially for company, product, and menu_product