Room SELECT Query for 1-n hashmap relationship

I have the following ORM model:

Basically I want to track all public facilities of a state. So I have 1-n Relationship with a public facility like e.g. school:

public class StateWithFacilities {

    @Embedded
    State  state;
    @Relation(entity = School.class,parentColumn = "schoolId",entityColumn = "id")
    ArrayList<School> allSchools;
}

The issue is that, I'm don't quite sure how to get allLocations which belong to a school, as a school has a HashMap of locations where the value represents to cost of the building.

My 1) idea looks like this:

public class StateWithFacilities {

    @Embedded
    State  state;
    @Relation(entity = School.class,parentColumn = "schoolId",entityColumn = "id")
    ArrayList<SchoolWithLocations> allSchools;
}

public class SchoolWithLocations {
    @Embedded
    School school;
    @Relation(entity = Location.class,parentColumn = "locationId",entityColumn = "id")
    HashMap<Location, float> alllocationsWithCost;
}

The proposed idea wouldn't work, since I can't keep track of the cost of the building. What would the best solution be? I want to have the implemented, rather than creating a new table/entity class unless I don't have another option.

I think you might be causing yourself angst because of how you are handling the relationships.

Considering StateWithFacilities

You appear to be saying get the school who's id is equal to the schoolId column in the state.

While the realtionship should be from a column in the School that stores the appropriate StateId.

You appear to be using the reverse.

Example

Perhaps consider this example based upon what you appear to be wanting to do: (States, Schools and Locations have been given a name column to make the output easier to understand)-

State class (Entity and therefore Table) which is top of the hierarchy.

@Entity
public class State {
    @PrimaryKey
    Long stateId;
    String stateName;
    // etc //

    public State(){}
    @Ignore
    public State(String stateName){
        this.stateName = stateName;
    }
    .... getters and setters
}

School class (Entity and therefore Table) which will belong to a State.

@Entity(
        foreignKeys = {
                @ForeignKey(entity = State.class,parentColumns = "stateId",childColumns = "stateIdMap"),
        },
        indices = {@Index("stateIdMap")}
)
public class School {
    @PrimaryKey
    Long schoolId;
    Long stateIdMap;
    String schoolName;
    // etc

    public School(){}
    @Ignore
    public School(String schoolName, long stateId) {
        this.schoolName = schoolName;
        this.stateIdMap = stateId;

    }
    .... getters and setters
}

• ForeignKeys aren't necessary but can assist with maintaining referential integrity.
• Likewise the index on the stateIdMap column isn't required but if the ForeignKeys are defined Room issues a warning if the index doesn't exist.

Location class (Entity and therefore Table) which will belong to a School (a Scholl can have many Locations).

@Entity(
        foreignKeys = {
                @ForeignKey(entity = School.class,parentColumns = "schoolId",childColumns = "schoolIdMap")
        },
        indices = {@Index("schoolIdMap")}
)
public class Location {
    @PrimaryKey
    Long locationId;
    Long schoolIdMap;
    String locationName;
    float x1;
    float y1;
    float x2;
    float y2;
    // etc

    public Location(){}
    @Ignore
    public Location(String locationName,long schoolId, float x1, float y1, float x2, float y2) {
        this.locationName = locationName;
        this.schoolIdMap = schoolId;
        this.x1 = x1;
        this.y1 = y1;
        this.x2 = x2;
        this.y2 = y2;
    }
    .... getters and setters
}

• To make the demo easier to read, Location has been given a name.

To cater for retrieving a parent with it's children the following POJO's are used :-

SchoolWithLocations

public class SchoolWithLocations {

    @Embedded
    School school;
    @Relation(entity = Location.class,parentColumn = "schoolId",entityColumn = "schoolIdMap")
    List<Location> locationList;
}

StateWithSchoolsWithLocations

public class StateWithSchoolsWithLocations {

