[BE] V1 조회 API에서 발생하는 쿼리를 정리한다.

[seokjin8678]

✨ 세부 내용

V1 조회 API에서 발생하는 쿼리를 정리합니다.

커맨트로 남기는 템플릿은 다음과 같습니다.

## Swagger Summary
### Swagger Description
### API URL

sql code block start
select ...
sql code block end

---

(의견)

해당 쿼리에 남길 의견은 수정을 사용하여 하단에 추가합니다.

---

공통으로 API 문서화에 summary, description 형식 통일이 필요할 것 같네요!

또한 해당 쿼리 실행 계획 조회하여 어떤 index를 타는지, cost가 얼마인지 분석된 결과도 있으면 좋을 것 같습니다!
(cost는 환경마다 다르니, dev 서버의 db를 조회해서 적어야 합니다!)
ex)
/api/v1/schools/{schoolId}/festivals?isPast=true

⏰ 예상 소요 시간

5시간

[seokjin8678]

학교 검색

곧 시작하는 축제일을 포함된 학교를 검색한다.

/api/v1/search/schools

select
    s1_0.id,
    s1_0.name,
    s1_0.logo_url 
from
    school s1_0 
where
    s1_0.name like '%서울%' escape '!' 
order by
    s1_0.name limit 11
    
select
    f1_0.school_id,
    min(f1_0.start_date) 
from
    festival f1_0 
where
    f1_0.school_id in (1,2,3) 
    and f1_0.end_date>='2024-04-23' 
group by
    f1_0.school_id

[seokjin8678]

축제 검색

축제를 검색한다. ~대 혹은 ~대학교로 끝날 시 대학교 축제 검색이며 그 외의 경우는 아티스트 기반 축제 검색입니다.

/api/v1/search/festivals

# 대, 대학교로 끝나지 않은 경우
select
    f1_0.id,
    f1_0.name,
    f1_0.start_date,
    f1_0.end_date,
    f1_0.poster_image_url,
    f3_0.artist_info 
from
    artist a1_0 
join
    stage_artist s1_0 
        on a1_0.name like '%아이유%' escape '!' 
        and s1_0.artist_id=a1_0.id 
join
    stage s2_0 
        on s2_0.id=s1_0.stage_id 
join
    festival f1_0 
        on f1_0.id=s2_0.festival_id 
join
    festival_query_info f3_0 
        on f1_0.id=f3_0.festival_id 
where
    a1_0.name like '%아이유%' escape '!'

# 대, 대학교로 끝나는 경우
select
    f1_0.id,
    f1_0.name,
    f1_0.start_date,
    f1_0.end_date,
    f1_0.poster_image_url,
    f2_0.artist_info 
from
    festival f1_0 
join
    festival_query_info f2_0 
        on f1_0.id=f2_0.festival_id 
where
    f1_0.name like '%서울대%' escape '!'

---

stage_artist join 시 a1_0.name like '%아이유%' escape '!' 해당 조건 필요한지 확인할 것

[seokjin8678]

아티스트 목록 검색 조회

키워드로 아티스트 목록을 검색한다

/api/v1/search/artists

select
    a1_0.id,
    a1_0.name,
    a1_0.profile_image_url 
from
    artist a1_0 
where
    a1_0.name like '%아이유%' escape '!' 
order by
    a1_0.name

select
    s1_0.artist_id,
    s2_0.start_time 
from
    stage_artist s1_0 
join
    stage s2_0 
        on s2_0.id=s1_0.stage_id 
where
    s1_0.artist_id=3 
    and s2_0.start_time>='2024-04-23T00:00'

[seokjin8678]

학교 상세 조회

학교와 해당하는 소셜미디어 정보를 함께 조회한다.

/api/v1/schools/{id}

select
    s1_0.id,
    s1_0.name,
    s1_0.logo_url,
    s1_0.background_image_url,
    s2_0.media_type,
    s2_0.name,
    s2_0.logo_url,
    s2_0.url 
from
    school s1_0 
left join
    social_media s2_0 
        on s2_0.owner_id=1 
        and s2_0.owner_type='SCHOOL' 
where
    s1_0.id=1

[seokjin8678]

학교 상세 조회

해당 학교의 축제들을 페이징하여 조회한다.

/api/v1/schools/{id}/festivals

# ?isPast=false
select
    f1_0.id,
    f1_0.name,
    f1_0.start_date,
    f1_0.end_date,
    f1_0.poster_image_url,
    f2_0.artist_info 
from
    festival f1_0 
left join
    festival_query_info f2_0 
        on f2_0.festival_id=f1_0.id 
where
    f1_0.school_id=1 
    and f1_0.end_date>='2024-04-23'
order by
    f1_0.start_date limit 11

# ?isPast=true
select
    f1_0.id,
    f1_0.name,
    f1_0.start_date,
    f1_0.end_date,
    f1_0.poster_image_url,
    f2_0.artist_info 
from
    festival f1_0 
left join
    festival_query_info f2_0 
        on f2_0.festival_id=f1_0.id 
where
    f1_0.school_id=1
    and f1_0.end_date<'2024-04-23' 
order by
    f1_0.end_date desc limit 11

