[SQLAlchemy] join함수 사용기

GitHub - SweetCase-Cobalto/cloudmodular: NAS Cloud Hosting Service Application [구현중]

CloudModular는 Nextcloud 처럼 사용자가 직접 자신의 개인 서버에 설치해서 사용하는 클라우드 서비스 프로젝트 입니다. microcloudchip.log는 해당 프로젝트를 진행하면서 배웠거나 깨달은 부분을 일기처럼 적는 카테고리로 일부 틀린 내용이 있을 수 있습니다. 해당 카테고리에 등록된 포스트들 중 일부는 추가적인 학습 후, 프로그래밍 관련 카테고리에 해당 주제와 관련해서 포스팅이 됩니다.

TMI: Nextcloud는 PHP로 구현되어있습니다. 반면에 CloudModular는 FastAPI와 ReactJS로 구현되고 있습니다.

개요

프로젝트를 시작한지 한달 반, 백엔드 쪽 구현은 이제 거의 마무리가 되어 간다. 프로젝트의 코어 기능들은 전부 구현했고 마지막으로 품질 개선만 이틀 정도만 진행하면 바로 프론트엔드를(ReactJS) 작업하려고 한다. 정확히는 바로 코딩은 하지 않고 디자인부터 대충 그려놓은 다음 그걸 기반으로 구현할 예정이다.

마지막 남은 기능인 파일/디렉토리 검색 기능을 구현하는 과제가 있었다. 특정 필터링과 정렬을 기준으로 검색 대상인 파일들의 "정보", 그러니까 파일 이름이나 파일/디렉토리 여부 등 이런것들을 리스트로 가져오는 기능이다. 작년에 비슷한 컨셉으로 진행했던 Microcloudchip-NATURAL 때는 DB를 사용하지 않고 일일히 디렉토리를 뒤져가며 데이터를 수집했었다. 그렇기 때문에 OS와 다이렉트로 접근하는 일이 무지하게 많았고, 그 결과 파일/디렉토리가 GB 단위로 많아졌을 때는 성능이 완전히 개차반되는 결과를 낳았다.

하지만 이번에는 데이터를 생성하거나 업로드하면 해당 정보를 DB에 저장하는 로직을 설계했다. 비록 동기화 과정이나 비상시에 대한 대처 로직을 추가적으로 구현해야 하는 상황이 있지만, 성능을 올릴수만 있다면야 이정도 코딩은 공부한다 생각하고 즐길려고 한다. 탐색 프로세스도 이제 직접 디렉토리에 접근하는 일은 없다. DB에서만 쿼리문 돌리면 된다.

여튼 이번 포스트의 주제는 프로젝트 소개가 아니니 각설은 여기까지만 하고 본론으로 넘어가겠다.

RDB 구조

github readme에도 적어놓았지만 해당 프로젝트의 ER은 대강 이렇다.

DataInfo는 해당 파일이나 디렉토리에 대한 정보다. root는 상위 디렉토리, is_favorite는 즐겨찾기 여부, is_dir은 디렉토리 여부다. False면 파일인 셈. DataTag와 Tag는 파일에 달려있는 말 그대로 Tag, DataShared는 해당 데이터가 공유되어 있는지에 대한 테이블이다. 공유기간은 created와 .env에 박혀 있는 공유 기간으로 계산한다.

탐색 Topic

내가 구현해야 할 데이터 탐색 필터또는 정렬 기준은 아래와 같다.

1. [필수] 검색 대상의 사용자
2. [필수] 검색 디렉토리 위치
3. Recursive 여부: 해당 루트 위에서 뿐만 아니라 하위 디렉토리까지 전부 조사한다.
4. 즐겨찾기 여부: 즐겨찾기가 설정되어 있는 데이터만 출력
5. 공유 상태 여부: 공유가 되어 있는 데이터만 출력
6. 태그: 검색 대상의 태그 리스트에 하나라도 포함이되면 출력
7. 이름 순 정렬
8. 생성 순 정렬