    @Embedded
    State state;
    @Relation(entity = School.class, parentColumn = "stateId",entityColumn = "stateIdMap")
    List<SchoolWithLocations> schoolWithLocationsList;
}

A Dao AllDao with some common useful Dao's :-

@Dao
interface AllDao {

    @Insert
    long insert(State state);
    @Insert
    long[] insert(State...states);
    @Insert
    long insert(Location location);
    @Insert
    long[] insert(Location...locations);
    @Insert
    long insert(School school);
    @Insert
    long[] insert(School...schools);

    @Query("SELECT * FROM State")
    List<State> getAllStates();
    @Query("SELECT * FROM State WHERE stateId=:stateId")
    State getStateById(long stateId);
    @Query("SELECT * FROM Location")
    List<Location> getAllLocations();
    @Query("SELECT * FROM Location WHERE locationId=:locationId")
    Location getLocationById(long locationId);
    @Query("SELECT * FROM Location WHERE x1=:x1 AND y1=:y1 AND x2=:x2 AND y2=:y2")
    Location getLocationByCoords(float x1,float y1,float x2,float y2);
    @Query("SELECT * FROM School")
    List<School> getAllSchools();

    @Transaction
    @Query("SELECT * FROM State")
    List<StateWithSchoolsWithLocations> getStateWithSchoolsAndLocations();

    @Transaction
    @Query("SELECT * FROM State WHERE stateId=:stateId")
    List<StateWithSchoolsWithLocations> getStateByIdWithSchoolsAndLocations(long stateId);
}

A Database class TheDatabase

@Database(entities = {State.class,Location.class,School.class},exportSchema = false,version = 1)
abstract class TheDatabase extends RoomDatabase {
    abstract AllDao getAllDao();

    private static volatile TheDatabase instance;

    public static TheDatabase getInstance(Context context) {
        if (instance == null) {
            instance = Room.databaseBuilder(
                    context,
                    TheDatabase.class,
                    "state.db"
            )
                    .allowMainThreadQueries()
                    .build();
        }
        return instance;
    }
}

And finally and activity to demonstrate (run on the main thread) :-

public class MainActivity extends AppCompatActivity {

    TheDatabase db;
    AllDao dao;
    static final String TAG = "StateINFO";

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        //Instantiate Database and get dao
        db = TheDatabase.getInstance(this);
        dao = db.getAllDao();
        // Add 3 states
        long s1Id = dao.insert(new State("State1"));
        long s2Id = dao.insert(new State("State2"));
        // Add 2 Schools (in State1)
        long sc1 = dao.insert(new School("School1 in State1",s1Id));
        long sc2 = dao.insert(new School("School2 in State1",s1Id));
        // Add 4 Locations
        long l1Id = dao.insert(new Location("Loc1",sc1,1f,1f,2f,2f));
        long l2Id = dao.insert(new Location("Loc2",sc1,2f,2f,3f,3f));
        long l3Id = dao.insert(new Location("Loc3",sc1,3f,3f,4f,4f));
        long l4Id = dao.insert(new Location("Loc4",sc2,4f,4f,5f,5f));

        // Get Everything via State
        for (StateWithSchoolsWithLocations swswl: dao.getStateWithSchoolsAndLocations() ) {
            Log.d(TAG,"State is " + swswl.state.stateName);
            for (SchoolWithLocations s: swswl.schoolWithLocationsList) {
                Log.d(TAG,"\tSchool is " + s.school.schoolName);
                for (Location l: s.locationList) {
                    Log.d(TAG,"\t\tLocation is " + l.locationName + " XYvalues are X1=" + l.x1 + " Y1=" + l.y2 + " X2=" + l.x2 + " Y2=" + l.y2);
                }
            }
        }
    }
}

Result

As can be seen it's easy to retrieve all the locations and the x1..Y2 values. The log, when the above is run, includes :-

