javaandroidkotlinandroid-room
@Query says it does not have the fields, although I have them in the query itself -> Kotlin Android Room
I got this code in my DAO:
@Query("select Conversation.*, User.* from Conversation join User on Conversation.createdBy = User.userUuid where conversationUuid = :conversationUuid")
fun selectAllForOverview(conversationUuid: UUID): LiveData<List<ConversationSelectAllForOverview>>
This is ConversationSelectAllForOverview
data class ConversationSelectAllForOverview(
@Embedded(prefix = "arg0")
val arg0: DbConversation,
@Embedded(prefix = "arg1")
val arg1: DbUser
)
I read that I needed to annotate my fields with a prefix to get rid of errors when they have common field names. I get this error and I don't know how I can remove it. I am 100% sure all the columns are available, since DbConversation and DbUser are just generated from the database. How can I fix this problem? DbConversation and DbUser share some columns, see the definition of DbConversation here: https://gist.github.com/Jasperav/381243e7b3cf387bfc0e9f1343f9faeb. DbUser looks the same.
error: The columns returned by the query does not have the fields
[conversationUuid,createdBy,tsCreated,distanceMapped,showOnMap,showOnOverview,allowMessagesByInRangeRegularUsers,allowMessagesByOutOfRangeRegularUsers,stillReadableForOutOfRangeRegularUsers,freedomInReplies,title,subject,likes,latitude,longitude,hasPassword,isSubscribed,showOnMapScreen,isLiked,bypassChecks,isHidden,nsfw,currentDirectEvents,totalDirectEventsAfterLastJoin,subscriptions,userUuid,username,karma,tsCreated,allowsPrivateChats,allowsNsfw,thisUserBlockedCurrentUser,incomingFriendshipRequest,outstandingFriendshipRequest,friends,bio,appRoleMapped]
in entity.ConversationSelectAllForOverview even though they are
annotated as non-null or primitive. Columns returned by the query:
[conversationUuid,createdBy,tsCreated,distanceMapped,showOnMap,showOnOverview,allowMessagesByInRangeRegularUsers,allowMessagesByOutOfRangeRegularUsers,stillReadableForOutOfRangeRegularUsers,freedomInReplies,title,subject,likes,avatar,latitude,longitude,hasPassword,isSubscribed,showOnMapScreen,isLiked,bypassChecks,isHidden,conversationReportReasonMapped,nsfw,currentDirectEvents,totalDirectEventsAfterLastJoin,lastReadConversationEventPk,mostRecentConversationEventUuid,relevance,subscriptions,userUuid,username,karma,tsCreated,allowsPrivateChats,allowsNsfw,avatar,currentUserBlockedThisUserTsCreated,thisUserBlockedCurrentUser,searchScreenScore,recentSearchedTsCreated,userReportReasonMapped,incomingFriendshipRequest,outstandingFriendshipRequest,friends,bio,appRoleMapped]
public abstract androidx.lifecycle.LiveData<java.util.List<entity.ConversationSelectAllForOverview>>
selectAllForOverview(@org.jetbrains.annotations.NotNull()
Solution
The Issue
You are prefixing the columns in the @Embedded annotation in conjunction with the output columns not being prefixed according to the query.
For example the ConversationSelectAllForOverview class is expecting to find the column named arg0conversationUuid in the output/result of the query but the query only has the column conversationUuid.
The Fix
Instead of using select Conversation.*, User.* .... you need to use
select Conversation.conversationUuid AS arg0conversationUuid, Conversation.createdBy AS arg0createdBy ....
ASgiving the output conversationUuid column an alias of arg0conversationUuid etc.
i.e. for every column in both tables you have to alias the actual column with it's prefix.
e.g. using (only partially adjusted):-
@Query("select " +
"Conversation.conversationUuid AS arg0conversationUuid" +
", Conversation.createdBy AS arg0createdBy" +
", Conversation.tsCreated AS arg0tsCreated" +
", Conversation.distanceMapped AS arg0distanceMapped" +
/* .... */
", User.* from Conversation join User on Conversation.createdBy = User.userUuid where conversationUuid = :conversationUuid")
fun selectAllForOverview(conversationUuid: UUID): LiveData<List<ConversationSelectAllForOverview>>
- the message is then
The columns returned by the query does not have the fields [showOnMap,showOnOverview ....- i.e. conversationUuid, createdBy, tsCreated and distanceMapped are now not included in the field-column mismatch list.
Alternative Fix (untested and reliant upon Room 2.5.0 libraries )
Another solution, which may work with Room 2.5.0 (untested) would be to use the @Relation annotation instead of the @Embedded annotation for the child(ren). e.g. without any other changes other than:-
data class ConversationSelectAllForOverview(
@Embedded/*(prefix = "arg0")*/
val arg0: DbConversation,
@Relation(
entity = DbUser::class,
parentColumn = "createdBy",
entityColumn = "userUuid"
)
val arg1: DbUser
)
and then using :-
@Transaction
@Query("SELECT * FROM Conversation WHERE conversationUUid=:conversationUuid")
fun selectAllForOverview(conversationUuid: UUID): LiveData<List<ConversationSelectAllForOverview>>
Then it compiles successfully (again not run, see below). i.e. there are no issues with the duplicated columns (again see below).
You could also use (note the function name being different to allow both to be compiled):-
@Transaction
@Query("select Conversation.*,User.* from Conversation join User on Conversation.createdBy = User.userUuid where conversationUuid = :conversationUuid")
fun selectAllForOverviewAlt(conversationUuid: UUID): LiveData<List<ConversationSelectAllForOverview>>
- However the JOINed columns are superfluous as Room uses the
@Relationshipparameters to then build the DbUser object via a subsequent query (hence the @Transaction).
NOTE Room used to take the last value found, for a like named output column, and apply that last value to all of the fields so named. This has reportedly been fixed with 2.5.0. This hasn't been confirmed as being the case though. As such you may get unintended results, so if you take this approach you should confirm that values are as expected (i.e. check the values of the like named columns/fields).
Additional a Demo
The following is a working demo based upon the code as per the question DbConversation class. BUT, to simplify matters other code has been made up and additionally many of the fields have been commented out. LiveData has been commented out and .allowMainThreadQueries to simplify the demo.
The Demo uses both fixes and for the @Relation both the original query and the suggested more consice query.
A debugging breakpoint has been used to demonstrate the sets of 3 returns.
The Database Code (everything needed for the demo plus some that isn't due to commenting out). It should be Noted that much of the code may differ, simple assumptions have been made.
:-
@Entity(
tableName = "Conversation",
primaryKeys = ["conversationUuid"],
indices = [/*Index(value = ["nsfw", "relevance"]), Index(value = ["isSubscribed"]),*/ Index(value = ["createdBy"])/*, Index(
value = ["avatar"]
), Index(value = ["mostRecentConversationEventUuid"])*/],
foreignKeys = [/*ForeignKey(
entity = DbConversationEventMostRecent::class,
childColumns = ["mostRecentConversationEventUuid"],
parentColumns = ["conversationEventUuid"],
onDelete = SET_NULL,
onUpdate = CASCADE,
), ForeignKey(
entity = DbMedia::class,
childColumns = ["avatar"],
parentColumns = ["mediaUuid"],
onDelete = CASCADE,
onUpdate = NO_ACTION,
), */ForeignKey(
entity = DbUser::class,
childColumns = ["createdBy"],
parentColumns = ["userUuid"],
onDelete = CASCADE,
onUpdate = NO_ACTION,
)]
)
data class DbConversation(
@ColumnInfo(typeAffinity = ColumnInfo.TEXT)
val conversationUuid: UUID,
@ColumnInfo(typeAffinity = ColumnInfo.TEXT)
val createdBy: UUID,
@ColumnInfo(typeAffinity = ColumnInfo.INTEGER)
val tsCreated: Long,
@ColumnInfo(typeAffinity = ColumnInfo.TEXT)
val distanceMapped: ConversationDistance,
@ColumnInfo(typeAffinity = ColumnInfo.INTEGER)
val showOnMap: Boolean,
/*
@ColumnInfo(typeAffinity = ColumnInfo.INTEGER)
val showOnOverview: Boolean,
@ColumnInfo(typeAffinity = ColumnInfo.INTEGER)
val allowMessagesByInRangeRegularUsers: Boolean,
@ColumnInfo(typeAffinity = ColumnInfo.INTEGER)
val allowMessagesByOutOfRangeRegularUsers: Boolean,
@ColumnInfo(typeAffinity = ColumnInfo.INTEGER)
val stillReadableForOutOfRangeRegularUsers: Boolean,
@ColumnInfo(typeAffinity = ColumnInfo.INTEGER)
val freedomInReplies: Boolean,
@ColumnInfo(typeAffinity = ColumnInfo.TEXT)
val title: String,
@ColumnInfo(typeAffinity = ColumnInfo.TEXT)
val subject: String,
@ColumnInfo(typeAffinity = ColumnInfo.INTEGER)
val likes: Long,
@ColumnInfo(typeAffinity = ColumnInfo.TEXT)
val avatar: UUID?,
@ColumnInfo(typeAffinity = ColumnInfo.REAL)
val latitude: Double,
@ColumnInfo(typeAffinity = ColumnInfo.REAL)
val longitude: Double,
@ColumnInfo(typeAffinity = ColumnInfo.INTEGER)
val hasPassword: Boolean,
@ColumnInfo(typeAffinity = ColumnInfo.INTEGER)
val isSubscribed: Boolean,
@ColumnInfo(typeAffinity = ColumnInfo.INTEGER)
val showOnMapScreen: Boolean,
@ColumnInfo(typeAffinity = ColumnInfo.INTEGER)
val isLiked: Boolean,
@ColumnInfo(typeAffinity = ColumnInfo.INTEGER)
val bypassChecks: Boolean,
@ColumnInfo(typeAffinity = ColumnInfo.INTEGER)
val isHidden: Boolean,
@ColumnInfo(typeAffinity = ColumnInfo.TEXT)
val conversationReportReasonMapped: ConversationReportReason?,
@ColumnInfo(typeAffinity = ColumnInfo.INTEGER)
val nsfw: Boolean,
@ColumnInfo(typeAffinity = ColumnInfo.INTEGER)
val currentDirectEvents: Long,
@ColumnInfo(typeAffinity = ColumnInfo.INTEGER)
val totalDirectEventsAfterLastJoin: Long,
@ColumnInfo(typeAffinity = ColumnInfo.BLOB)
val lastReadConversationEventPk: ConversationEventPk?,
@ColumnInfo(typeAffinity = ColumnInfo.TEXT)
val mostRecentConversationEventUuid: UUID?,
@ColumnInfo(typeAffinity = ColumnInfo.REAL)
val relevance: Double?,
@ColumnInfo(typeAffinity = ColumnInfo.INTEGER)
val subscriptions: Long
*/
)
@Dao
interface AllDAOs {
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(dbUser: DbUser): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(dbConversation: DbConversation): Long
@Query("select " +
"Conversation.conversationUuid AS arg0conversationUuid" +
", Conversation.createdBy AS arg0createdBy" +
", Conversation.tsCreated AS arg0tsCreated" +
", Conversation.distanceMapped AS arg0distanceMapped" +
", Conversation.showOnMap AS arg0showOnMap" +
/* .... */
",User.userUuid AS arg1userUuid" + /*?????? made up/incomplete/asssumed */
",User.userName AS arg1userName" +
" from Conversation join User on Conversation.createdBy = User.userUuid where conversationUuid = :conversationUuid")
fun selectAllForOverviewOld(conversationUuid: UUID): /*LiveData<*/List<OldConversationSelectAllForOverview>/*>*/
@Query("SELECT * FROM Conversation WHERE conversationUuid=:conversationUuid")
fun selectConversationByUuid(conversationUuid: UUID): List<DbConversation>
@Query("SELECT * FROM Conversation WHERE conversationUuid=:conversationUuid")
fun selectAllForOverview(conversationUuid: UUID): /*LiveData<*/List<ConversationSelectAllForOverview>/*>*/
@Query("select Conversation.*,User.* from Conversation join User on Conversation.createdBy = User.userUuid where conversationUuid = :conversationUuid")
fun selectAllForOverviewAlt(conversationUuid: UUID): /*LiveData<*/List<ConversationSelectAllForOverview>/*>*/
}
data class ConversationDistance(
val blah: String
)
data class ConversationReportReason(
val blah: String
)
data class ConversationEventPk(
val blah: ByteArray
)
class RoomTypeConverters {
@TypeConverter
fun fromConversationDistanceToJSON(conversationDistance: ConversationDistance): String = Gson().toJson(conversationDistance)
@TypeConverter
fun toConversationDistanceFromJSON(json: String): ConversationDistance = Gson().fromJson(json,ConversationDistance::class.java)
@TypeConverter
fun fromConversationReportReasonToJSON(conversationReportReason: ConversationReportReason): String = Gson().toJson(conversationReportReason)
@TypeConverter
fun toConversationReportReasonFromJSON(json: String): ConversationReportReason = Gson().fromJson(json,ConversationReportReason::class.java)
@TypeConverter
fun fromConversationEventPkToByteArray(conversationEventPk: ConversationEventPk): ByteArray = ByteArray(100)
@TypeConverter
fun toConversationEventPkFromByteArray(byteArray: ByteArray): ConversationEventPk = ConversationEventPk(byteArray)
}
@Entity(tableName = "User")
data class DbUser(
@PrimaryKey
val userUuid: UUID,
val userName: String
)
@Entity
data class DbMedia(
@PrimaryKey
val mediaUuid: UUID,
val mediaName: String
)
@Entity
data class DbConversationEventMostRecent(
@PrimaryKey
val conversationEventUuid: UUID
)
data class ConversationSelectAllForOverview(
@Embedded/*(prefix = "arg0")*/
val arg0: DbConversation,
@Relation(
entity = DbUser::class,
parentColumn = "createdBy",
entityColumn = "userUuid"
)
val arg1: DbUser
)
data class OldConversationSelectAllForOverview(
@Embedded(prefix = "arg0")
val arg0: DbConversation,
@Embedded(prefix = "arg1")
val arg1: DbUser
)
@TypeConverters(value = [RoomTypeConverters::class])
@Database(entities = [DbMedia::class,DbUser::class,DbConversationEventMostRecent::class,DbConversation::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() {
abstract fun getAllDAOs(): AllDAOs
companion object {
private var instance: TheDatabase? = null
fun getInstance(context: Context): TheDatabase {
if (instance==null) {
instance=Room.databaseBuilder(context,TheDatabase::class.java,"the_database")
.allowMainThreadQueries() /* For brevity of demo */
.build()
}
return instance as TheDatabase
}
}
}
The Activity Code:-
class MainActivity : AppCompatActivity() {
lateinit var db: TheDatabase
lateinit var dao: AllDAOs
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
db = TheDatabase.getInstance(this)
dao = db.getAllDAOs()
val u1uuid = UUID(10L,10L)
val u2uuid = UUID(11L,12L)
val c1uuid = UUID(20L,20L)
val c2uuid = UUID(21L,21L)
val c3uuid = UUID(22L,10L)
val c4uuid = UUID(15L,10L)
dao.insert(DbUser(u1uuid,"Fred"))
dao.insert(DbUser(u2uuid,"Mary"))
dao.insert(DbConversation(c1uuid,u1uuid,0, ConversationDistance("blah blah blah"),true))
dao.insert(DbConversation(c2uuid,u2uuid,0,ConversationDistance("uhm uhm uhm"),true))
dao.insert(DbConversation(c3uuid,u1uuid,0,ConversationDistance("meh meh meh"),true))
dao.insert(DbConversation(c4uuid,u1uuid,0,ConversationDistance("good good good"),true))
val c1 = dao.selectConversationByUuid(c1uuid)
val c2 = dao.selectConversationByUuid(c2uuid)
val c3 = dao.selectConversationByUuid(c3uuid)
val c4 = dao.selectConversationByUuid(c4uuid)
val t1c1 = dao.selectAllForOverviewOld(c1uuid)
val t1c2 = dao.selectAllForOverview(c1uuid)
val t1c3 = dao.selectAllForOverviewAlt(c1uuid)
val t2c1 = dao.selectAllForOverviewOld(c2uuid)
val t2c2 = dao.selectAllForOverview(c2uuid)
val t2c3 = dao.selectAllForOverviewAlt(c2uuid)
if (t1c1==null) t1c1==null /*<<<<<<<<<< BREAKPOINT HERE >>>>>>>>>>*/
}
}
When run from a fresh install, then The debug window, with t1?? and t2?? expanded:-
As can be seen all 3 queries produce the same result. As such the simplest solution would be to
- ensure that you are using Room 2.5.0 libraries, AND
- use
@Relationinstead of@Embeddedand use the more concise query just extracting the relevant DbConversation(s).
- Restricting access to static methds with ArchUnit
- Java day of the week from string
- Populating Spinner using ArrayList in Android
- Ionic Capacitor - Unknown Kotlin JVM target: 21
- Singleton & Multithreading in Java
- Maven version management w multiple child modules
- get number of working days in specific month
- Issue with Regex Pattern (split string with characters & numbers)
- Why would I ever create a separate mutex/lock object?
- Happens before guarantee in Volatile
- android.security.KeyStoreException: Signature/MAC verification failed
- array of parameterized types
- JavaFX WARNING: Unsupported JavaFX configuration: classes were loaded from 'unnamed module @...'
- unit test @Valid of springboot without having to spin up / mock the server
- Why dec 31 2010 returns 1 as week of year?
- Performance Difference of AtomicInteger vs Integer
- how do i restric a float value to only one placess after decimal in kotlin android
- consecutive pairs divisible by 3
- How to call a service from Main application calls Spring Boot?
- Setting Namespace Attributes on an Element
- How to find jUnit tests in a Java project?
- validate the credit card expiry date using java?
- A failure occurred while executing com.android.build.gradle.internal.tasks.FinalizeBundleTask$BundleToolRunnable
- Is Java completely Platform Independent?
- What does it exactly mean to use @DirtiesContext annotation on a class?
- Spring JBDC bad SQL grammar on PostgreSQL
- Test REST APIs on a real server (without localhost )
- How can I pass an array of objects in query params in postman for the post request?
- Is there a way to do 24-hour format and 60-minute format in Java?
- Removing files installed by previous version in Inno Setup