import { Knex } from 'knex' ; import { ActivityType , CIRCLE_ACTION , CIRCLE_STATE , MATERIALIZED_VIEW , TAG_ACTION , USER_ACTION , } from 'common/enums' ; import { knex } from 'connectors' ; const viewName = MATERIALIZED_VIEW . user_activity_materialized ; export const withExcludedUsers = ( { userId } ) => knex . with ( 'excluded_users' , ( builder ) => { builder . select ( 'target_id as user_id' ) . from ( 'action_user' ) . where ( { userId , action : USER_ACTION . block } ) . union ( knex . raw ( `select '${ userId }' as user_id` ) ) ; } ) ; export const makeBaseActivityQuery = ( { userId } ) => withExcludedUsers ( { userId } ) . select ( ) . from ( knex . as ( 'selected_activities' ) . select ( 'acty.*' ) . from ( 'action_user as au' ) . join ( `${ viewName } as acty` , 'acty.actor_id' , 'au.target_id' ) . leftJoin ( 'excluded_users' , 'acty.actor_id' , 'excluded_users.user_id' ) . where ( { 'excluded_users.user_id' : null , 'au.user_id' : userId , 'au.action' : USER_ACTION . follow , 'acty.type' : ActivityType . UserPublishArticleActivity , } ) . union ( [ knex . select ( 'acty.*' ) . from ( 'action_tag as at' ) . join ( `${ viewName } as acty` , 'acty.target_id' , 'at.target_id' ) . leftJoin ( 'excluded_users' , 'acty.actor_id' , 'excluded_users.user_id' ) . where ( { 'excluded_users.user_id' : null , 'at.user_id' : userId , 'at.action' : TAG_ACTION . follow , 'acty.type' : ActivityType . UserAddArticleTagActivity , } ) , ] ) ) ; export const makeCircleActivityQuery = ( { userId } ) => withExcludedUsers ( { userId } ) . select ( ) . from ( knex . as ( 'selected_activities' ) . select ( 'acty.*' ) . from ( 'action_user as au' ) . join ( `${ viewName } as acty` , 'acty.actor_id' , 'au.target_id' ) . leftJoin ( 'excluded_users' , 'acty.actor_id' , 'excluded_users.user_id' ) . where ( { 'excluded_users.user_id' : null , 'au.user_id' : userId , 'au.action' : USER_ACTION . follow , 'acty.type' : ActivityType . UserCreateCircleActivity , } ) . union ( [ knex . select ( 'acty.*' ) . from ( 'action_circle as ac' ) . join ( `${ viewName } as acty` , 'acty.target_id' , 'ac.target_id' ) . leftJoin ( 'excluded_users' , 'acty.actor_id' , 'excluded_users.user_id' ) . where ( { 'excluded_users.user_id' : null , 'ac.user_id' : userId , 'ac.action' : CIRCLE_ACTION . follow , 'acty.type' : ActivityType . UserBroadcastCircleActivity , } ) , ] ) ) ; export const makeUserDonateArticleActivityQuery = ( { userId , } ) => { const query = ( builder ) => builder . select ( ) . from ( subquery ) . where ( { rowNumber : 1 } ) . as ( 'selected_activities' ) ; const subquery = ( builder ) => builder . as ( 'activities' ) . select ( 'acty.*' ) . select ( knex . raw ( 'row_number() over (partition by node_id order by acty.id) as row_number' ) ) . from ( 'action_user as au' ) . join ( `${ viewName } as acty` , 'acty.actor_id' , 'au.target_id' ) . leftJoin ( 'excluded_users' , 'acty.actor_id' , 'excluded_users.user_id' ) . where ( { 'excluded_users.user_id' : null , 'au.user_id' : userId , 'au.action' : USER_ACTION . follow , 'acty.type' : ActivityType . UserDonateArticleActivity , } ) ; return withExcludedUsers ( { userId } ) . select ( ) . from ( query ) . orderBy ( 'created_at' , 'desc' ) ; } ; export const makeUserFollowUserActivityQuery = ( { userId , } ) => { const query = ( builder ) => builder . select ( ) . from ( subquery ) . where ( { rowNumber : 1 } ) . as ( 'selected_activities' ) ; const subquery = ( builder ) => builder . as ( 'activities' ) . select ( 'acty.*' ) . select ( knex . raw ( 'row_number() over (partition by node_id order by acty.id) as row_number' ) ) . from ( 'action_user as au' ) . join ( `${ viewName } as acty` , 'acty.actor_id' , 'au.target_id' ) . leftJoin ( 'excluded_users' , 'acty.actor_id' , 'excluded_users.user_id' ) . leftJoin ( 'excluded_followers' , 'acty.node_id' , 'excluded_followers.user_id' ) . where ( { 'excluded_users.user_id' : null , 'excluded_followers.user_id' : null , 'au.user_id' : userId , 'au.action' : USER_ACTION . follow , 'acty.type' : ActivityType . UserFollowUserActivity , } ) ; return withExcludedUsers ( { userId } ) . with ( 'excluded_followers' , ( builder ) => { builder . select ( 'target_id as user_id' ) . from ( 'action_user' ) . where ( { userId , action : USER_ACTION . follow } ) ; } ) . select ( ) . from ( query ) . orderBy ( 'created_at' , 'desc' ) ; } ; export const makeUserSubscribeCircleActivityQuery = ( { userId , } ) => { const query = ( builder ) => builder . select ( ) . from ( subquery ) . where ( { rowNumber : 1 } ) . as ( 'selected_activities' ) ; const subquery = ( builder ) => builder . as ( 'activities' ) . select ( 'acty.*' ) . select ( knex . raw ( 'row_number() over (partition by node_id order by acty.id) as row_number' ) ) . from ( 'action_user as au' ) . join ( `${ viewName } as acty` , 'acty.actor_id' , 'au.target_id' ) . leftJoin ( 'excluded_users' , 'acty.actor_id' , 'excluded_users.user_id' ) . leftJoin ( 'excluded_circles' , 'acty.node_id' , 'excluded_circles.circle_id' ) . where ( { 'excluded_users.user_id' : null , 'excluded_circles.circle_id' : null , 'au.user_id' : userId , 'au.action' : USER_ACTION . follow , 'acty.type' : ActivityType . UserSubscribeCircleActivity , } ) ; return withExcludedUsers ( { userId } ) . with ( 'excluded_circles' , ( builder ) => { builder . select ( 'target_id as circle_id' ) . from ( 'action_circle' ) . where ( { userId , action : CIRCLE_ACTION . follow } ) . union ( knex . select ( 'id as circle_id' ) . from ( 'circle' ) . where ( { owner : userId , state : CIRCLE_STATE . active } ) ) ; } ) . select ( ) . from ( query ) . orderBy ( 'created_at' , 'desc' ) ; } ; export const makeReadArticlesTagsActivityQuery = ( { userId , } ) => withExcludedUsers ( { userId } ) . select ( ) . from ( knex . as ( 'selected_recommendations' ) . select ( 'recommended.*' ) . from ( 'recommended_articles_from_read_tags_materialized as recommended' ) . leftJoin ( 'article' , 'recommended.article_id' , 'article.id' ) . leftJoin ( 'excluded_users' , 'article.author_id' , 'excluded_users.user_id' ) . where ( { 'excluded_users.user_id' : null , 'recommended.user_id' : userId , } ) ) ;