2021-06-13 08:53:40.748 D/StateINFO: State is State1
2021-06-13 08:53:40.748 D/StateINFO:    School is School1 in State1
2021-06-13 08:53:40.748 D/StateINFO:        Location is Loc1 XYvalues are X1=1.0 Y1=2.0 X2=2.0 Y2=2.0
2021-06-13 08:53:40.748 D/StateINFO:        Location is Loc2 XYvalues are X1=2.0 Y1=3.0 X2=3.0 Y2=3.0
2021-06-13 08:53:40.748 D/StateINFO:        Location is Loc3 XYvalues are X1=3.0 Y1=4.0 X2=4.0 Y2=4.0
2021-06-13 08:53:40.748 D/StateINFO:    School is School2 in State1
2021-06-13 08:53:40.748 D/StateINFO:        Location is Loc4 XYvalues are X1=4.0 Y1=5.0 X2=5.0 Y2=5.0
2021-06-13 08:53:40.748 D/StateINFO: State is State2

---

I want to have the implemented, rather than creating a new table/entity class unless I don't have another option.

Whether or not the above could be used to make appropriate corrections to keep your current tables is something that you would have to determine.

---

---

Additional re HashMaps

The method added to SchoolWithLocations POJO :-

public HashMap<String,Float> getLocationsAsHashMap() {
    HashMap<String,Float> rv = new HashMap<>();
    for (Location l: locationList) {
        String basekey = this.getClass().getSimpleName() + (rv.size() + 1);
        rv.put(basekey+"x1",l.x1);
        rv.put(basekey+ "y1",l.y1);
        rv.put(basekey+"x2",l.x2);
        rv.put(basekey+"y2",l.y2);
    }
    return rv;
}

The method added to School

public HashMap<String,Float> getLocationsAsHashMap(AllDao dao) {
    HashMap<String,Float> rv = new HashMap<>();
    for(Location l: dao.getLocationsBySchool(schoolId)) {
        String basekey = this.getClass().getSimpleName() + (rv.size() + 1);
        rv.put(basekey+"x1",l.x1);
        rv.put(basekey+ "y1",l.y1);
        rv.put(basekey+"x2",l.x2);
        rv.put(basekey+"y2",l.y2);
    }
    return rv;
}

• Notice the Subtle differences. As the School object does not contain the Locations then these need to be retrieved from the database. Hence, it needs an instance of the AllDao as it uses a dao to get the Locations.

AllDao

The following was added to AllDao to facilitate getting the applicable Locations for the School :-

@Query("SELECT * FROM location WHERE schoolIdMap=:schoolId")
List<Location> getLocationsBySchool(long schoolId);

The Amended loop that traverses the retrieved List of StateWithSchoolsWithLocations

    // Get Everything via State
    HashMap<String,Float> locations = new HashMap<>(); //<<<<< ADDED
    HashMap<String,Float> locationsFromSchool = new HashMap<>(); //<<<<<ADDDED
    for (StateWithSchoolsWithLocations swswl: dao.getStateWithSchoolsAndLocations() ) {
        Log.d(TAG,"State is " + swswl.state.stateName);
        for (SchoolWithLocations s: swswl.schoolWithLocationsList) {
            Log.d(TAG,"\tSchool is " + s.school.schoolName);
            for (Location l: s.locationList) {
                Log.d(TAG,"\t\tLocation is " + l.locationName + " XYvalues are X1=" + l.x1 + " Y1=" + l.y2 + " X2=" + l.x2 + " Y2=" + l.y2);
            }
            /* ADDED get HashMap of Locations */
            locations = s.getLocationsAsHashMap();
            /* OR */
            locationsFromSchool = s.school.getLocationsAsHashMap(dao);
            Float value = 99.99999F; //<<<<< ADDED for setting a breakpoint
        }
    }

Result of the Amended Code

A breakpoint was added to the Line Float value = 99.99999F and run in debug mode.

When the Breakpoint was first hit (first StateWithSchoolsAndWithLocations) the debug window was :-

The Second Breakpoint :-