+ static async getStats () {
+
+ function getActiveVideoChannels (days: number) {
+ const options = {
+ type: QueryTypes.SELECT as QueryTypes.SELECT,
+ raw: true
+ }
+
+ const query = `
+SELECT COUNT(DISTINCT("VideoChannelModel"."id")) AS "count"
+FROM "videoChannel" AS "VideoChannelModel"
+INNER JOIN "video" AS "Videos"
+ON "VideoChannelModel"."id" = "Videos"."channelId"
+AND ("Videos"."publishedAt" > Now() - interval '${days}d')
+INNER JOIN "account" AS "Account"
+ON "VideoChannelModel"."accountId" = "Account"."id"
+INNER JOIN "actor" AS "Account->Actor"
+ON "Account"."actorId" = "Account->Actor"."id"
+AND "Account->Actor"."serverId" IS NULL
+LEFT OUTER JOIN "server" AS "Account->Actor->Server"
+ON "Account->Actor"."serverId" = "Account->Actor->Server"."id"`
+
+ return VideoChannelModel.sequelize.query<{ count: string }>(query, options)
+ .then(r => parseInt(r[0].count, 10))
+ }
+
+ const totalLocalVideoChannels = await VideoChannelModel.count()
+ const totalLocalDailyActiveVideoChannels = await getActiveVideoChannels(1)
+ const totalLocalWeeklyActiveVideoChannels = await getActiveVideoChannels(7)
+ const totalLocalMonthlyActiveVideoChannels = await getActiveVideoChannels(30)
+ const totalHalfYearActiveVideoChannels = await getActiveVideoChannels(180)
+
+ return {
+ totalLocalVideoChannels,
+ totalLocalDailyActiveVideoChannels,
+ totalLocalWeeklyActiveVideoChannels,
+ totalLocalMonthlyActiveVideoChannels,
+ totalHalfYearActiveVideoChannels
+ }
+ }
+
+ static listForApi (parameters: {
+ actorId: number
+ start: number
+ count: number
+ sort: string
+ }) {
+ const { actorId } = parameters
+
+ const query = {
+ offset: parameters.start,
+ limit: parameters.count,
+ order: getSort(parameters.sort)
+ }
+
+ return VideoChannelModel
+ .scope({
+ method: [ ScopeNames.FOR_API, { actorId } as AvailableForListOptions ]
+ })
+ .findAndCountAll(query)
+ .then(({ rows, count }) => {
+ return { total: count, data: rows }
+ })
+ }
+
+ static listLocalsForSitemap (sort: string): Promise<MChannelActor[]> {
+ const query = {
+ attributes: [ ],
+ offset: 0,
+ order: getSort(sort),
+ include: [
+ {
+ attributes: [ 'preferredUsername', 'serverId' ],
+ model: ActorModel.unscoped(),
+ where: {
+ serverId: null
+ }
+ }
+ ]
+ }
+
+ return VideoChannelModel
+ .unscoped()
+ .findAll(query)
+ }
+
+ static searchForApi (options: {
+ actorId: number
+ search: string
+ start: number
+ count: number
+ sort: string
+ }) {
+ const attributesInclude = []
+ const escapedSearch = VideoModel.sequelize.escape(options.search)
+ const escapedLikeSearch = VideoModel.sequelize.escape('%' + options.search + '%')
+ attributesInclude.push(createSimilarityAttribute('VideoChannelModel.name', options.search))
+