SQL Проверьте, содержится ли один идентификатор в списке IDS, сохраненном в базе данных

Я пишу SQL-запрос в своем приложении для Android, которое, хотя и будет работать, но не работает. Итак, это выглядит так:

@Query("SELECT * FROM ${AppDatabase.GAME_TABLE} WHERE :collectionId IN (themesIds)")
    fun getAllGamesForCollection(collectionId: String): List<GameEntity>

В моем объекте Game_Table поле themeIds представлено следующим образом:

@ColumnInfo(name = "themesIds")
    var themesIds: List<String> = mutableListOf()

Я хотел проверить, какие игры содержат «collectionId» (единственную строку, которую я отправляю в своем коде) в своих темах Ids (список строк, которые содержат разные идентификаторы и сохраняются в базе данных). Пользуюсь комнатной библиотекой (только для записей).

Почему это не работает?

См. stackoverflow.com/questions/75133104/…

user39950 16.01.2023 21:14

@ user39950 извините, но я не вижу никакой связи между этими двумя вопросами. Более того, мой код не вызывает сбоев, он просто не возвращает никакого значения для этого запроса. Также я использую не список объектов, а список примитивных данных в моей сущности.

Kratos 16.01.2023 21:39
0
2
56
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Вы можете использовать WHERE instr(themesIds,:collectionId) или WHERE themesIds LIKE '%'||:collectionId||'%'.

Это зависит от сохраненного представления списка (см. ниже), содержащего текстовые значения (если список преобразуется в поток байтов, то описанное выше не будет работать без преобразования байтов).

Однако оба склонны к другим нежелательным совпадениям; например, AAA также найдет AAABBBBBBAAACCC, поскольку AAA существует во всех них.

Вероятной причиной того, что IN не работает, является то, что он ожидает, что значение после IN будет либо списком значений, разделенных запятыми, либо результатом запроса на выборку. Маловероятно, что данные (список строк, содержащих разные идентификаторы и сохраненные в базе данных) являются именно таким CSV-файлом, это будет одно значение, содержащее все значения списка.

  • на основе выделенной жирным шрифтом и курсивом цитаты из вопроса.

Если упомянутая выше слабость является проблемой, вам нужно будет более конкретно указать, какой именно список строк, которые содержат разные идентификаторы и сохраняются в базе данных, на самом деле; это не может быть список. То есть SQLite (база данных, оболочкой которой является Room) не хранит списки, а хранит отдельные значения для каждого столбца, которые могут отражать объект (список).

  • Скажем, список состоит из элементов
    • Быстрый
    • Коричневый
    • Лиса

Затем 4 значения должны быть представлены одним значением.

Если бы это единственное значение было The,Quick,Brown,Fox (CSV), то IN не работало бы, поскольку это единственное значение (если бы это были «The», «Quick», «Brown», «Fox», тогда это сработало бы, но чтобы получить это из столбца будет довольно сложным (рекурсивный подзапрос, который разбивает строку)).

Однако нет причин, по которым представление могло бы вместо этого быть The;Quick;Brown;Fox, и в этом случае IN не работало бы.

  • Для комнаты это будет зависеть от преобразователя типов, используемого для хранения списка.

Вы можете отредактировать свой вопрос, включив в него пример фактических данных, хранящихся в столбце themesIds (для этого можно использовать проверку приложений)

человек, это отличное объяснение ситуации. Я попробовал WHERE instr(themesIds,: collectionId), и это работает как шарм. Спасибо за помощь!

Kratos 17.01.2023 22:52

Вот еще один способ, который, с точки зрения реляционной базы данных (например, SQLite, который является оболочкой Room), считается лучшим/правильным (и на что пытается указать комментарий в вашем вопросе).

То есть вместо того, чтобы встраивать список, этот список действительно должен быть другой таблицей, которая имеет отношение к таблице, содержащей встроенный список. Как таковой, а не список themeId таблицы Game и таблицы Theme (которые у вас вполне могут быть). Поскольку тема может использоваться многими играми, и эта игра может иметь множество тем. Отношения будут отношениями многих-многих.

Связь «многие-многие» обычно имеет другую таблицу с двумя основными столбцами. Один для ссылки на одну таблицу, другой для ссылки на другую таблицу. Два столбца образуют составной уникальный индекс обоих столбцов (составной первичный ключ). Таким образом, любая игра может ссылаться (карта/ссылка....) на любую тему.

Такая таблица имеет множество названий: справочная таблица, таблица сопоставления, ассоциативная таблица....

С такой настройкой можно было бы использовать IN.

