2022 02 23

2022-02-23

중개 테이블 활용한 JOIN 의문

• 문제
  • 피드테크를 가장 많이 가진 피드의 id를 순서대로 10개 알려주세요 (동점일 시 feed_id 순서대로)

• 답변 1. 피드테크에서 테크 많이 쓴 feed_id를 추출하여 이를 피드와 JOIN

  select feed.id
      from feed
      inner join 
          (select feed_id, count(*) as tech_used
              from feed_tech
              group by feed_id
              order by tech_used desc, feed_id asc 
              LIMIT 10) as feed_tech_info
          on feed.id = feed_tech_info.feed_id
      order by feed_tech_info.techused desc, feed_id asc;
  

• 답변 2. Order By 절에서 바로 feed_tech.feed_id 가 몇번 나왔는지 세서 구하기

  select distinct feed.id, feed.content
      from feed 
      inner join feed_tech 
          on feed.id = feed_tech.feed_id 
      order by 
          (select count(feed_tech.feed_id) 
          from feed_tech 
          where feed.id = feed_tech.feed_id) desc, feed.id asc LIMIT 10;
  

  - 이미 feed와 feed_tech는 합쳐져 있음 - 합쳐진 곳에서 order by 서브쿼리가 들어가기 때문에 가능한 것! <= Order by는 굉장히 나중에 실행된다는 것을 기억! - 쿼리 실행 순서를 기억해두자!

SQL 쿼리 실행 순서

참고: https://bicloud.tistory.com/20

• 쿼리 실행 작동 순서
  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. HAVING (GROUP BY에서 where과 같은 조건을 담당)
  7. SELECT
  8. DISTINCT
  9. ORDER BY
  10. TOP

JPQL로 해당 쿼리 작성하기

• 필요한 쿼리: 파트너쉽이 많은 프로젝트 순으로 반환하기
  • 1. 이런식으로 오더바이로 해버리기 - 해당 쿼리의 결과는 project * partnership 카데시안 곱으로 나옴 - select 문에 project 정보, partnership 정보도 다 때려넣기 때문 - project 중복 반환을 막기 위해 Set을 반환할 수 있음

    @Query("select project " +
        "from Project as project " +
        "join fetch project.partnerships " +
        "order by project.partnerships.size desc")
    Set<Project> findProjectsByNumberOfPartnerships();
    

    - Distinct를 쿼리에 붙이면 아래와 같은 에러가 나옴

    Hibernate: 
        select
            distinct project0_.id as id1_1_0_,
            partnershi1_.id as id1_0_1_,
            project0_.about as about2_1_0_,
            project0_.category as category3_1_0_,
            project0_.homepage as homepage4_1_0_,
            project0_.logo as logo5_1_0_,
            project0_.mainnet as mainnet6_1_0_,
            project0_.name as name7_1_0_,
            partnershi1_.project_id as project_2_0_1_,
            partnershi1_.venture_capital_id as venture_3_0_1_,
            partnershi1_.project_id as project_2_0_0__,
            partnershi1_.id as id1_0_0__ 
        from
            project project0_ 
        left outer join
            partnership partnershi1_ 
                on project0_.id=partnershi1_.project_id 
        order by
            (select
                count(partnershi2_.project_id) 
            from
                partnership partnershi2_ 
            where
                project0_.id=partnershi2_.project_id) desc
    2022-02-23 17:20:36.815  WARN 11008 --- [    Test worker] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 90068, SQLState: 90068
    2022-02-23 17:20:36.815 ERROR 11008 --- [    Test worker] o.h.engine.jdbc.spi.SqlExceptionHelper   : Order by expression "(SELECT
        COUNT(PARTNERSHI2_.PROJECT_ID)
    FROM PUBLIC.PARTNERSHIP PARTNERSHI2_
    WHERE PROJECT0_.ID = PARTNERSHI2_.PROJECT_ID)" must be in the result list in this case; SQL statement:
    select distinct project0_.id as id1_1_0_, partnershi1_.id as id1_0_1_, project0_.about as about2_1_0_, project0_.category as category3_1_0_, project0_.homepage as homepage4_1_0_, project0_.logo as logo5_1_0_, project0_.mainnet as mainnet6_1_0_, project0_.name as name7_1_0_, partnershi1_.project_id as project_2_0_1_, partnershi1_.venture_capital_id as venture_3_0_1_, partnershi1_.project_id as project_2_0_0__, partnershi1_.id as id1_0_0__ from project project0_ left outer join partnership partnershi1_ on project0_.id=partnershi1_.project_id order by (select count(partnershi2_.project_id) from partnership partnershi2_ where project0_.id=partnershi2_.project_id) desc [90068-199]