[희락] Events server쪽 보완

jaeyeon_lee·2024년 4월 16일

YDK프로젝트 희락

희락

목록 보기

6/8

3달치를 불러오고 전체를 불러오는게 너무 비효율적이라는 코드 리뷰를 받았당.. 그래서 한가지 쿼리로 합치기로 결정!

Backend

event.ctrl.ts

변경 전 코드

export const getDateEvents = asyncHandler(async (req, res) => {
  // console.log("Event req.query:",req.query)
  const {year , month, date} = req.query as { year: string, month: string, date: string } ;
  // console.log("year,month,date",year,month,date)

  const result = await findDateEvents(year,month,date);
  res.json(result.rows);
});

export const getMonthEvents = asyncHandler(async (req, res) => {
  console.log("Event Moth req.query:",req.query)
  const {year , month} = req.query as { year: string, month: string} ;
  const result = await findMonthEvents(year,month);
  res.json(result.rows);
});

변경 후 코드

export const getEvents = asyncHandler(async (req, res) => {
  const { from, to, limit } = req.query as {
    from: string;
    to: string;
    limit: string;
  };
  let limitNumber;
  if (limit) {
    limitNumber = Number(limit);
  }
  const result = await eventService.getEvents(from, to, limitNumber);
  res.json(result);
});

from, to, limit를 입력받고 전달해주는 형식으로 바꿈! from이 시작날짜, to가 종료날짜!

event.db.ts

변경 전 코드

export const findDateEvents = async (year: string, month: string, date: string) => {
  const startDate = new Date(`${year}-${month}-${date}`);

  const result = await db.query<Event>('SELECT title,start_date FROM events WHERE start_date >= $1 LIMIT 5', [startDate]);
  console.log("find event result:", result)
  return result;
};

export const findMonthEvents = async (year: string, month: string) => {
  const startDate = new Date(`${year}-${month}-01`);

  // 시작 날짜로부터 앞뒤 한 달씩 총 세 달치의 범위 계산
  const startPreviousMonth = new Date(startDate);
  startPreviousMonth.setMonth(startPreviousMonth.getMonth() - 1);

  const startNextMonth = new Date(startDate);
  startNextMonth.setMonth(startNextMonth.getMonth() + 1);

  // 각 범위의 마지막 날짜를 계산
  const lastDayOfPreviousMonth = new Date(startPreviousMonth.getFullYear(), startPreviousMonth.getMonth() + 1, 0);
  const lastDayOfCurrentMonth = new Date(startDate.getFullYear(), startDate.getMonth() + 1, 0);
  const lastDayOfNextMonth = new Date(startNextMonth.getFullYear(), startNextMonth.getMonth() + 1, 0);

  const result = await db.query<Event>('SELECT * FROM events WHERE start_date >= $1 AND start_date <= $2 OR start_date >= $3 AND start_date <= $4 OR start_date >= $5 AND start_date <= $6 ORDER BY start_date ASC',
    [startPreviousMonth, lastDayOfPreviousMonth, startDate, lastDayOfCurrentMonth, startNextMonth, lastDayOfNextMonth]);

  console.log("find three months events result:", result);
 return result
};

변경 후 코드

export const findEvents = async (
  from: Date,
  to: Date | undefined,
  limit: number
) => {
  const sql = `SELECT * FROM ${TABLE_NAMES.events}
    WHERE start_date >= '${from.toISOString()}' ${
    to ? `AND start_date < '${to.toISOString()}'` : ""
  } LIMIT ${limit}`;
  console.log("find event query=", sql);
  return (await db.query<Event>(sql)).rows;
}

event.route.ts

변경 전 코드

.get( (req, res, next) => {
    // 클라이언트가 날짜를 제공한 경우에는 getDateEvents 호출
    if (req.query.date) {
      return eventCtrl.getDateEvents(req, res, next);
    } else {
      // 그 외의 경우에는 getMonthEvents 호출
      // return eventCtrl.getAllEvents(req, res, next)
      return eventCtrl.getMonthEvents(req,res,next);
    }
  })

변경 후 코드

.get(eventCtrl.getEvents)

event.service.ts

from ~ to에 해당하는 애들을 찾는데, to값은 전달되지 않을 수도 있기 때문에 해당 코드를 추가해줌!

export const getEvents = async (from: string, to: string, limit = 100) => {
  try {
    let toDate;
    let fromDate = new Date(from);
    if (to) {
      toDate = new Date(to);
    }
    return await eventDb.findEvents(fromDate, toDate, limit);
  } catch (error) {
    throw error;
  }
};

frontend

event.api.ts

변경 전 코드

export const getMonthEvents = async (year : string, month: string) => {
  const { data } = await axios.get(
    `${apiPath}/events?year=${year}&month=${month}`
  console.log('getMonthEvents: ', data)
  return data;
}

변경 후 코드

export const getEvents = async (
  from: string,
  to: string = "",
  limit: number
) => {
  const { data } = await axios.get(
    `${apiPath}/events?from=${from}&to=${to}&limit=${limit}`
 console.log("get events: ", data);
  return data;
}

InfoEvent.tsx

변경 전 코드

const fetchEvents = async (viewMonth: string) => {
    try {
      console.log("fetchEvents:", viewMonth);
      const today = new Date(viewMonth);
      const fromDate = addMonth(today, -1);
      const toDate = addMonth(today, 2);
      const year = viewMonth.split("-")[0];
      const month = viewMonth.split("-")[1];
      const events = await getMonthEvents(year, month);

변경 후 코드

const fetchEvents = async (viewMonth: string) => {
    try {
      console.log("fetchEvents:", viewMonth);
      const today = new Date(viewMonth);
      const fromDate = addMonth(today, -1);
      const toDate = addMonth(today, 2);
      const year = viewMonth.split("-")[0];
      const month = viewMonth.split("-")[1];
      const events = await getEvents(
        getDateString(fromDate),
        getDateString(toDate),
        100
      );

이때 한가지 문제점이 있었는데 시작날짜 기준으로 조회해오다보니 시작날짜~종료날짜가 엄청 긴 일정일때 가져오지 못하는 오류가 생겼다.. 그래서 해결방안을 생각해보았고 가져오지 않을 부분 (여집합)을 생각해서 쿼리를 수정하기로 함!

event.db.ts

변경 전 코드

const sql = `SELECT * FROM ${TABLE_NAMES.events}
    WHERE start_date >= '${from.toISOString()}' ${
    to ? `AND start_date < '${to.toISOString()}'` : ""
  } LIMIT ${limit}`;

변경 후 코드

const sql = `SELECT * FROM ${TABLE_NAMES.events} WHERE (start_date, end_date) NOT IN (select start_date, end_date from ${TABLE_NAMES.events} where end_date < '${from.toISOString()}' ${
    to ? `OR start_date >= '${to.toISOString()}'` : ""
  }) ORDER BY start_date LIMIT ${limit}`;