article_interest_view

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

int8

√

null

user_id

int8

√

null

score

float8

17,17

√

null

View Definition

WITH tag_interest AS (
         SELECT user_tag.tag_id,
            user_tag.user_id,
            ((user_tag.count)::numeric / (tag_total.count)::numeric) AS interest
           FROM (( SELECT article_tag.tag_id,
                    articles.user_id,
                    count(article_tag.tag_id) AS count
                   FROM (( SELECT comment.article_id AS id,
                            comment.author_id AS user_id
                           FROM comment
                        UNION
                         SELECT appreciation.reference_id AS id,
                            appreciation.sender_id AS user_id
                           FROM appreciation) articles
                     JOIN article_tag ON ((article_tag.article_id = articles.id)))
                  WHERE (articles.user_id IS NOT NULL)
                  GROUP BY article_tag.tag_id, articles.user_id) user_tag
             JOIN ( SELECT count(article_tag.tag_id) AS count,
                    article_tag.tag_id AS id
                   FROM article_tag
                  GROUP BY article_tag.tag_id) tag_total ON ((tag_total.id = user_tag.tag_id)))
        ), article_interest AS (
         SELECT article.id,
            tag_interest.user_id,
            sum(tag_interest.interest) AS interest
           FROM ((article
             JOIN article_tag ON ((article_tag.article_id = article.id)))
             JOIN tag_interest ON ((article_tag.tag_id = tag_interest.tag_id)))
          WHERE (article.state = 'active'::text)
          GROUP BY article.id, tag_interest.user_id
        )
 SELECT article_value_materialized.id,
    article_interest.user_id,
    ((((article_value_materialized.score)::numeric)::double precision / (( SELECT max(article_value_materialized_1.score) AS max
           FROM article_value_materialized article_value_materialized_1) + (1)::double precision)) * ((article_interest.interest / (( SELECT max(article_interest_1.interest) AS max
           FROM article_interest article_interest_1) + (1)::numeric)))::double precision) AS score
   FROM ((article_value_materialized
     JOIN article_interest ON ((article_interest.id = article_value_materialized.id)))
     LEFT JOIN ( SELECT article_read_count.article_id,
            article_read_count.user_id
           FROM article_read_count
          WHERE (article_read_count.user_id IS NOT NULL)) read ON (((read.article_id = article_interest.id) AND (read.user_id = article_interest.user_id))))
  WHERE ((read.article_id IS NULL) AND (article_value_materialized.score > (0)::double precision));

Columns

View Definition

Possibly Referenced Tables/Views