How to receive not duplicative rows using Room?

I have two related entities:

Station

@Entity(tableName = "stations")
data class Station(

    @PrimaryKey
    @ColumnInfo(name = "id")
    val id: Long,

    @ColumnInfo(name = "latitude")
    val latitude: Double,

    @ColumnInfo(name = "longitude")
    val longitude: Double,

    @ColumnInfo(name = "connectors")
    val connectors: List<Connector>, // this field has a type converter
)

Connector

@Entity(
    tableName = "connectors",
    primaryKeys = ["station_id", "station_connector_id"]
)
class Connector(

    @ColumnInfo(name = "station_connector_id")
    val stationConnectorId: Int,

    @ColumnInfo(name = "station_id")
    val stationId: Long,

    @ColumnInfo(name = "type")
    val type: ConnectorType,
)

When I insert data, I fill up both entites and it seems kinda okay, but when I’m trying to receive stations with particular types of connectors it duplicates rows. For example, I have an object

Station(
    id = 100, 
    latitude = 56.565, 
    longitude = 34.565, 
    connectors = [
        Connector(stationConnectorId=1, stationId=100, type=TYPE_2), 
        Connector(stationConnectorId=2, stationId=100, type=CHADEMO), 
        Connector(stationConnectorId=3, stationId=100, type=TYPE_1)
    ]
)

And if I want to filter stations only by one connector type I receive one row with this station(and it’s right), but if I want to reset filters and look up for stations that can contain many connectors, I receive duplicates of this stations(in this example if I request for station with TYPE_1, TYPE_2 and CHADEMO connector types it will be three equal rows).

I’m using this query to request stations from my database:

SELECT * FROM simple_stations 
INNER JOIN connectors ON simple_stations.id = connectors.station_id 
WHERE connectors.type IN (:connectorTypesList)

I’ve tried to use DISTINCT in the query, ForeignKeys and Indexes in these Entities, but it was not working, so now I’m completely lost.

Answer

If you just want Stations then you have various options then your have various options.

1. is to use a GROUP BY clause such as GROUP BY simple_stations.id

However, the issue you may then encounter is that Station would be incomplete/unreliable as you have a List of Connectors and if you GROUP by Station then you will only get a single arbitrary Connector (there again that may depend upon you TypeConvertor).

2. to use DISTINCT you would have to only include the Station columns (similar problem as above).

I’d suggest that your schema is at fault by including the List of Connectors related to the Station you are duplicating data (aka it’s not normalised).

rather if you removed

@ColumnInfo(name = "connectors")
val connectors: List<Connector>, // this field has a type converter

from the Station Entity the data itself would still be available for retrieval.

You may then wish to have a POJO that Embeds the Station and has a List of Connector’s, perhaps one with and one without the Connector List have an @Relationship (with and you would get all connectors irrespective of the WHERE clause as that’s how @Relationship works). Without and you could have the constructor get only the Connectors with the types you want.

Perhaps consider the following based upon your code:-

The Station Entity

@Entity(tableName = "stations")
data class Station(

    @PrimaryKey
    @ColumnInfo(name = "id")
    val id: Long,

    @ColumnInfo(name = "latitude")
    val latitude: Double,

    @ColumnInfo(name = "longitude")
    val longitude: Double

    /*
    @ColumnInfo(name = "connectors")
    val connectors: List<Connector> // this field has a type converter
    NO NEED IMPLIED BY RELATIONSHIP
     */
)

The Connector Entity

@Entity(
    tableName = "connectors",
    primaryKeys = ["station_id", "station_connector_id"]
)
class Connector(

    @ColumnInfo(name = "station_connector_id")
    val stationConnectorId: Int,

    @ColumnInfo(name = "station_id")
    val stationId: Long,

    @ColumnInfo(name = "type")
    val type: String //<<<<< changed for convenience
)

The StationWithConnectors POJO NEW

class StationWithConnectors {
    @Embedded
    var station: Station? = null
    var connectors: List<Connector> = emptyList()

    constructor(allDao: AllDao, station: Station, connectorTypeList: List<String>) {
        this.station = station
        this.connectors = allDao.getConnectorsOfSpecifiedTypesByStationId(station.id,connectorTypeList)
    }
}

• note the embedded query to build the list of connectors of only the specfified types

The Dao used i.e. AllDao

@Dao
interface AllDao {

    @Insert
    fun insert(station: Station): Long
    @Insert
    fun insert(connector: Connector): Long
    @Query("SELECT * FROM stations")
    fun getAllStations(): List<Station>
    @Query("SELECT * FROM stations WHERE stations.id = :stationId")
    fun getStationById(stationId: Long): Station
    
    // Gets the Connectors per Station of the requested Type (i.e. NOT ALL CONNECTORS necessarily)
    @Query("SELECT * FROM connectors WHERE station_id = :stationId AND connectors.type IN( :connectorTypesList)")
    fun getConnectorsOfSpecifiedTypesByStationId(stationId: Long, connectorTypesList: List<String>): List<Connector>
    
    // Gets the Stations that have Connectors of the requested type DISTINCT used along with only the station columns
    @Query("SELECT DISTINCT stations.id, stations.latitude, stations.longitude " +
            "FROM stations INNER JOIN connectors ON connectors.station_id = stations.id " +
            "WHERE connectors.type IN(:connectorTypesList)")
    fun getStationsWithSpecificConnectorTypes(connectorTypesList: List<String>): List<Station>
}

The @Database TheDatabase

@Database(entities = [Connector::class,Station::class],version = 1)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDao(): AllDao
}

and finaly an Activity to test/demonstrate

class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: AllDao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        db = Room.databaseBuilder(this,TheDatabase::class.java,"thedb.db")
            .allowMainThreadQueries()
            .build()
        dao = db.getAllDao()
        // Define some types
        var type1 = "TYPE_1"
        var type2 = "TYEP_2"
        var type3 = "CHADEMO"
        var type4 = "ANOTHER"

        // Define stations with Connectors
        var station1 = Station(100,56.565,34.565)
        dao.insert(station1)
        dao.insert(Connector(10,station1.id,type1))
        dao.insert(Connector(20,station1.id,type4))
        dao.insert(Connector(30,station1.id,type3))
        dao.insert(Connector(40,station1.id ,type2))

        var station2 = Station(200,33.333,22.222)
        dao.insert(station2)
        dao.insert(Connector(100,station2.id,type2))
        dao.insert(Connector(110,station2.id,type4))
        dao.insert(Connector(120,station2.id,type3))

        // Define the search types
        var listOfTypes = listOf(type1,type2) // Types to search for

        // prepare the StationWithConnectors list
        var allswcList: ArrayList<StationWithConnectors> = ArrayList()

        // Get the stations with connectors of the required types
        var stationsWithCertainTYpes = dao.getStationsWithSpecificConnectorTypes(listOfTypes)
        // Build the StationWithCertainTypes POJOs
        for(s: Station in stationsWithCertainTYpes) {
            allswcList!!.add(StationWithConnectors(dao,s, listOfTypes))
        }
        var count = stationsWithCertainTYpes.size //<<<< just so breakpoint can be added
    }
}

When run in debug mode then:-

StationWithCertainTypes gets both Stations (station 1 has type1 and type2, station2 has type2) as per :-

allswcList has the 2 StationWithConnectors built from the 2 Stations as per :-