PostPopularityMView

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

id

uuid

2147483647

√

null

createdAt

timestamptz

35,6

√

null

popularity

float8

17,17

√

null

GroupId

uuid

2147483647

√

null

Relationships

Close relationships within degrees of separation

One

View Definition

WITH popularity_factors AS (
         SELECT post.id,
            post."createdAt",
            count(DISTINCT reply.id) AS "replyCount",
            count(DISTINCT reaction.id) AS "reactionCount",
            count(DISTINCT attachments.id) AS "imageCount",
            (date_part('d'::text, (now() - post."createdAt")) + (1)::double precision) AS age,
                CASE
                    WHEN (jsonb_array_length(((post.content -> 'entities'::text) -> 'urls'::text)) > 0) THEN 1
                    ELSE 0
                END AS "hasEmbeddedLinks",
                CASE
                    WHEN (jsonb_array_length((((post.content -> 'entities'::text) -> 'hashtag'::text) -> 'data'::text)) > 0) THEN 1
                    ELSE 0
                END AS "hasHashtags",
            post."GroupId"
           FROM ((((gcud."Posts" post
             LEFT JOIN gcud."Replies" reply ON (((post.id = reply."RootContentId") AND (reply.status = ANY (ARRAY['UNCATEGORIZED'::gcud."enum_Contents_status", 'APPROVED'::gcud."enum_Contents_status"])))))
             LEFT JOIN gcud."Replies" parentreply ON ((reply."ParentReplyId" = parentreply.id)))
             LEFT JOIN gcud."Reactions" reaction ON ((post.id = reaction."ContentId")))
             LEFT JOIN gcud."Attachments" attachments ON (((attachments."ContentId" = post.id) AND (attachments.type = 'IMAGE'::gcud."enum_Attachments_type"))))
          WHERE ((parentreply.status = ANY (ARRAY['UNCATEGORIZED'::gcud."enum_Contents_status", 'APPROVED'::gcud."enum_Contents_status"])) OR (parentreply.id IS NULL))
          GROUP BY post.id
        )
 SELECT popularity_factors.id,
    popularity_factors."createdAt",
    ((((((((popularity_factors."replyCount")::numeric * 0.1) + ((popularity_factors."reactionCount")::numeric * 0.45)) + ((popularity_factors."imageCount")::numeric * 0.05)) + ((popularity_factors."hasEmbeddedLinks")::numeric * 0.2)) + ((popularity_factors."hasHashtags")::numeric * 0.2)))::double precision *
        CASE
            WHEN (popularity_factors.age <= (3)::double precision) THEN ((1)::double precision / popularity_factors.age)
            WHEN (popularity_factors.age <= (7)::double precision) THEN ((1)::double precision / (popularity_factors.age ^ (2)::double precision))
            WHEN (popularity_factors.age <= (14)::double precision) THEN ((1)::double precision / (popularity_factors.age ^ (3)::double precision))
            WHEN (popularity_factors.age <= (21)::double precision) THEN ((1)::double precision / (popularity_factors.age ^ (4)::double precision))
            WHEN (popularity_factors.age <= (30)::double precision) THEN ((1)::double precision / (popularity_factors.age ^ (5)::double precision))
            ELSE ((1)::double precision / (popularity_factors.age ^ (6)::double precision))
        END) AS popularity,
    popularity_factors."GroupId"
   FROM popularity_factors;

Description

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views