J'interroge à partir de deux tables et combine les données à un POJO.

J'ai une table avec quelques entrées et une table avec des catégories.

J'interroge avec la déclaration suivante:

@Transaction
@Query("SELECT $BOOKENTRIES.*, $CATEGORIES.$ID AS $PREFIX_CATEGORY$ID, $CATEGORIES.$NAME AS $PREFIX_CATEGORY$NAME, $CATEGORIES.$ICON_ID AS $PREFIX_CATEGORY$ICON_ID, $CATEGORIES.$COLOR AS $PREFIX_CATEGORY$COLOR FROM $BOOKENTRIES INNER JOIN $CATEGORIES ON $BOOKENTRIES.$CATEGORY_ID = $CATEGORIES.$ID WHERE $BOOKENTRIES.$ID = :id")
fun get(id: Long): BookEntry?

La colonne $BOOKENTRIES.$CATEGORY_ID peut être null. La requête fonctionne si je supprime tout ce qui concerne l'instruction de jointure interne.

Le POJO:

class BookEntry(
        @Embedded var entity: BookEntryEntity = BookEntryEntity(),
        @Embedded(prefix = PREFIX_CATEGORY) var category: Category? = null,
        contacts: List<Contact.ContactEntity>? = null
) : Parcelable {

    @Relation(
            entity = Contact.ContactEntity::class,
            parentColumn = ID,
            entityColumn = BOOKENTRY_ID
    )
    var embeddedContacts: List<Contact.ContactEntity>? = contacts

    var id: Long
        get() = entity.id
        set(value) {
            entity.id = value
        }

    var title: String
        get() = entity.title
        set(value) {
            entity.title = value
        }

    var date: Date
        get() = entity.date
        set(value) {
            entity.date = value
        }

    var value: Double
        get() = entity.value
        set(value) {
            entity.value = value
        }

    var notes: String
        get() = entity.notes
        set(value) {
            entity.notes = value
        }

    var entryType: Int
        get() = entity.entryType
        set(value) {
            entity.entryType = value
        }

    fun contacts(context: Context, onFinish: (List<Contact>?) -> Unit) = GlobalScope.launch(Dispatchers.Main) {
        onFinish(contacts(context))
    }

    suspend fun contacts(context: Context): List<Contact>? = withContext(Dispatchers.IO) {
        return@withContext context.hasPermission(Manifest.permission.READ_CONTACTS).takeIf { it }?.run {
            embeddedContacts?.map {
                Contact(it.id, it.bookEntryId, it.contactId, ContactsLoader(context).loadContactName(it.contactId), it.hasPaid)
            } ?: listOf()
        }
    }

    val isClaimOrDebt: Boolean
        get() = entryType == Type.Claim || entryType == Type.Debt

    object Type {
        const val Earning = 0
        const val Expense = 1
        const val Claim = 2
        const val Debt = 3
    }

    @Entity(tableName = Database.Table.BOOKENTRIES)
    data class BookEntryEntity(
            @PrimaryKey(autoGenerate = true) @ColumnInfo(name = ID) var id: Long = 0,
            @ColumnInfo(name = TITLE) var title: String = "",
            @ColumnInfo(name = DATE) var date: Date = Date(),
            @ColumnInfo(name = VALUE) var value: Double = 0.0,
            @ColumnInfo(name = NOTES) var notes: String = "",
            @ColumnInfo(name = ENTRYTYPE) var entryType: Int = Type.Earning,
            @ColumnInfo(name = CATEGORY_ID) var categoryId: Long? = null
    ) : Parcelable {

        constructor(parcel: Parcel) : this(
                parcel.readLong(),
                parcel.readString() as String,
                Date(parcel.readLong()),
                parcel.readDouble(),
                parcel.readString() as String,
                parcel.readInt()
        )

        override fun writeToParcel(parcel: Parcel, flags: Int) {
            parcel.writeLong(id)
            parcel.writeString(title)
            parcel.writeLong(date.time)
            parcel.writeDouble(value)
            parcel.writeString(notes)
            parcel.writeInt(entryType)
        }

        override fun describeContents(): Int {
            return 0
        }

        companion object CREATOR : Parcelable.Creator<BookEntryEntity> {
            override fun createFromParcel(parcel: Parcel): BookEntryEntity {
                return BookEntryEntity(parcel)
            }

            override fun newArray(size: Int): Array<BookEntryEntity?> {
                return arrayOfNulls(size)
            }
        }
    }

    //region Parcelable

    constructor(parcel: Parcel) : this(
            parcel.readParcelable(BookEntryEntity::class.java.classLoader) as BookEntryEntity,
            parcel.readParcelable(Category::class.java.classLoader),
            parcel.createTypedArrayList(Contact.ContactEntity)
    )

    override fun writeToParcel(parcel: Parcel, flags: Int) {
        parcel.writeParcelable(entity, flags)
        parcel.writeParcelable(category, flags)
        parcel.writeList(embeddedContacts)
    }

    override fun describeContents(): Int {
        return 0
    }

    //endregion

    companion object {

        fun create(id: Long = 0, title: String, date: Date, value: Double, notes: String, entryType: Int, categoryId: Long? = null, contacts: List<Contact.ContactEntity>? = null): BookEntry {
            return BookEntry(BookEntryEntity(id = id, title = title, date = date, value = value, notes = notes, entryType = entryType, categoryId = categoryId), contacts = contacts)
        }

        fun createClaimEntry(title: String, date: Date, value: Double, notes: String, categoryId: Long? = null, contacts: List<Contact.ContactEntity>?): BookEntry {
            return create(title = title, date = date, value = value, notes = notes, entryType = Type.Claim, categoryId = categoryId, contacts = contacts)
        }

        fun createDebtEntry(title: String, date: Date, value: Double, notes: String, categoryId: Long? = null, contacts: List<Contact.ContactEntity>?): BookEntry {
            return create(title = title, date = date, value = value, notes = notes, entryType = Type.Debt, categoryId = categoryId, contacts = contacts)
        }

        @JvmField
        val CREATOR = object : Parcelable.Creator<BookEntry> {
            override fun createFromParcel(parcel: Parcel): BookEntry {
                return BookEntry(parcel)
            }

            override fun newArray(size: Int): Array<BookEntry?> {
                return arrayOfNulls(size)
            }
        }
    }
}

