How to query nested Embeded objects using Room Persistance Library in android?

Question

Consider I have 3 classes User, Address, Location

class Address {
    public String street;
    public String state;
    public String city;

    @ColumnInfo(name = "post_code")
    public int postCode;

    @Embedded(prefix = "home_")
    public Location homeLocation;

    @Embedded(prefix = "office_")
    public Location officeLocation;
}

class Location{
     public long lat;
     public long lng;
}

@Entity
class User {
    @PrimaryKey
    public int id;

    public String firstName;

    @Embedded(prefix = "addr_")
    public Address address;
}

How should i write the query to get the users whose home location is between certain latitude and longitude boundary ?

Ex: If i want to find all users whose home location is between these two points Location1(13.135795,77.360348) & Location2(12.743639, 77.901424). My query would look something like this -

select * from User where address.homelocation.lat < :l1_latitude && address.homelocation.lat > l2_latitude && address.homelocation.lng > :l1_longitude && address.homelocation.lng < :l2_longitude

If i have to use prefix in the embedded location from my understanding, correct me if am wrong, all the fields inside address will get appended with prefix. So i can query city as addr_city and if i have to query lat inside the homeLocation then will it become addr_home_lat ?

Is nested embedded objects permitted in room database? If yes then how do i query the nested embedded objects?

Need some help here. Thank you.

Solution

Yes "nested embedded objects" are permitted inside ROOM. You can write a User class which has an embedded Address class as which contains a embedded Location class.

Each time when an embedded object is added, room flattens out the table. In your case room generates a table called "User" with the following columns:

id, firstName, addr_street, addr_state, addr_city, addr_post_code, addr_home_lat, addr_home_lng, addr_office_lat, addr_office_lng

So your query should be like :

@Query("SELECT * FROM User WHERE " +
        "addr_home_lat BETWEEN :lat1 AND :lat2" +
        " AND addr_home_lng BETWEEN :lng1 AND :lng2")
List<User> findInRange(long lat1, long lat2, long lng1, long lng2);

Notice that "lat" has been flattened to "addr_home_lat" and "lng" to "addr_home_lng". So you can use these column names for applying the filter logic.

In case you misspelled a column name for example "home_lng" instead of "addr_home_lng", then room will find that out and give you an error :

There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (no such column: home_lng)

For more information on room database, check out the Google I/O talk.

Comments

[SkyTreasure]

Hey thanks dude :) There was a reference to Location from one more dao file, unfortunately i wasn't getting any error for that so that was causing issue :)

[Sonu Sanjeev]

@SkyTreasure Okay. Thanks for that additional information :).