SQLAlchemy Many To Many Additional Column

1. 요구 사항

이벤트와 비디오는 M:N관계이다. 이벤트에 속한 비디오를 가져올 때 특정 컬럼을 통해 정렬하여 가져와야 한다.

2. 해결 방법

아래와 같이 events, videos두개의 테이블이 존재한다고 가정한다.

class Event(Base):
    __tablename__ = 'events'

    id = Column(BigInteger, primary_key=True, autoincrement=True)
    title = Column(Unicode(255))

class Video(Base):
    __tablename__ = 'videos'

    id = Column(BigInteger, primary_key=True, autoincrement=True)
    name = Column(Unicode(255))

만약 여러개의 비디오가 여러개의 이벤트에 속할 수 있는 즉, M:N관계의 테이블 구성이 필요할 경우 아래처럼 중간 Pivot테이블을 추가적으로 구성한다.

event_video = Table(
    'event_video',
    Base.metadata,
    Column('id', BigInteger, primary_key=True, autoincrement=True),
    Column('event_id', BigInteger, ForeignKey('events.id')),
    Column('video_id', BigInteger, ForeignKey('videos.id')),
)

여기까진 굉장히 일반적이다. 그런데 여기서 M:N테이블에 추가적인 컬럼이 필요한 상황이 있다면 어떻게 해야 할까. 예를 들어서 비디오의 순서에 관한 컬럼(seq)이 필요하다고 생각해보자. Video는 고유 비디오에 대한 정보이므로 딱 1개의 로우만 저장된다. 따라서 seq컬럼을 추가하기에 적합하지 않다. 이 때 중간 테이블인 event_video테이블에 컬럼을 추가해야하는데, 일반적이지 않은 상황이라 어떻게 쿼리를 해야할 지 난감했다.

event_video = Table(
    'event_video',
    Base.metadata,
    Column('id', BigInteger, primary_key=True, autoincrement=True),
    Column('event_id', BigInteger, ForeignKey('events.id')),
    Column('video_id', BigInteger, ForeignKey('videos.id')),
    Column('seq', Integer, nullable=False), # HERE
)

(seq컬럼이 추가된 모습)

일단 위처럼 Table을 통해 만든 중간 테이블을 다른 테이블 모델처럼 Class형태로 구성한다.

class EventVideo(Base):
    __table__ = event_video

    video = relationship('Video', backref='video_relation')
    event = relationship('Event', backref='event_relation')

그리고 video, event라는 relationship필드를 생성하고 각 테이블에 매핑시켜준다. 다음으로 기존 Event, Video 클래스에도 relationship을 걸어준다.

class Event(Base):
    __tablename__ = 'events'

    id = Column(BigInteger, primary_key=True, autoincrement=True)
    title = Column(Unicode(255))
    videos = relationship(  # HERE
        'Video',
        secondary=event_video,
        back_populates='events',
    )


class Video(Base):
    __tablename__ = 'videos'

    id = Column(BigInteger, primary_key=True, autoincrement=True)
    name = Column(Unicode(255))
    events = relationship(  # HERE
        'Event',
        secondary=event_video,
        back_populates='videos',
    )

이제 어떻게 쿼리를 날려서 저장해야할까. 먼저 Event와 Video를 생성해둔 상태라고 가정하고 설명한다. 

event = session.query(Event).first()
video = session.query(Video).first()
event_video = EventVideo(event=event, video=video, seq=1)
session.add(event_video)
session.commit()

위처럼 event와 video를 가져온다음 EventVideo의 relationship필드에 직접 박아준다. 그리고 추가적으로 삽입한 seq컬럼에도 정보를 넣어준다. 커밋한 이후 테이블을 살펴보면 정상적으로 데이터가 저장된 모습을 확인할 수 있다.

마지막으로 여기서 event에 딸린 video를 가져올 때 seq컬럼을 기반으로 정렬하려면 어떻게 해줘야 할까?

from sqlalchemy import  desc
from sqlalchemy.orm import backref


class Event(Base):
    __tablename__ = 'events'

    id = Column(BigInteger, primary_key=True, autoincrement=True)
    title = Column(Unicode(255))
    videos = relationship(
        'Video',
        secondary=event_video,
        back_populates='events',
        order_by=desc(event_video.c.seq),  # HERE
    )

마지막 order_by 속성에 넣어준 것 처럼 M:N테이블.c.필드명을 desc(또는 asc)를 통해 order by조건에 명시해주면 videos관계를 통해 비디오를 가져올 때 seq기반으로 정렬하여 가져오는 모습을 확인할 수 있다.