이 8가지를 하나만 쓰는 게 아니라 여러 필터를 조합해서 사용해야 한다. 예를 들어 공유와 즐겨찾기 두개가 동시에 설정되어 있는 파일 및 디렉토리를 해당 루트 위치로부터 Recursive하게 조사한다던가.(1 & 2 & 3 & 4 & 5).

이거 자체는 어렵지 않다 조건에 맞춰서 filter()와 그에 해당하는 조건문을 추가해주면 그만이다. 근데 이건 어디까지나 테이블 하나만 사용했을 때가 쉽지 두개 이상이면 Join문이 들어가야 한다.

Output

해당 API에 대한 리턴값은 Json형태에 List로 출력된다.

{
    "id": 데이터 id
    "root": 해당 데이터가 위치해 있는 디렉토리
    "is_dir": 디렉토리인 경우 True, 파일인 경우 False,
    "name": 데이터 이름,
    "is_favorite": 즐겨찾기가 설정되어 있으면 True, 아니면 False,
    'shared_id": 공유안되어 있으면 -1 있으면 해당 파일에 대한 공유 코드
}

리턴값을 생성하기 위해 DataInfo를 사용하고, SharedID를 추가하기 위해 DataShared의 데이터도 사용한다. 하지만 DataInfo에 대응되는 DataShared가 없으면 SharedID를 -1로 설정한다.

정리

쿼리르 돌릴때 User를 제외한 모든 테이블을 Join해서 사용해야 한다. user_id는 어차피 API input값으로 들어오기 때문에 User Table을 사용할 일이 없다.

웬만해선 Inner Join을 쓰되 DataShared같은 경우는 공유 여부 필터링을 사용하지 않으면 DataShared를 갖고 있지 않은 DataInfo도 출력해야 하기 때문에 Innter Join이 아닌 Left Outer Join을 사용해야 한다.

Query()

쿼리문을 돌릴 때 항상 session.query()에 대상 테이블들을 집어넣고 시작한다.

session.query(DataInfo)

이렇게 코드를 작성하고 all()을 실행하면 DataInfo 테이블에 들어있는 모든 레코드들을 출력한다는 사실은 누구나 다 알고 있을 것이다. SQL문을 뜯어보면

SELECT
    datainfo.id AS datainfo_id,
    datainfo.root AS datainfo_root,
    datainfo.name AS datainfo_name,
    datainfo.is_dir AS datainfo_is_dir,
    datainfo.created AS datainfo_created,
    datainfo.is_favorite AS datainfo_is_favorite,
    datainfo.user_id AS datainfo_user_id
FROM datainfo

위와 같다. select * from datainfo;와 일치하다고 보면 된다. 그럼 이건 어떨까?

session.query(DataInfo, DataShared)

이번엔 테이블 두개가 붙어 있다. 하지만 where문이나 join문 같은건 없다 SQL문을 찍어보면

SELECT
    datainfo.id AS datainfo_id,
    datainfo.root AS datainfo_root,
    datainfo.name AS datainfo_name,
    datainfo.is_dir AS datainfo_is_dir,
    datainfo.created AS datainfo_created,
    datainfo.is_favorite AS datainfo_is_favorite,
    datainfo.user_id AS datainfo_user_id,
    
    "dataShared".id AS "dataShared_id",
    "dataShared".share_started AS "dataShared_share_started",
    "dataShared".is_active AS "dataShared_is_active",
    "dataShared".datainfo_id AS "dataShared_datainfo_id"
FROM datainfo, "dataShared"

DataInfo와 DataShared가 From 앞에 붙어있고 아무것도 없기 때문에 where문 조차도 없다 이 상태에서 all()을 수행하면