---

SchoolV1Controller의 @Operation 수정해야함

[seokjin8678]

축제 목록 조회

축제 목록를 조건별로 조회한다. PROGRESS: 진행 중, PLANNED: 진행 예정, END: 종료, 기본값 -> 진행 중, limit의 크기는 0 < limit < 21 이며 기본 값 10이다.

/api/v1/festivals

# ?filter=PROGRESS
select
    f1_0.id,
    f1_0.name,
    f1_0.start_date,
    f1_0.end_date,
    f1_0.poster_image_url,
    s1_0.id,
    s1_0.name,
    f2_0.artist_info 
from
    festival f1_0 
join
    school s1_0 
        on s1_0.id=f1_0.school_id 
join
    festival_query_info f2_0 
        on f2_0.festival_id=f1_0.id 
where
    f1_0.start_date<='2024-04-23' 
    and f1_0.end_date>='2024-04-23'
order by
    f1_0.start_date desc,
    f1_0.id limit 11

# ?filter=PLANNED
select
    f1_0.id,
    f1_0.name,
    f1_0.start_date,
    f1_0.end_date,
    f1_0.poster_image_url,
    s1_0.id,
    s1_0.name,
    f2_0.artist_info 
from
    festival f1_0 
join
    school s1_0 
        on s1_0.id=f1_0.school_id 
join
    festival_query_info f2_0 
        on f2_0.festival_id=f1_0.id 
where
    f1_0.start_date>'2024-04-23'
order by
    f1_0.start_date,
    f1_0.id limit 11

# ?filter=END
select
    f1_0.id,
    f1_0.name,
    f1_0.start_date,
    f1_0.end_date,
    f1_0.poster_image_url,
    s1_0.id,
    s1_0.name,
    f2_0.artist_info 
from
    festival f1_0 
join
    school s1_0 
        on s1_0.id=f1_0.school_id 
join
    festival_query_info f2_0 
        on f2_0.festival_id=f1_0.id 
where
    f1_0.end_date<'2024-04-23' 
order by
    f1_0.end_date desc limit 11

[seokjin8678]

특정 축제 상세 조회

특정 축제의 상세 정보를 조회한다.

/api/v1/festivals/{id}

select
    f1_0.id,
    f1_0.name,
    s1_0.id,
    s1_0.name,
    f1_0.start_date,
    f1_0.end_date,
    f1_0.poster_image_url,
    s3_0.media_type,
    s3_0.name,
    s3_0.logo_url,
    s3_0.url,
    s4_0.id,
    s4_0.start_time,
    s5_0.artist_info 
from
    festival f1_0 
join
    school s1_0 
        on s1_0.id=f1_0.school_id 
left join
    social_media s3_0 
        on s3_0.owner_id=s1_0.id 
        and s3_0.owner_type='SCHOOL'
left join
    stage s4_0 
        on s4_0.festival_id=f1_0.id 
left join
    stage_query_info s5_0 
        on s5_0.stage_id=s4_0.id 
where
    f1_0.id=258

[seokjin8678]

(누락)

아티스트의 정보를 조회한다.

/api/v1/artists/{id}

select
    a1_0.id,
    a1_0.name,
    a1_0.profile_image_url,
    a1_0.background_image_url,
    cast(s1_0.media_type as char),
    s1_0.name,
    s1_0.logo_url,
    s1_0.url 
from
    artist a1_0 
left join
    social_media s1_0 
        on s1_0.owner_id=a1_0.id 
        and s1_0.owner_type='ARTIST'
where
    a1_0.id=1

---

누락된 Swagger Summary 추가할 것
cast(s1_0.media_type as char) 발생하지 않게 DTO 필드 ENUM 반환하도록 할 것

[seokjin8678]

아티스트 축제 조회

아티스트가 참석한 축제를 조회한다. isPast 값으로 종료 축제 와 진행, 예정 축제를 구분 가능하다

/api/v1/artists/{id}/festivals

# ?isPast=false
select
    f1_0.id,
    f1_0.name,
    f1_0.start_date,
    f1_0.end_date,
    f1_0.poster_image_url,
    f3_0.artist_info 
from
    stage_artist s1_0 
join
    stage s2_0 
        on s1_0.artist_id=1 
        and s2_0.id=s1_0.stage_id 
join
    festival f1_0 
        on f1_0.id=s2_0.festival_id 
left join
    festival_query_info f3_0 
        on f1_0.id=f3_0.festival_id 
where
    f1_0.end_date>='2024-04-23' 
order by
    f1_0.start_date,
    f1_0.id limit 11

# ?isPast=true
select
    f1_0.id,
    f1_0.name,
    f1_0.start_date,
    f1_0.end_date,
    f1_0.poster_image_url,
    f3_0.artist_info 
from
    stage_artist s1_0 
join
    stage s2_0 
        on s1_0.artist_id=1 
        and s2_0.id=s1_0.stage_id 
join
    festival f1_0 
        on f1_0.id=s2_0.festival_id 
left join
    festival_query_info f3_0 
        on f1_0.id=f3_0.festival_id 
where
    f1_0.end_date<'2024-04-23'
order by
    f1_0.end_date desc limit 11