Android Room Relations with multiple parentColumn and entityColumn

Question

Let's say that you have a Room database with two tables (entities) that are related using foreign key. (This is just a simplified example, so no need to propose a new data structure :) )

@Entity(tableName = "Streets", primaryKeys = {"Country", "City"})
public class Street {
...
}

@Entity(tableName = "Users",
    primaryKeys = {"Country", "City", "Name"},
    foreignKeys = @ForeignKey(entity = Street.class,
            parentColumns = {"Country", "City"},
            childColumns = {"Country", "City"},
            onDelete = CASCADE))
public class User {
...
}

How can I retrieve all the information from the database in one query? Below works almost as expected, but I would need to add the City column as well, but how can this be done? How do you add multiple parentColumn and entityColumn?

@Query("SELECT * FROM Streets")
public abstract LiveData<List<PoJo>> getAllUsers();

public static class PoJo {
    @Embedded
    private Street street;
    @Relation(parentColumn = "Country", entityColumn = "Country")
    private List<User> mUsers;
}

Brilliant

Answer 1

This answer will not address the question for the use of Room. We are using SQLite so the code will need to be styled for Room. This code is in Kotlin
First the two Model Classes ParentModel and ChildModel

data class ParentModel (
    val title : String = "",
    val children : List<ChildModel>
)

data class ChildModel(
    val image : Int = -1,
    val title : String = ""
)

Side Note we are not using the image so it is easy to loose
Now the two Adapter YEA Parent and Child not creative naming

class ChildAdapter(private val children : List<ChildModel>)
: RecyclerView.Adapter<ChildAdapter.ViewHolder>(){

override fun onCreateViewHolder(parent: ViewGroup, viewType: Int): ViewHolder {
    val v =  LayoutInflater.from(parent.context)
                  .inflate(R.layout.child_recycler,parent,false)
    return ViewHolder(v)
}

override fun getItemCount(): Int {
    return children.size
}

override fun onBindViewHolder(holder: ViewHolder, position: Int) {
    val child = children[position]
    holder.imageView.setImageResource(child.image)
    holder.textView.text = child.title
}


inner class ViewHolder(itemView : View) : RecyclerView.ViewHolder(itemView){

    val textView : TextView = itemView.child_textView
    val imageView: ImageView = itemView.child_imageView

}
}

class ParentAdapter(private val parents : List<ParentModel>) : RecyclerView.Adapter<ParentAdapter.ViewHolder>(){

private val viewPool = RecyclerView.RecycledViewPool()

override fun onCreateViewHolder(parent: ViewGroup, viewType: Int): ViewHolder {
    val v = LayoutInflater.from(parent.context).inflate(R.layout.parent_recycler,parent,false)
    return ViewHolder(v)
}

override fun getItemCount(): Int {
   return parents.size
}

override fun onBindViewHolder(holder: ViewHolder, position: Int) {
    val parent = parents[position]
    holder.textView.text = parent.title
    holder.recyclerView.apply {
        layoutManager = LinearLayoutManager(holder.recyclerView.context, LinearLayout.HORIZONTAL, false)
        adapter = ChildAdapter(parent.children)
        recycledViewPool = viewPool
    }
}


inner class ViewHolder(itemView : View) : RecyclerView.ViewHolder(itemView){
    val recyclerView : RecyclerView = itemView.rv_child
    val textView:TextView = itemView.textView
}
}

Notice in the ParentAdapter the ChildAdapter is bound to bring the info over
Now the XML associated files first the parent then the child

<android.support.v7.widget.CardView xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:card_view="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:id="@+id/card_view"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_gravity="center"
android:layout_margin="2dp"
card_view:cardBackgroundColor="#fff"
card_view:cardCornerRadius="5dp"
card_view:cardElevation="4dp"
card_view:cardUseCompatPadding="true">

<RelativeLayout
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical">

    <TextView
        android:id="@+id/textView"
        style="@style/Base.TextAppearance.AppCompat.Subhead"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentTop="true"
        android:layout_alignStart="@+id/rv_child"
        android:padding="20dp"
        android:text="Hello World"
        android:textColor="@color/colorAccent"
        android:textStyle="bold" />

    <android.support.v7.widget.RecyclerView
        android:id="@+id/rv_child"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_alignParentTop="true"
        android:layout_centerHorizontal="true"
        android:padding="20dp"
        android:layout_marginTop="30dp"
        android:orientation="horizontal"
        tools:layout_editor_absoluteX="74dp" />

</RelativeLayout>

<?xml version="1.0" encoding="utf-8"?>
<android.support.constraint.ConstraintLayout
    xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:orientation="horizontal"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content">

    <TextView
        android:id="@+id/child_textView"
        android:layout_width="129dp"
        android:layout_height="37dp"
        android:layout_alignParentStart="true"
        android:layout_alignParentTop="true"
        android:layout_marginBottom="8dp"
        android:layout_marginEnd="8dp"
        android:layout_marginStart="8dp"
        android:layout_marginTop="1dp"
        android:background="@android:color/darker_gray"
        android:padding="10dp"
        android:text="TextView"
        android:textColor="@android:color/white"
        android:textStyle="bold"
        app:layout_constraintBottom_toTopOf="@id/child_imageView"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintHorizontal_bias="0.0"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        app:layout_constraintVertical_bias="0.0" />

    <ImageView
        android:id="@+id/child_imageView"
        android:layout_width="126dp"
        android:layout_height="189dp"
        android:layout_marginBottom="38dp"
        android:layout_marginEnd="8dp"
        android:layout_marginStart="8dp"
        android:layout_marginTop="42dp"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintHorizontal_bias="1.0"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        app:layout_constraintVertical_bias="1.0"
        app:srcCompat="@drawable/aviator" />

</android.support.constraint.ConstraintLayout>

And the SQLite code to call to get the data (we have not written the Activity to implement the view yet)

fun getDepartments(): List<ParentModel>{
    val cursor = writableDatabase.query(DEPARTMENTS, arrayOf(ID, TITLE), null, null,
        null, null, ID)
    val list = mutableListOf<ParentModel>()
    if (cursor != null) {
        cursor.moveToFirst()
        while (!cursor.isAfterLast){
            val departmentId = cursor.getLong(cursor.getColumnIndex(ID))
            val items = getItems(departmentId)
            val d = ParentModel(cursor.getString(cursor.getColumnIndex(TITLE)), items)
            list.add(d)
            cursor.moveToNext()
        }
        cursor.close()
    }
    return list
}

Gluttonize