[
 (<0x7f951cd8dd90>, <0x7f951cd8de20>),
 (<0x7f951cd8dd90>, <0x7f951cd8dac0>),
 (<0x7f951cd8da30>, <0x7f951cd8de20>),
 (<0x7f951cd8da30>, <0x7f951cd8dac0>),
 (<0x7f951cd8de80>, <0x7f951cd8de20>),
 (<0x7f951cd8de80>, <0x7f951cd8dac0>),
 (<0x7f951cd8dee0>, <0x7f951cd8de20>),
 (<0x7f951cd8dee0>, <0x7f951cd8dac0>),
 (<0x7f951cd8df40>, <0x7f951cd8de20>),
 (<0x7f951cd8df40>, <0x7f951cd8dac0>)
]

위와 같은 결과가 나온다. 결과가 복잡해서 일부 텍스트를 지웠는데 리스트의 요소에서 왼쪽 데이터는 DataInfo, 오른쪽은 DataShared다. 데이터베이스에서 DataInfo의 레코드는 5개, DataShared의 데이터는 2개가 되고, 아까 ER에서도 보았듯이. DataShared의 컬럼에 DataInfo의 pk값에 Unique가 걸려있기 때문에 1대1 관계다.

아무것도 조건문이 붙어있지 않기 때문에 하나의 DataInfo에 모든 DataShared 데이터가 전부 붙어서 출력되었다. 즉

DataInfo의 갯수 x DataShared의 갯수  = 5 x 2 = 10개가 출력된 것이다.

그러나 보통 이러한 방식은 똑같은 데이터가 출력되었기 때문에 메모리 낭비가 된다. 실제로 쿼리문을 날리고 보면 아래와 같은 경고창이뜬다.

SAWarning: SELECT statement has a cartesian product between FROM element(s)
"datainfo" and FROM element "dataShared".  Apply join condition(s)
between each element to resolve.
    print(query.all())

말 그대로 제발좀 Join문 써달라는 뜻이다.

그리고 맨 첫째줄에 보면 certestain이라는 단어가 보이는데 검색해 봤더니 데카르트 좌표계라고 나온다 x,y좌표를 의미하는 건데 아마 x는 DataInfo, y는 DataShared를 의미하는 것 같다.

filter를 이용한 DataInfo, Shared 값 찾기

이러한 문제는 조건문만 추가한다면 해결이 된다.

query = session.query(DataInfo, DataShared)
query = query.filter(DataInfo.id == DataShared.datainfo_id)

SELECT ... 생략 ... FROM datainfo, "dataShared" WHERE datainfo.id = "dataShared".datainfo_id

이번엔 id 일치 조건문을 추가했다. 사실상 Inner Join이나 다름없고 아까 DataShared의 레코드가 2개밖에 없기 때문에 이에 대응하는 DataInfo와 함께 두 개의 레코드가 출력이 된다.

[(<DataInfo object at 0x7f9d5a0acb80>, <DataShared object at 0x7f9d5a0acd90>),
(<DataInfo object at 0x7f9d5a0acc70>, <DataShared object at 0x7f9d5a0acd00>)]

예상대로 두 개의 레코드가 출력이 되었다.

그런데 문제는 아까 위에서 설명했듯이. DataInfo와 DataShared의 관계는 Inner Join이 아니라 Left Outer Join을 사용해야 한다. 공유되지 않은 (DataShared를 갖고 있지 않은) DataInfo도 출력해야 하기 때문이다. 그리고 저걸 쓴다고 해도

session.query(DataInfo, DataShared, DataTag, Tag)

테이블을 전부 사용하는 건 좋은데, 정작 출력해야 할 데이터는 DataInfo와  DataShared 뿐이다. DataTag와 Tag까지는 필요 없다 불필요한 데이터까지 출력하는 셈이다. 하지만 join()을 쓴다면 다른 테이블을 참조한다고 굳이 query()안에 집어넣을 필요가 없다. 이제 join()을 사용할 때다.

그리고 이걸 두고 돌리면 원하는 결과가 나오지 않는다.

Join() - Inner Join

join()의 사용은 간단하다. 첫 번째 파라미터에 참조할 모델을 입력하고 두번 째 줄에 Join 조건문을 추가한다.

