Get data from Junction Table with Android Room

Question

I am currently building an android app, which displays a Route, which is constructed out of multiple waypoints. I already planned the database schema (chen-notation [possibly invalid "syntax"]):

I tried to recreate the n-m relation with android room, but I can't figure out how I can retrieve the index_of_route attribute of the junction table (route_waypoint).

I want the junction table attribute index_of_route, when I get the Data like so:

    @Transaction
    @Query("SELECT * FROM POIRoute")
    List<RouteWithWaypoints> getRoutes();

inside the POIWaypoint class (maybe as extra attribute), or at least accessible from another class which maybe is implemented like so:

    @Embedded
    POIWaypoint waypoint;
    int indexOfRoute;

Currently I don't get the indexOfRoute attribute from the junction table.

My already created classes:

RouteWithWaypoints:

public class RouteWithWaypoints {

    @Embedded
    private POIRoute poiRoute;

    @Relation(parentColumn = "id",entityColumn = "id",associateBy = @Junction(value = RouteWaypoint.class, parentColumn = "routeId", entityColumn = "waypointId"))
    private List<POIWaypoint> waypoints;

    public POIRoute getPoiRoute() {
        return poiRoute;
    }

    public void setPoiRoute(POIRoute poiRoute) {
        this.poiRoute = poiRoute;
    }

    public List<POIWaypoint> getWaypoints() {
        return waypoints;
    }

    public void setWaypoints(List<POIWaypoint> waypoints) {
        this.waypoints = waypoints;
    }

RouteWaypoint:

@Entity(primaryKeys = {"waypointId", "routeId"}, foreignKeys = {
        @ForeignKey(entity = POIWaypoint.class, parentColumns = {"id"}, childColumns = {"waypointId"}),
        @ForeignKey(entity = POIRoute.class, parentColumns = {"id"}, childColumns = {"routeId"})
})
public class RouteWaypoint {
    private int waypointId;
    private int routeId;
    
    // I want this attribute inside the POIWaypoint class
    @ColumnInfo(name = "index_of_route")
    private int indexOfRoute;

    public int getWaypointId() {
        return waypointId;
    }

    public void setWaypointId(int waypointId) {
        this.waypointId = waypointId;
    }

    public int getRouteId() {
        return routeId;
    }

    public void setRouteId(int routeId) {
        this.routeId = routeId;
    }
}

POIRoute:

@Entity
public class POIRoute{

    private String name;
    private String description;
    @PrimaryKey(autoGenerate = true)
    private int id;
    private boolean user_generated;
    private int parentId;


    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public boolean isUser_generated() {
        return user_generated;
    }

    public void setUser_generated(boolean user_generated) {
        this.user_generated = user_generated;
    }

    public int getParentId() {
        return parentId;
    }

    public void setParentId(int parentId) {
        this.parentId = parentId;
    }


}

POIWaypoint (please ignore the position attribute it isn't finished):

@Entity
public class POIWaypoint {
    @PrimaryKey(autoGenerate = true)
    private long id;
    @ColumnInfo(name = "long_description")
    private String longDescription;
    private String title;
    @ColumnInfo(name = "short_description")
    private String shortDescription;

    // use converter: https://developer.android.com/training/data-storage/room/referencing-data
    @Ignore
    private GeoPoint position;

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public GeoPoint getPosition() {
        return position;
    }

    public void setPosition(GeoPoint position) {
        this.position = position;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getShortDescription() {
        return shortDescription;
    }

    public void setShortDescription(String shortDescription) {
        this.shortDescription = shortDescription;
    }

    public String getLongDescription() {
        return longDescription;
    }

    public void setLongDescription(String longDescription) {
        this.longDescription = longDescription;
    }

Lael

It seems that you've done it well. But what is exactly your problem? You don't get some field in RouteWithWaypoints, do you? It's not clear – Klapp

Edited my post to make more specific was my problem is and what I want to accomplish. In short: currently I have no access to the index_of_waypoint attribute from the junction table. In the best case, I want the index_of_waypoint inside the POIWaypoint class, when I get an instance of RouteWithWaypoints. – Lael

I think you're moving in right direction and you should try your ideas on practice. But I have a guess that since Relations in Room is not very universal tool, if your ideas fails you should hold in your mind that you can do all you want with Plain Old SQL and loops ) – Klapp

Answer 1

I solved my problem by manage the relation by myself. I changed my RouteDao to an abstract class to insert my own method, which manages part of the junction table by itself:

RouteDao:

 private RouteDatabase database;

    public RouteDao(RouteDatabase database) {
        this.database = database;
    }

    @Query("Select * from POIRoute")
    public abstract List<POIRoute> getRoutes();

    @Query("SELECT * FROM POIRoute WHERE id = :id")
    public abstract POIRoute getRoute(int id);

    @Insert
    abstract void insertRouteWithWaypoints(RouteWithWaypoints routeWithWaypoints);

    public List<RouteWithWaypoints> getRoutesWithWaypoints() {
        List<POIRoute> routes = this.getRoutes();
        List<RouteWithWaypoints> routesWithWaypoints = new LinkedList<>();
        for (POIRoute r : routes) {
            routesWithWaypoints.add(new RouteWithWaypoints(r, database.wayPointDao().getWaypointsFromRoute(r.getId())));
        }

        return routesWithWaypoints;
    }

    public RouteWithWaypoints getRouteWithWaypoints(int id) {
        POIRoute route = this.getRoute(id);
        RouteWithWaypoints routeWithWaypoints = null;
        if (route != null) {
            routeWithWaypoints = new RouteWithWaypoints(route, database.wayPointDao().getWaypointsFromRoute(route.getId()));
        }


        return routeWithWaypoints;
    }

WayPointDao:

    @Query("SELECT * FROM POIWaypoint")
    POIWaypoint getWaypoints();

    @Query("SELECT * FROM POIWaypoint WHERE id = :id")
    POIWaypoint getWaypoint(long id);

    @Query("SELECT pw.*, rw.index_of_route as 'index' FROM POIWaypoint as pw Join RouteWaypoint as rw on (rw.waypointId = pw.id) where rw.routeId = :id order by 'index' ASC")
    List<POIRouteStep> getWaypointsFromRoute(int id);

Lael

Well done. Still there is some field for future optimisation, since you make database calls inside loop in your getRoutesWithWaypoints method (in general it's not best choice). – Klapp