Чтобы продемонстрировать, рассмотрим следующий код, который отражает часть того, что можно почерпнуть из вашего вопроса.

Сначала версия класса AppDatabase (расширенная для включения ВСЕХ имен компонентов, таблицы и столбцов, а также расширенная для обслуживания таблицы сопоставления, надеюсь, имена должны быть достаточно понятными): -

class AppDatabase {
    companion object {
        const val DATABASE_NAME = "game.db"
        const val GAME_TABLE = "game"
        const val GAME_ID_COL = "${GAME_TABLE}${BaseColumns._ID}"
        const val GAME_THEMEIDS_COL = "${GAME_TABLE}_themids"

        const val THEME_TABLE = "theme"
        const val THEME_ID_COL = "${THEME_TABLE}${BaseColumns._ID}"
        const val THEME_NAME_COL = "${ THEME_TABLE}_themename"

        /* The Mapping table */
        const val GAME_THEME_MAP_TABLE = "game_theme_map"
        const val GAME_THEME_MAP_GAME_ID_COL = "${GAME_THEME_MAP_TABLE}_${GAME_ID_COL}"
        const val GAME_THEME_MAP_THEME_ID_COL  = "${GAME_THEME_MAP_TABLE}_${THEME_ID_COL}"
    }
}
  • Обратите внимание, что приведенное выше гарантирует, что все имена столбцов уникальны (Room без жалоб может сопоставлять значения между столбцами с одинаковыми именами).

  • Как видно, 3 таблицы Game, Theme и таблица сопоставления между ними.

Для демонстрации игровой стол: -

@Entity(tableName = AppDatabase.GAME_TABLE)
data class Game(
    @ColumnInfo(name = AppDatabase.GAME_ID_COL)
    @PrimaryKey
    val id: Long?=null,
    //@ColumnInfo(name = AppDatabase.GAME_THEMEIDS_COL)
    //val themeIds: List<String>
)
  • обратите внимание, что идентификаторы тем были закомментированы, так как они не нужны.

Таблица тем: -

@Entity(
    tableName = AppDatabase.THEME_TABLE,

    /* Make the Theme name unique */
    indices = [
        Index(AppDatabase.THEME_NAME_COL, unique = true)
    ]
)
data class Theme(
    @ColumnInfo(name = AppDatabase.THEME_ID_COL)
    @PrimaryKey
    val id: Long?=null,
    @ColumnInfo(name = AppDatabase.THEME_NAME_COL)
    val themeName: String
)
  • Обратите внимание, если у вас есть столбец имени, но он все равно был добавлен.
  • Из-за того, как работает SQLite и более эффективно обрабатывает идентификатор строки, идентификатор будет псевдонимом идентификатора строки. Несмотря на то, что, как будет видно, неэффективное использование autoGenerate не использовалось (хотя идентификаторы будут генерироваться автоматически, если для идентификатора не используется значение, отличное от нуля).
  • Обратите внимание, если хотите, но имя темы должно быть уникальным (чтобы продемонстрировать, как это сделать в комнате без столбца, являющегося первичным ключом (строковые первичные ключи менее эффективны))

Теперь таблица сопоставления GameThemeMap (карта используется только потому, что она короче): -