query = session.query(DataInfo)
uery = query.join(DataShared, DataInfo.id == DataShared.datainfo_id)

SELECT
 datainfo.id AS datainfo_id,
 datainfo.root AS datainfo_root,
 datainfo.name AS datainfo_name,
 datainfo.is_dir AS datainfo_is_dir,
 datainfo.created AS datainfo_created,
 datainfo.is_favorite AS datainfo_is_favorite,
 datainfo.user_id AS datainfo_user_id
FROM datainfo
 JOIN "dataShared"
 ON datainfo.id = "dataShared".datainfo_id

[<apps.storage.models.DataInfo object at 0x7ff1511680a0>,
<apps.storage.models.DataInfo object at 0x7ff1511682e0>]

join()을 사용했기 때문에 SQL에서도 JOIN이 들어간 걸 불 수가 있고 아까 filter()를 사용했을 때 처럼 두 개의 데이터가 출력되었다. 단 query()에 DataShared를 추가하지 않음으로써, 출력된 데이터에서는 DataShared가 보이지 않는다. DataShared도 출력하고 싶다면 query()에 DataShared를 추가하면 된다.

하지만 아까도 얘기했듯이 DataShared의 관계는 Left Outer Join을 사용해야 한다.

outerjoin() - Left Outer Join

outerjoin()의 사용법은 Left Outer를 할 테이블을 지정해 주기만 하면 된다.

query = session.query(DataInfo, DataShared)
query = query.outerjoin(DataShared)

SELECT ... 생략 ... FROM datainfo
 LEFT OUTER JOIN "dataShared"
 ON datainfo.id = "dataShared".datainfo_id

[
 [<DataInfo object at 0x7f88ff6a4e20>, <DataShared object at 0x7f88ff6a4eb0>),
 (<DataInfo object at 0x7f88ff6a4cd0>, <DataShared object at 0x7f88ff6a4d60>),
 (<DataInfo object at 0x7f88ff6a4f10>, None),
 (<DataInfo object at 0x7f88ff6a4f70>, None),
 (<DataInfo object at 0x7f88ff6a4fd0>, None)
]

Left Outer Join이 들어갔기 때문에 DataShared를 갖고 있지 않은 DataInfo도 출력되어 총 5개의 레코드가 출력되었다. 없는 경우 None으로 처리되었다.

실전

쿼리문 만들기

이제 Join문들을 활용해 보자 풀어야 할 문제는 다음과 같다.

데이터 공유 상태와 상관 없이 즐겨 찾기가 설정되어 있고 "tag1"이라는 태그를 가지고 있는 데이터를 데이터 이름과 데이터 공유 여부를 출력해라.

우선 DataInfo와 DataShared만 출력하기 때문에 query()를 다음과 같이 잡는다.

query = session.query(DataInfo, DataShared)

데이터 공유 여부와 상관이 없으므로 DataShared를 기준으로 Outer Left Join을 잡는다.

query = query.outerjoin(DataShared)

즐겨찾기가 되어있는 데이터만 출력하라 했으므로 filter()를 추가한다.

query = query.filter(DataInfo.is_favorite == True)

이제 태그 이름이 tag1인 태그를 가지고 있는 데이터를 출력해야 한다. 그런데 데이터 마다 갖고있는 태그는 1개뿐이 아니다 0개가 될 수 있고 여러개가 될 수 있다. 게다가 DataInfo의 태그를 담당하는 DataTag의 이름은 DataTag가 아닌 Tag 테이블에 있다. 즉 DataTag와 Tag는 1대1 관계다 "tag1"을 찾기 전에 DataInfo와 DataTag를 join해서 이전 쿼리로 인해 검색된 DataInfo 레코드들이 갖고 있는 DataTag를 join을 통해서 검색하고, 태그의 이름을 갖고오기 위해 DataTag와 관계가 되어 있는 Tag의 데이터를 join을 이용해 매칭시킨다.

