Exclude channel notes from featured polls (#13838)

* feat(backend): add `channelId` to `MiPoll` as a Denormalized field

* feat(backend): option to exclude polls in channels

* chore: exclude channel notes from featured polls

* docs(changelog): みつけるのアンケート欄にてチャンネルのアンケートが含まれてしまう問題を修正

* fix: missing license header

6 files changed, 43 insertions, 0 deletions

diff --git a/packages/backend/migration/1716129964060-ChannelIdDenormalizedForMiPoll.js b/packages/backend/migration/1716129964060-ChannelIdDenormalizedForMiPoll.js
new file mode 100644
index 0000000000..f736378c04
--- /dev/null
+++ b/packages/backend/migration/1716129964060-ChannelIdDenormalizedForMiPoll.js
@@ -0,0 +1,21 @@
+/*
+ * SPDX-FileCopyrightText: syuilo and misskey-project
+ * SPDX-License-Identifier: AGPL-3.0-only
+ */
+
+export class ChannelIdDenormalizedForMiPoll1716129964060 {
+    name = 'ChannelIdDenormalizedForMiPoll1716129964060'
+
+    async up(queryRunner) {
+        await queryRunner.query(`ALTER TABLE "poll" ADD "channelId" character varying(32)`);
+        await queryRunner.query(`COMMENT ON COLUMN "poll"."channelId" IS '[Denormalized]'`);
+        await queryRunner.query(`CREATE INDEX "IDX_c1240fcc9675946ea5d6c2860e" ON "poll" ("channelId") `);
+        await queryRunner.query(`UPDATE "poll" SET "channelId" = "note"."channelId" FROM "note" WHERE "poll"."noteId" = "note"."id"`);
+    }
+
+    async down(queryRunner) {
+        await queryRunner.query(`DROP INDEX "public"."IDX_c1240fcc9675946ea5d6c2860e"`);
+        await queryRunner.query(`COMMENT ON COLUMN "poll"."channelId" IS '[Denormalized]'`);
+        await queryRunner.query(`ALTER TABLE "poll" DROP COLUMN "channelId"`);
+    }
+}
diff --git a/packages/backend/src/core/NoteCreateService.ts b/packages/backend/src/core/NoteCreateService.ts
index 32104fea90..e5580f36d1 100644
--- a/packages/backend/src/core/NoteCreateService.ts
+++ b/packages/backend/src/core/NoteCreateService.ts
@@ -473,6 +473,7 @@ export class NoteCreateService implements OnApplicationShutdown {
 						noteVisibility: insert.visibility,
 						userId: user.id,
 						userHost: user.host,
+						channelId: insert.channelId,
 					});
 
 					await transactionalEntityManager.insert(MiPoll, poll);
diff --git a/packages/backend/src/models/Poll.ts b/packages/backend/src/models/Poll.ts
index c2693dbb19..ca985c8b24 100644
--- a/packages/backend/src/models/Poll.ts
+++ b/packages/backend/src/models/Poll.ts
@@ -8,6 +8,7 @@ import { noteVisibilities } from '@/types.js';
 import { id } from './util/id.js';
 import { MiNote } from './Note.js';
 import type { MiUser } from './User.js';
+import type { MiChannel } from "@/models/Channel.js";
 
 @Entity('poll')
 export class MiPoll {
@@ -58,6 +59,14 @@ export class MiPoll {
 		comment: '[Denormalized]',
 	})
 	public userHost: string | null;
+
+	@Index()
+	@Column({
+		...id(),
+		nullable: true,
+		comment: '[Denormalized]',
+	})
+	public channelId: MiChannel['id'] | null;
 	//#endregion
 
 	constructor(data: Partial<MiPoll>) {
diff --git a/packages/backend/src/server/api/endpoints/notes/polls/recommendation.ts b/packages/backend/src/server/api/endpoints/notes/polls/recommendation.ts
index ba38573065..4fd6f8682d 100644
--- a/packages/backend/src/server/api/endpoints/notes/polls/recommendation.ts
+++ b/packages/backend/src/server/api/endpoints/notes/polls/recommendation.ts
@@ -32,6 +32,7 @@ export const paramDef = {
 	properties: {
 		limit: { type: 'integer', minimum: 1, maximum: 100, default: 10 },
 		offset: { type: 'integer', default: 0 },
+		excludeChannels: { type: 'boolean', default: false },
 	},
 	required: [],
 } as const;
@@ -86,6 +87,12 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 			query.setParameters(mutingQuery.getParameters());
 			//#endregion
 
+			//#region exclude channels
+			if (ps.excludeChannels) {
+				query.andWhere('poll.channelId IS NULL');
+			}
+			//#endregion
+
 			const polls = await query
 				.orderBy('poll.noteId', 'DESC')
 				.limit(ps.limit)
diff --git a/packages/frontend/src/pages/explore.featured.vue b/packages/frontend/src/pages/explore.featured.vue
index b5c8e70166..cfdb235d3a 100644
--- a/packages/frontend/src/pages/explore.featured.vue
+++ b/packages/frontend/src/pages/explore.featured.vue
@@ -29,6 +29,9 @@ const paginationForPolls = {
 	endpoint: 'notes/polls/recommendation' as const,
 	limit: 10,
 	offsetMode: true,
+	params: {
+		excludeChannels: true,
+	},
 };
 
 const tab = ref('notes');
diff --git a/packages/misskey-js/src/autogen/types.ts b/packages/misskey-js/src/autogen/types.ts
index 1b9f1304d5..302587ccfa 100644
--- a/packages/misskey-js/src/autogen/types.ts
+++ b/packages/misskey-js/src/autogen/types.ts
@@ -21019,6 +21019,8 @@ export type operations = {
           limit?: number;
           /** @default 0 */
           offset?: number;
+          /** @default false */
+          excludeChannels?: boolean;
         };
       };
     };