[Android] SQLITE 및 ROOM 환경에서 Join 쿼리 사용하기

문제는 이러했다.

A테이블과 B테이블이 있는데 Room에서 지원하는 Relation 방식으로는 Column 비교 시 대소문자 구분이 안되는 상황.

 

결국 쌩쿼리를 짜서 뽑아 써야 했다.

다음과 같이 LEFT JOIN을 이용하여 기준이 되는 테이블 A를 모두 포함하도록 하고 해당하는 Symbol 데이터를 붙여주는 방식.

 

 

@Query("SELECT tickers.*, ticker_symbols.* FROM tickers LEFT JOIN ticker_symbols ON tickers.base_symbol = ticker_symbols.symbol COLLATE NOCASE")
    fun getAllTickerWithSymbols(): List<TickerWithSymbol>

[TickerSymbol]

@Entity(tableName = "ticker_symbols"
//    foreignKeys = [ForeignKey(entity = Ticker::class, parentColumns = ["base_symbol"], childColumns = ["symbol"])]
)
data class TickerSymbol(
    val symbol: String?,
    val name: String?
) {
    @PrimaryKey(autoGenerate = true)
    var id: Int = 0
}

[TickerThumbnail]

@Entity(tableName = "ticker_thumbnails")
data class TickerThumbnail(
    val symbol: String?,
    val image: String?
) {
    @PrimaryKey(autoGenerate = true)
    var id: Int = 0
}

 

data class TickerWithSymbol(
    @Embedded
    val ticker: Ticker,

    @Embedded
    val Symbol: TickerSymbol?
)

 

문제는 조회 테이블이 3개일 때 터졌다.

TickerSymbol과 TickerThumbnail에 동일한 이름의 Column이 있다보니 Room의 Embedded 사용 시 충돌한다는 것...

Room이 Embedded를 적용하면서 데이터 테이블의 컬럼을 Flat하게 만들어버리는 데서 기인하는 문제다.

 

해결책으로 Embedded(prefix = "symbol_")을 주었는데 이번엔 데이터가 null로 나와버리는 문제가 발생.

data class TickerWithSymbolAndThumbnail(
    @Embedded
    val ticker: Ticker,

//    @Relation(
//        parentColumn = "base_symbol",
//        entityColumn = "symbol"
//    )
    @Embedded(prefix = "symbol_")
    val symbol: TickerSymbol?,

//    @Relation(
//        parentColumn = "base_symbol",
//        entityColumn = "symbol"
//    )
    @Embedded(prefix = "thumbnail_")
    val thumbnail: TickerThumbnail?
)

 

 

이틀을 삽질하다가 겨우 해결.

쿼리를 찾을 때 AS를 이용하여 수정된 prefix로 조회를 해주어야 한다.

TickerSymbol과 TickerThumbnail의 symbol을 PrimaryKey로 쓰면 Nullable이 불가능하여 반드시 컬럼선택 시 

함께 선택해주어야 한다.

다른 방법도 있긴 한데 그냥 이게 제일 간단한 방법이다... 

그리고 반드시 각 Join마다 COLLATE NOCASE를 설정해주어야 대소문자 구분 없이 탐색이 가능.

 

@Transaction
@Query("SELECT tickers.*, " +
            "ticker_symbols.symbol AS symbol_symbol, " +
            "ticker_symbols.name AS symbol_name, " +
            "ticker_thumbnails.symbol AS thumbnail_symbol, " +
            "ticker_thumbnails.image AS thumbnail_image FROM tickers " +
            "LEFT JOIN ticker_symbols ON tickers.base_symbol = ticker_symbols.symbol COLLATE NOCASE " +
            "LEFT JOIN ticker_thumbnails ON tickers.base_symbol = ticker_thumbnails.symbol COLLATE NOCASE")
fun getTickerWithSymbolAndThumbnailTest(): List<TickerWithSymbolAndThumbnail>

 

 

Room이 생성하는 GeneratedClass 까지 다 뒤져보고 공식문서까지 하나하나 다 살펴봤는데

다 삽질이었고 해결책은 생각보다 가까이에 있었다...

 

근데 Relation은 왜 대소문자를 구별하는 속성이 없는지 의문이다.

공식적으로 이슈를 제기할 예정.