# 전체 코드
query = session.query(DataInfo, DataShared)
query = query.outerjoin(DataShared)
query = query.filter(DataInfo.is_favorite == True)
# 추가
query = query.join(DataTag, DataTag.datainfo_id == DataInfo.id)
query = query.join(Tag, Tag.id == DataTag.tag_id)
query = query.filter(Tag.name == 'tag1')

Tag 테이블이 들어갔고 Tag.id와 DataTag.tag_id가 매칭되었으며 DataTag.datainfo_id와 DataInfo.id가 join되었다. 이제 filter()를 이용해 "tag1"이 포함되어 있는 DataInfo를 뽑아오면 된다.

SELECT ... 생략 ...
FROM datainfo
LEFT OUTER JOIN "dataShared" ON datainfo.id = "dataShared".datainfo_id
JOIN "dataTag" ON "dataTag".datainfo_id = datainfo.id
JOIN tag ON tag.id = "dataTag".tag_id
WHERE datainfo.is_favorite = 1 AND tag.name = ?

tag.name = '?'이건 나도 잘모르겠긴 한데 뭐 그래도 쿼리문은 잘 돌아가는 것 같다. 결과는 아래와 같다. (데이터의 이름만 뽑아왔다.)

hi.txt, subdir

이렇게 보면 얘가 진짜로 "tag1"이라는 태그를 가지고 있는지 모르기 때문에 직접 확인을 해봐야 겠다.

hi.txt
===================
tag2
tag1

subdir
===================
tag1

hi.txt는 tag1, tag2를 subdir는 tag1을 갖고 있다. 제대로 출력이 되었다.

Column에 Alias 정하기

사실 이상태로 출력해도 문제는 없지만, Alias를 정해서 원하는 Column만 출력되게 할 수 있다. 해당 주제에서는 데이터 이름과 데이터 공유 여부만 출력하면 된다.

방법은 간단하다 query()문에 작성되어진 DataInfo와 DataShared를 아래와 같이 바꿔주면 된다.

query = session.query(DataInfo.name.label('이름'), DataShared.is_active.label('공유 상태'))

SELECT datainfo.name AS "이름", "dataShared".is_active AS "공유 상태"
FROM datainfo
LEFT OUTER JOIN "dataShared" ON datainfo.id = "dataShared".datainfo_id
JOIN "dataTag" ON "dataTag".datainfo_id = datainfo.id
JOIN tag ON tag.id = "dataTag".tag_id
WHERE datainfo.is_favorite = 1 AND tag.name = ?

결과는 아래와 같이 나온다. 

('hi.txt', True)
('subdir', None)

공유가 되어 있는 경우 True로 출력되고 그렇지 않은 경우는 데이터 자체가 없기 때문에 None으로 출력되었다.

사실 해당 프로젝트에서 공유 상태를 판단하는 기준은 is_active 뿐만 아니라 is_active가 True로 잡혀 있어도 추가적으로 공유 시작 날짜와 활성화 기간 등을 고려해야 하는데 이건 너무 깊게 들어갔기 때문에 DataShared 레코드 존재 유무로만 가정했다.

끗

나는 한참 전에 SQLAlchemy 관련 포스트에서 join을 한번 언급한 적이 있었다.

[SQLAlchemy] - first(), scalar(), one()

단 Join을 이용한 테이블에서 first()를 호출할 때, 해당 테이블의 상태에 따라 여러 개의 행으로 출력될 수 있습니다만, 아직 Join을 이용한 orm 구문을 사용해 본 적이 없기 때문에 어떻게 되는 지는 잘 모르겠군요, 혹시 Join하고 first()를 동시해 사용하는 상황이 올 때 추가적으로 작성하겠습니다. 사실 join 쓰려고 발버둥 치긴 하는데 자꾸 안되서 그냥 테이블을 합성하는 정도로만 사용하고 있어요 ㅠㅠ

