I'm using ActiveAndroid in an app for database interaction. I need to join a few tables and then filter the results, which I would usually accomplish using a SQL query with a sub-query.
How might one accomplish a sub-query using ActiveAndroid?
If a sub-query cannot be done, is there a way to only return unique Model objects? The join in my query has the effect of creating duplicate rows in the join table, but I only want one instance of each Model to be returned.
EDIT: Providing more info
The model I'm trying to retrieve is called Profile.
With regard to the query, there are 3 tables: Profiles, Roles, and ProfileRoleJoins
There is a many-to-many relationships between Profiles and Roles and that relationship is maintained by the join-table called ProfileRoleJoins.
My goal is to obtain all Profiles that have a certain "event_id" and are connected to one or more Roles specified by a list of Role types.
Of interest to this query are the following fields:
Profiles
- Id
- event_id (not a foreign key, just a data field)
Roles
- Id
- type (TEXT)
ProfileRoleJoins
- profile (foreign key)
- role (foreign key)
What I've Tried
String eventId = //provided as input to this function
String[] roles = //array of "types" of roles we want to limit to
String whereClause = //built in a loop, looks like "Roles.type = ? OR Roles.type = ?..."
new Select()
.from(ProfileEntity.class)
.where("event_id = ?", eventId)
.join(ProfileRoleJoinsTable.class)
.on("Profiles.Id = ProfileRoleJoins.profile")
.join(RoleEntity.class)
.on("ProfileRoleJoins.role = Roles.Id")
.where(whereClause, roles)
.execute();
The above query has the problem that it returns duplicate Profiles because a single Profile can be associated with multiple Roles which creates duplicates during the join process.
I need to retrieve a list of unique Profiles.
I tried using Distinct(), but that qualifier applies per set of columns - I need entire Profiles back.
The way I would normally accomplish the uniqueness is by doing the above query in a sub-query and return only DISTINCT Profiles.Id values. Then I would run a filter in the main query against the original Profiles table.
