wenbao-website/server/api/ecms/article/byChannelName.ts

import { defineEventHandler, EventHandlerRequest } from 'h3';
import { DB } from '~~/server/db/DB';
import { createErrorResponse, createSuccessResponse, IResponse } from '~~/server/utils/response';
import { CommonPageResult, ICommonPageResult } from '~~/server/db/CommonModel';
import type { IArticle } from './[id]';

export default defineEventHandler<EventHandlerRequest, Promise<IResponse<ICommonPageResult<IArticle> & {
  channel_id: number;
}>>>(async (event) => {
  try {  
    const query = getQuery(event);
    const page = Number(query.page as string) || 1;
    const pageSize = Number(query.pageSize as string) || 10;
    const channelName = query.channelName as string;
    if (!channelName)
      return createErrorResponse('分类名称不能为空');

    const channelNames = channelName.split(',');
    // 1. 从pr_cms_channel表中通过name查询channel_id

    const channelIds : number[] = [];
    for (const channelName of channelNames) {
      const channel = await DB.table('pr_cms_channel')
        .where('name', channelName)
        .where('status', 'normal')
        .select('id')
        .first();
      if (channel) {
        channelIds.push(channel.id);
        const subChannelIds = (await DB.table('pr_cms_channel')
          .where('parent_id', channel.id)
          .where('status', 'normal')
          .select('id')
          .get()).map(item => item.id);
        channelIds.push(...subChannelIds);
      }
    }
    
    // 如果没有找到对应的频道，返回空数组
    if (channelIds.length === 0) 
      return createSuccessResponse(new CommonPageResult<IArticle>(undefined, [], page, pageSize, 0));

    // 2. 从pr_cms_archives表中通过channel_id查询文章
    const articles = await DB.table('pr_cms_archives')
            .whereIn('channel_id', channelIds)
            .where('status', 'normal')
            .whereNull('deletetime')
            .orderBy('weigh', 'desc')
            .orderBy('publishtime', 'desc')
            .orderBy('createtime', 'desc')
            .orderBy('id', 'asc')
            .paginate(page, pageSize);
    if (!articles) 
      return createErrorResponse('文章不存在');

    return createSuccessResponse({
      channel_id: channelIds[0],
      ...articles
    });
  } catch (error) {
    return createErrorResponse(error);
  }
});