저때 join 한번 쓰겠다고 며칠 난리 치다가 중간에 포기했던 것을 기억한다. 이번 기회를 통해 join/outerjoin에 대한 사용법을 그나마 공부할 수 있었다. 차후에 도움이 많이 될 것 같다. (저 토이 프로젝트는 지금 진행하는 프로젝트와는 별개이며 저건 지금 때려침)

부록) 프로젝트에 대한 내 쿼리 코드

아까 위에 서술했던 탐색 Topic에 대해 나는 아래와 같이 쿼리문을 작성했다. 예시 용도로만 보면 좋을 것 같다. alias는 쓰지 않았다.

class DataSearchManager(FrontendManager):
    def search(
        self, token: str,
        user: str,                      # 사용자 이름
        root: str,                      # 탐색 위치(디렉토리만)
        recursive: int = 0,             # 하위 부분까지 전부 탐색
        favorite: int = 0,              # 즐겨찾기 여부
        shared: int = 0,                # 공유 여부
        tags: Optional[str] = None,     # 태그가 포함된 데이터만
        word: Optional[str] = None,     # 연관검색어
        sort_create: int = 0,           # 생성 순 정렬
        sort_name: int = 0,             # 이름 순 정렬
    ):
        """ ... Token checking 부분 생략 ... """

        # User 탐색
        if not (user_value := UserCRUDManager().read(user_name=user)):
            # 해당 유저가 없으면 검색 결과가 없는 것으로 설정
            return []
        # 대상 data 탐색
        if root != '/':
            splited = root[:-1].split('/')
            root_query = ['/'.join(splited[:-1]) + '/', splited[-1]]
            data_value = DataDBQuery().read(full_root=root_query, is_dir=True)
            if not data_value:
                return []
        # 검색 시작
        shared_len = SERVER['data-shared-length']
        session = DatabaseGenerator.get_session()
        query = session.query(DataInfo, DataShared)
        query = query.outerjoin(DataShared)
        # 해당 사용자의 데이터만
        query = query.filter(DataInfo.user_id == user_value.id)
        # Recursive 여부
        query = query.filter(DataInfo.root == root) if not recursive \
            else query.filter(DataInfo.root.startswith(root))        
        if favorite:
            # 즐겨찾기 여부
            query = query.filter(DataInfo.is_favorite == True)
        if word:
            # 연관검색어
            query = query.filter(DataInfo.name.contains(word))
        if shared:
            # 공유 여부
            left_time = (datetime.now() - timedelta(minutes=shared_len)).strftime('%Y-%m-%d')
            right_time = '2200-01-01'
            query = query.filter(and_(
                    DataShared.is_active == True,
                    DataShared.share_started.between(left_time, right_time)
                ))
        if tags:
            # 태그 검색
            tag_list = tags.split(',')
            query = query.join(DataTag, DataTag.datainfo_id == DataInfo.id)
            query = query.join(Tag, Tag.id == DataTag.tag_id)
            query = query.filter(Tag.name.in_(tag_list))
        # 정렬시 디렉토리 - 파일 순 정렬은 필수
        if sort_name and sort_create:
            # 이름, 생성 순 정렬
            query = query.order_by(
                DataInfo.is_dir.desc(), 
                DataInfo.created.asc(), 
                DataInfo.name.asc()
            )
        elif sort_name:
            query = query.order_by(
                DataInfo.is_dir.desc(), 
                DataInfo.name.asc()
            )
        elif sort_create:
            query = query.order_by(
                DataInfo.is_dir.desc(), 
                DataInfo.created.asc()
            )
        records = query.all()
        res = list()

        for data, shared in records:
            shared_id = -1
            if shared:
                # 공유 ID 구하기
                # 공유 상태가 아닌 경우 -1
                shared_id = shared.id
                if shared.is_active \
                    and shared.share_started + timedelta(minutes=shared_len) >= datetime.now():
                    shared_id = shared.id
            res.append({
                'id': data.id,
                'root': data.root,
                'is_dir': data.is_dir,
                'name': data.name,
                'is_favorite': data.is_favorite,
                'shared_id': shared_id
            })
        

        return res