/* caters for a many-many relationship */
/* i.e. many games can map to a theme, many themes can map to a them */
/* no need to store lists*/
@Entity(tableName = AppDatabase.GAME_THEME_MAP_TABLE,
    primaryKeys = [AppDatabase.GAME_THEME_MAP_GAME_ID_COL,AppDatabase.GAME_THEME_MAP_THEME_ID_COL],
    /* Optional but enforces Referential Integrity */
    foreignKeys = [
        ForeignKey(
            entity =  Game::class,
            parentColumns = [AppDatabase.GAME_ID_COL],
            childColumns = [AppDatabase.GAME_THEME_MAP_THEME_ID_COL],
            /* Optional within Foreign Key but helps to automatically maintain Referential Integrity */
            onDelete = ForeignKey.CASCADE, /* deletes children if parent is deleted */
            onUpdate = ForeignKey.CASCADE /* updates children if parent mapped column is updated */
        ),
        ForeignKey(
            entity = Theme::class,
            parentColumns = [AppDatabase.THEME_ID_COL],
            childColumns = [AppDatabase.GAME_THEME_MAP_THEME_ID_COL],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class GameThemeMap(
    @ColumnInfo(name = AppDatabase.GAME_THEME_MAP_GAME_ID_COL)
    val gameIdMap: Long,
    @ColumnInfo(index = true, name = AppDatabase.GAME_THEME_MAP_THEME_ID_COL)
    /* Index will speed up access via themes id column, but will have an overhead when inserting */
    val themeIdMao: Long
)
  • надеюсь, комментарии объяснят, что может показаться довольно сложным для таблицы с двумя столбцами.

Поскольку вы, вероятно, ожидаете, что сможете получить игру с ее темами, тогда POJO для этого GameWithThemes: -

data class GameWithThemes(
    @Embedded
    val game: Game,
    @Relation(
        entity = Theme::class,
        parentColumn = AppDatabase.GAME_ID_COL,
        entityColumn = AppDatabase.THEME_ID_COL,
        associateBy = Junction(
            value = GameThemeMap::class,
            parentColumn = AppDatabase.GAME_THEME_MAP_GAME_ID_COL,
            entityColumn = AppDatabase.GAME_THEME_MAP_THEME_ID_COL
        )
    )
    val themes: List<Theme>
)
  • Вышеупомянутое позволяет Room имитировать использование JOIN для получения связанных данных.

Некоторые функции @Dao включают два способа выбора игр через collectionId: один с использованием IN, другой с использованием сравнения идентификатора темы, извлеченного с помощью JOIN, поэтому TheDAO:

@Dao
interface TheDAOs {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(game: Game): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(theme: Theme): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(gameThemeMap: GameThemeMap): Long

    @Transaction /* recommended as Room executes subqueries to get the Themes for the Game (note that it gets ALL themes for a game) */
    @Query("SELECT ${AppDatabase.GAME_TABLE}.* " +
            "FROM ${AppDatabase.GAME_TABLE} " +
            "JOIN ${AppDatabase.GAME_THEME_MAP_TABLE} " +
            "ON  ${AppDatabase.GAME_THEME_MAP_TABLE}.${AppDatabase.GAME_THEME_MAP_GAME_ID_COL} = ${AppDatabase.GAME_TABLE}.${AppDatabase.GAME_ID_COL} " +
            "AND :collectionId = ${AppDatabase.GAME_THEME_MAP_TABLE}.${AppDatabase.GAME_THEME_MAP_THEME_ID_COL}")
    fun getGamesWithThemesViaJoin(collectionId: Long): List<GameWithThemes>

    @Transaction
    @Query("SELECT * FROM ${AppDatabase.GAME_TABLE} WHERE :collectionId IN(SELECT ${AppDatabase.GAME_THEME_MAP_THEME_ID_COL} FROM ${AppDatabase.GAME_THEME_MAP_TABLE} WHERE ${AppDatabase.GAME_THEME_MAP_GAME_ID_COL}=${AppDatabase.GAME_ID_COL})")
    fun getGamesWithThemesViaIN(collectionId: Long): List<GameWithThemes>

    @Query("SELECT ${AppDatabase.THEME_ID_COL} FROM ${AppDatabase.THEME_TABLE} WHERE ${AppDatabase.THEME_NAME_COL}=:themeName")
    fun getThemeIdFromThemeName(themeName: String): Long
}
  • надеюсь, имена функций должны сказать, что происходит

Чтобы связать все вышеперечисленное с аннотированным абстрактным классом @Database TheDatabase:

@Database(entities = [Game::class,Theme::class,GameThemeMap::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getTheDAOs(): TheDAOs

    companion object {
        var instance: TheDatabase? = null
        fun getInstance(context: Context): TheDatabase {
            if (instance==null) {
                instance = Room.databaseBuilder(context,TheDatabase::class.java,AppDatabase.DATABASE_NAME)
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as TheDatabase
        }
    }
}
  • .allowMainThreadQueries для удобства и краткости

Последний, чтобы фактически продемонстрировать некоторый код в действии, которое добавляет 5 тем, 5 игр и различные темы, сопоставленные с играми.

  • В игре 1 есть 2 темы, сопоставленные с ней 1 и 2.
  • В игре 2 есть 2 другие темы 3 и 4.
  • Игра 3 состоит из 4 тем 1,2,3 и 4.
  • В игре 4 есть 1 тема 5
  • В игре 5 нет тем

После вставки данных выполняются 2 набора по 5 запросов и извлеченные GameWithThemes выводятся в лог методом logGameWithThemes.

Активность MainActivity: -

const val TAG = "DBINFO"
class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: TheDAOs
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        db = TheDatabase.getInstance(this)
        dao = db.getTheDAOs()

        val t1Id = dao.insert(Theme(themeName = "Theme 1"))
        val t2Id = dao.insert(Theme(themeName = "Theme 2"))
        val t3Id = dao.insert(Theme(themeName = "Theme 3"))
        val t4Id = dao.insert(Theme(themeName = "Theme 4"))
        val t5Id = dao.insert(Theme(themeName = "Theme 5"))

        val game1Id =dao.insert(Game())
        val game2Id = dao.insert(Game())
        val game3Id = dao.insert(Game())
        val game4Id = dao.insert(Game())
        val game5Id = dao.insert(Game())

        dao.insert(GameThemeMap(game1Id,t1Id))
        dao.insert(GameThemeMap(game1Id,t2Id))
        dao.insert(GameThemeMap(game2Id,t3Id))
        dao.insert(GameThemeMap(game2Id,t4Id))
        dao.insert(GameThemeMap(game3Id,t1Id))
        dao.insert(GameThemeMap(game3Id,t2Id))
        dao.insert(GameThemeMap(game3Id,t3Id))
        dao.insert(GameThemeMap(game3Id,t4Id))
        dao.insert(GameThemeMap(game4Id,t5Id))

        for (g in dao.getGamesWithThemesViaIN(t1Id)) {
            logGameWithThemes(g,"_IN_TID_$t1Id")
        }
        for (g in dao.getGamesWithThemesViaIN(t2Id)) {
            logGameWithThemes(g,"_IN_TID_$t2Id")
        }
        for (g in dao.getGamesWithThemesViaIN(t3Id)) {
            logGameWithThemes(g,"_IN_TID_$t3Id")
        }
        for (g in dao.getGamesWithThemesViaIN(t4Id)) {
            logGameWithThemes(g,"_IN_TID_$t4Id")
        }
        for (g in dao.getGamesWithThemesViaIN(t5Id)) {
            logGameWithThemes(g,"_IN_TID_$t5Id")
        }
        for (g in dao.getGamesWithThemesViaJoin(t1Id)) {
            logGameWithThemes(g,"_JN_TID_$t1Id")
        }
        for (g in dao.getGamesWithThemesViaJoin(t2Id)) {
            logGameWithThemes(g,"_JN_TID_$t2Id")
        }
        for (g in dao.getGamesWithThemesViaJoin(t3Id)) {
            logGameWithThemes(g,"_JN_TID_$t3Id")
        }
        for (g in dao.getGamesWithThemesViaJoin(t4Id)) {
            logGameWithThemes(g,"_JN_TID_$t4Id")
        }
        for (g in dao.getGamesWithThemesViaJoin(t5Id)) {
            logGameWithThemes(g,"_JN_TID_$t5Id")
        }
    }
    fun logGameWithThemes(gameWithThemes: GameWithThemes, suffix: String) {
        val sb = StringBuilder()
        for(t in gameWithThemes.themes) {
            sb.append("\n\tTheme ID is ${t.id} Theme name is ${t.themeName}")
        }
        Log.d(TAG+suffix,"Game ID is ${gameWithThemes.game.id} it has ${gameWithThemes.themes.size} THEMES. They are:-${sb}")
    }
}

При запуске (предназначенном для запуска только один раз для демонстрации) вывод в журнал: -

2023-01-18 22:22:04.903 D/DBINFO_IN_TID_1: Game ID is 1 it has 2 THEMES. They are:-
        Theme ID is 1 Theme name is Theme 1
        Theme ID is 2 Theme name is Theme 2
        
2023-01-18 22:22:04.903 D/DBINFO_IN_TID_1: Game ID is 3 it has 4 THEMES. They are:-
        Theme ID is 1 Theme name is Theme 1
        Theme ID is 2 Theme name is Theme 2
        Theme ID is 3 Theme name is Theme 3
        Theme ID is 4 Theme name is Theme 4
        
        
2023-01-18 22:22:04.906 D/DBINFO_IN_TID_2: Game ID is 1 it has 2 THEMES. They are:-
        Theme ID is 1 Theme name is Theme 1
        Theme ID is 2 Theme name is Theme 2
        
2023-01-18 22:22:04.906 D/DBINFO_IN_TID_2: Game ID is 3 it has 4 THEMES. They are:-
        Theme ID is 1 Theme name is Theme 1
        Theme ID is 2 Theme name is Theme 2
        Theme ID is 3 Theme name is Theme 3
        Theme ID is 4 Theme name is Theme 4
        
        
2023-01-18 22:22:04.910 D/DBINFO_IN_TID_3: Game ID is 2 it has 2 THEMES. They are:-
        Theme ID is 3 Theme name is Theme 3
        Theme ID is 4 Theme name is Theme 4
        
2023-01-18 22:22:04.910 D/DBINFO_IN_TID_3: Game ID is 3 it has 4 THEMES. They are:-
        Theme ID is 1 Theme name is Theme 1
        Theme ID is 2 Theme name is Theme 2
        Theme ID is 3 Theme name is Theme 3
        Theme ID is 4 Theme name is Theme 4
        
        
2023-01-18 22:22:04.916 D/DBINFO_IN_TID_4: Game ID is 2 it has 2 THEMES. They are:-
        Theme ID is 3 Theme name is Theme 3
        Theme ID is 4 Theme name is Theme 4
        
2023-01-18 22:22:04.916 D/DBINFO_IN_TID_4: Game ID is 3 it has 4 THEMES. They are:-
        Theme ID is 1 Theme name is Theme 1
        Theme ID is 2 Theme name is Theme 2
        Theme ID is 3 Theme name is Theme 3
        Theme ID is 4 Theme name is Theme 4
        
        
2023-01-18 22:22:04.921 D/DBINFO_IN_TID_5: Game ID is 4 it has 1 THEMES. They are:-
        Theme ID is 5 Theme name is Theme 5
        
        
        
        
2023-01-18 22:22:04.926 D/DBINFO_JN_TID_1: Game ID is 1 it has 2 THEMES. They are:-
        Theme ID is 1 Theme name is Theme 1
        Theme ID is 2 Theme name is Theme 2
        
2023-01-18 22:22:04.926 D/DBINFO_JN_TID_1: Game ID is 3 it has 4 THEMES. They are:-
        Theme ID is 1 Theme name is Theme 1
        Theme ID is 2 Theme name is Theme 2
        Theme ID is 3 Theme name is Theme 3
        Theme ID is 4 Theme name is Theme 4
        
        
2023-01-18 22:22:04.931 D/DBINFO_JN_TID_2: Game ID is 1 it has 2 THEMES. They are:-
        Theme ID is 1 Theme name is Theme 1
        Theme ID is 2 Theme name is Theme 2
        
2023-01-18 22:22:04.931 D/DBINFO_JN_TID_2: Game ID is 3 it has 4 THEMES. They are:-
        Theme ID is 1 Theme name is Theme 1
        Theme ID is 2 Theme name is Theme 2
        Theme ID is 3 Theme name is Theme 3
        Theme ID is 4 Theme name is Theme 4
        
        
2023-01-18 22:22:04.937 D/DBINFO_JN_TID_3: Game ID is 2 it has 2 THEMES. They are:-
        Theme ID is 3 Theme name is Theme 3
        Theme ID is 4 Theme name is Theme 4
        
2023-01-18 22:22:04.937 D/DBINFO_JN_TID_3: Game ID is 3 it has 4 THEMES. They are:-
        Theme ID is 1 Theme name is Theme 1
        Theme ID is 2 Theme name is Theme 2
        Theme ID is 3 Theme name is Theme 3
        Theme ID is 4 Theme name is Theme 4
        
        
2023-01-18 22:22:04.940 D/DBINFO_JN_TID_4: Game ID is 2 it has 2 THEMES. They are:-
        Theme ID is 3 Theme name is Theme 3
        Theme ID is 4 Theme name is Theme 4
        
2023-01-18 22:22:04.940 D/DBINFO_JN_TID_4: Game ID is 3 it has 4 THEMES. They are:-
        Theme ID is 1 Theme name is Theme 1
        Theme ID is 2 Theme name is Theme 2
        Theme ID is 3 Theme name is Theme 3
        Theme ID is 4 Theme name is Theme 4
        
        
2023-01-18 22:22:04.945 D/DBINFO_JN_TID_5: Game ID is 4 it has 1 THEMES. They are:-
        Theme ID is 5 Theme name is Theme 5

то есть ожидаемые результаты (отмечая, что при использовании @Relation Room извлекает ВСЕ дочерние элементы родителя) и, что важно, оба метода (IN и JOIN) дают одинаковые результаты.

По сравнению с использованием instr или LIKE %???% описанный выше метод должен быть намного эффективнее (быстрее), так как избегается полное сканирование таблицы (при использовании instr или LIKE %????% нет другого способа, кроме полного сканирования таблицы, поскольку каждая строка должна быть обработана/проверена).

Кроме того, сравнения ТОЧНЫ.

Спасибо еще раз. Это больше касается деталей, хе-хе

Kratos 19.01.2023 20:56

Другие вопросы по теме