L'entité de catégorie:

@Entity(tableName = Database.Table.CATEGORIES)
data class Category(
    @PrimaryKey(autoGenerate = true) @ColumnInfo(name = ID) var id: Long = 0,
    @ColumnInfo(name = NAME) var name: String = "",
    @ColumnInfo(name = ICON_ID) var iconId: Int = 0,
    @ColumnInfo(name = COLOR) @ColorInt var color: Int = DEFAULT_COLOR
) : Parcelable {

    constructor(parcel: Parcel) : this(
        parcel.readLong(),
        parcel.readString() as String,
        parcel.readInt()
    )

    override fun writeToParcel(parcel: Parcel, flags: Int) {
        parcel.writeLong(id)
        parcel.writeString(name)
        parcel.writeInt(iconId)
    }

    override fun describeContents(): Int {
        return 0
    }

    companion object {

        @JvmField
        val CREATOR = object : Parcelable.Creator<Category> {
            override fun createFromParcel(parcel: Parcel): Category {
                return Category(parcel)
            }

            override fun newArray(size: Int): Array<Category?> {
                return arrayOfNulls(size)
            }
        }
    }
}

Et le schéma de la base de données:

"entities": [
  {
    "tableName": "bookentries",
    "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `title` TEXT NOT NULL, `date` INTEGER NOT NULL, `value` REAL NOT NULL, `notes` TEXT NOT NULL, `entrytype` INTEGER NOT NULL, `category_id` INTEGER)",
    "fields": [
      {
        "fieldPath": "id",
        "columnName": "id",
        "affinity": "INTEGER",
        "notNull": true
      },
      {
        "fieldPath": "title",
        "columnName": "title",
        "affinity": "TEXT",
        "notNull": true
      },
      {
        "fieldPath": "date",
        "columnName": "date",
        "affinity": "INTEGER",
        "notNull": true
      },
      {
        "fieldPath": "value",
        "columnName": "value",
        "affinity": "REAL",
        "notNull": true
      },
      {
        "fieldPath": "notes",
        "columnName": "notes",
        "affinity": "TEXT",
        "notNull": true
      },
      {
        "fieldPath": "entryType",
        "columnName": "entrytype",
        "affinity": "INTEGER",
        "notNull": true
      },
      {
        "fieldPath": "categoryId",
        "columnName": "category_id",
        "affinity": "INTEGER",
        "notNull": false
      }
    ],
    "primaryKey": {
      "columnNames": [
        "id"
      ],
      "autoGenerate": true
    },
    "indices": [],
    "foreignKeys": []
  },
  {
    "tableName": "categories",
    "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `name` TEXT NOT NULL, `icon_id` INTEGER NOT NULL, `color` INTEGER NOT NULL)",
    "fields": [
      {
        "fieldPath": "id",
        "columnName": "id",
        "affinity": "INTEGER",
        "notNull": true
      },
      {
        "fieldPath": "name",
        "columnName": "name",
        "affinity": "TEXT",
        "notNull": true
      },
      {
        "fieldPath": "iconId",
        "columnName": "icon_id",
        "affinity": "INTEGER",
        "notNull": true
      },
      {
        "fieldPath": "color",
        "columnName": "color",
        "affinity": "INTEGER",
        "notNull": true
      }
    ],
    "primaryKey": {
      "columnNames": [
        "id"
      ],
      "autoGenerate": true
    },
    "indices": [],
    "foreignKeys": []
  },
  {
    "tableName": "bookentrycontacts",
    "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `bookentry_id` INTEGER NOT NULL, `contact_id` INTEGER NOT NULL, `has_paid` INTEGER NOT NULL, FOREIGN KEY(`bookentry_id`) REFERENCES `bookentries`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )",
    "fields": [
      {
        "fieldPath": "id",
        "columnName": "id",
        "affinity": "INTEGER",
        "notNull": true
      },
      {
        "fieldPath": "bookEntryId",
        "columnName": "bookentry_id",
        "affinity": "INTEGER",
        "notNull": true
      },
      {
        "fieldPath": "contactId",
        "columnName": "contact_id",
        "affinity": "INTEGER",
        "notNull": true
      },
      {
        "fieldPath": "hasPaid",
        "columnName": "has_paid",
        "affinity": "INTEGER",
        "notNull": true
      }
    ],
    "primaryKey": {
      "columnNames": [
        "id"
      ],
      "autoGenerate": true
    },
    "indices": [
      {
        "name": "index_bookentrycontacts_bookentry_id",
        "unique": false,
        "columnNames": [
          "bookentry_id"
        ],
        "createSql": "CREATE  INDEX `index_bookentrycontacts_bookentry_id` ON `${TABLE_NAME}` (`bookentry_id`)"
      }
    ],
    "foreignKeys": [
      {
        "table": "bookentries",
        "onDelete": "CASCADE",
        "onUpdate": "NO ACTION",
        "columns": [
          "bookentry_id"
        ],
        "referencedColumns": [
          "id"
        ]
      }
    ]
  },
  {
    "tableName": "reminders",
    "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `bookentry_id` INTEGER NOT NULL, `worker_uuid` TEXT NOT NULL, `date` INTEGER NOT NULL, FOREIGN KEY(`bookentry_id`) REFERENCES `bookentries`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )",
    "fields": [
      {
        "fieldPath": "id",
        "columnName": "id",
        "affinity": "INTEGER",
        "notNull": true
      },
      {
        "fieldPath": "bookEntryId",
        "columnName": "bookentry_id",
        "affinity": "INTEGER",
        "notNull": true
      },
      {
        "fieldPath": "workerUUID",
        "columnName": "worker_uuid",
        "affinity": "TEXT",
        "notNull": true
      },
      {
        "fieldPath": "fireDate",
        "columnName": "date",
        "affinity": "INTEGER",
        "notNull": true
      }
    ],
    "primaryKey": {
      "columnNames": [
        "id"
      ],
      "autoGenerate": true
    },
    "indices": [
      {
        "name": "index_reminders_bookentry_id",
        "unique": true,
        "columnNames": [
          "bookentry_id"
        ],
        "createSql": "CREATE UNIQUE INDEX `index_reminders_bookentry_id` ON `${TABLE_NAME}` (`bookentry_id`)"
      }
    ],
    "foreignKeys": [
      {
        "table": "bookentries",
        "onDelete": "CASCADE",
        "onUpdate": "NO ACTION",
        "columns": [
          "bookentry_id"
        ],
        "referencedColumns": [
          "id"
        ]
      }
    ]
  }
]

