Search code examples
androidormactiveandroid

ActiveAndroid: Subqueries?

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.

Solution

  • I still don't know how to do sub-queries with ActiveAndroid (if its even possible), but I found out the source of my problem. I was using an outdated version of the library and there was some kind of error in the query system that screwed up the results. With the current version of the library I do not get duplicates.