Quel est le problème ici?

0
the_dani 26 janv. 2019 à 22:41

3 réponses

Meilleure réponse

Je changerais la requête comme suit:

@Transaction
@Query("SELECT $BOOKENTRIES.*, $CATEGORIES.$ID AS $PREFIX_CATEGORY$ID, $CATEGORIES.$NAME AS $PREFIX_CATEGORY$NAME, $CATEGORIES.$ICON_ID AS $PREFIX_CATEGORY$ICON_ID, $CATEGORIES.$COLOR AS $PREFIX_CATEGORY$COLOR FROM $BOOKENTRIES LEFT JOIN $CATEGORIES ON $BOOKENTRIES.$CATEGORY_ID = $CATEGORIES.$ID WHERE $BOOKENTRIES.$ID = :id")
fun get(id: Long): BookEntry?

Puisque je charge les données de la table "b" comme informations supplémentaires dans la table "a", je dois utiliser LEFT JOIN. (voir https://stackoverflow.com/a/6188334/5994190)

0
the_dani 27 janv. 2019 à 11:04

Le problème semble être que votre tableau Catégories est vide. Ensuite, vous n'obtenez aucun résultat en utilisant une jointure interne. Ce que vous voulez, c'est une jointure externe. Dans ce cas, une jointure gauche. Remplacez INNER par LEFT et voyez ce que vous obtenez.

0
itsLex 27 janv. 2019 à 14:24

J'ai donc examiné la requête ci-dessous que vous avez mentionnée et elle me semble bien:

SELECT $BOOKENTRIES.*, $CATEGORIES.$ID AS $PREFIX_CATEGORY$ID, 
$CATEGORIES.$NAME AS $PREFIX_CATEGORY$NAME, $CATEGORIES.$ICON_ID AS 
$PREFIX_CATEGORY$ICON_ID, $CATEGORIES.$COLOR AS $PREFIX_CATEGORY$COLOR FROM 
$BOOKENTRIES INNER JOIN $CATEGORIES ON $BOOKENTRIES.$CATEGORY_ID = 
$CATEGORIES.$ID WHERE $BOOKENTRIES.$ID = :id

Pouvez-vous s'il vous plaît partager vos tableaux afin que je puisse vous fournir la réponse exacte. Eh bien, pour l'instant, pouvez-vous essayer à nouveau sans filtre ou dans un cas comme celui-ci:

SELECT $BOOKENTRIES.*, $CATEGORIES.$ID AS $PREFIX_CATEGORY$ID, 
$CATEGORIES.$NAME AS $PREFIX_CATEGORY$NAME, $CATEGORIES.$ICON_ID AS 
$PREFIX_CATEGORY$ICON_ID, $CATEGORIES.$COLOR AS $PREFIX_CATEGORY$COLOR FROM 
$BOOKENTRIES INNER JOIN $CATEGORIES ON $BOOKENTRIES.$CATEGORY_ID = 
$CATEGORIES.$ID
0
Chetan Singh Samant 26 janv. 2019 à 20:01