title: How to implement complex queries using a Content Provider?
body: <p>I am asking this because I am not quite sure of how to work with Android Content Providers. I have a subset of my database with 8 tables and I need to create complex queries to get some of the data. My content provider works fine with simple queries. For example, I have a table Person on my <strong>PersonModel.java</strong> class and I get the data using: </p>
  
  <pre><code>String [] projection = {PersonModel.C_FIRST_NAME, PersonModel.C_LAST_NAME};
  Cursor cursor = context.getContentResolver().query(
              MyProvider.CONTENT_URI_PERSONS, projection, null,
              null, null);
  </code></pre>
  
  <p>and it works perfectly.</p>
  
  <p>On <strong>MyProvider</strong> I have a bunch of CONTENT_URI constants, on for each of my tables. </p>
  
  <pre><code>public class MyProvider extends ContentProvider {
      MyDbHelper dbHelper;
      SQLiteDatabase db;
  
  
      private static final String AUTHORITY = "com.myapp.models";
  
      //Paths for each tables
      private static final String PATH_PROFILE_PICTURES = "profile_pictures";
      private static final String PATH_PERSONS = "persons";
      private static final String PATH_USERS = "users";
      ....
  
      //Content URIs for each table
      public static final Uri CONTENT_URI_PROFILE_PICTURES = Uri
          .parse("content://" + AUTHORITY + "/" + PATH_PROFILE_PICTURES);
      public static final Uri CONTENT_URI_PERSONS = Uri.parse("content://"
          + AUTHORITY + "/" + PATH_PERSONS);
      public static final Uri CONTENT_URI_USERS = Uri.parse("content://"
          + AUTHORITY + "/" + PATH_USERS);
      ...
  
  
      private static final int PROFILE_PICTURES = 1;
      private static final int PROFILE_PICTURE_ID = 2;
      private static final int PERSONS = 3;
      private static final int PERSON_ID = 4;
      private static final int USERS = 5;
      private static final int USER_ID = 6;
  
      private static final UriMatcher sURIMatcher = new UriMatcher(
          UriMatcher.NO_MATCH);
      static {
      sURIMatcher.addURI(AUTHORITY, PATH_PROFILE_PICTURES, PROFILE_PICTURES);
      sURIMatcher.addURI(AUTHORITY, PATH_PROFILE_PICTURES + "/#",
              PROFILE_PICTURE_ID);
      sURIMatcher.addURI(AUTHORITY, PATH_PERSONS, PERSONS);
      sURIMatcher.addURI(AUTHORITY, PATH_PERSONS + "/#", PERSON_ID);
      sURIMatcher.addURI(AUTHORITY, PATH_USERS, USERS);
      sURIMatcher.addURI(AUTHORITY, PATH_USERS + "/#", USER_ID);
      ...
      }
  
  
  
    public Cursor query(Uri uri, String[] projection, String selection,
          String[] selectionArgs, String sortOrder) {
  
      // Uisng SQLiteQueryBuilder instead of query() method
      SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
  
      // check if the caller has requested a column which does not exists
      //checkColumns(projection);
  
      int uriType = sURIMatcher.match(uri);
  
      switch (uriType) {
      case PROFILE_PICTURES:
          queryBuilder.setTables(ProfilePictureModel.TABLE_PROFILE_PICTURE);
          break;
      case PROFILE_PICTURE_ID:
          // Adding the ID to the original query
          queryBuilder.appendWhere(ProfilePictureModel.C_ID + "="
                  + uri.getLastPathSegment());
      case PERSONS:
          queryBuilder.setTables(PersonModel.TABLE_PERSON);
          break;
      case PERSON_ID:
          // Adding the ID to the original query
          queryBuilder.appendWhere(PersonModel.C_ID + "="
                  + uri.getLastPathSegment());
      case USERS:
          queryBuilder.setTables(UserModel.TABLE_USER);
          break;
      case USER_ID:
          // Adding the ID to the original query
          queryBuilder.appendWhere(UserModel.C_ID + "="
                  + uri.getLastPathSegment());
  
      default:
          throw new IllegalArgumentException("Unknown URI: " + uri);
  
      }
  
      db = dbHelper.getWritableDatabase();
      Cursor cursor = queryBuilder.query(db, projection, selection,
              selectionArgs, null, null, sortOrder);
      // make sure that potential listeners are getting notified
      cursor.setNotificationUri(getContext().getContentResolver(), uri);
  
   }
  </code></pre>
  
  <p>That is a small part of my content provider. So my questions are: </p>
  
  <p>1) How do I implement a rawQuery() in my content provider? or how do I use properly my queryBuilder?, let's say I want to execute this query using several tables, renaming them and also passing the p1.id as a parameter? </p>
  
  <pre><code>SELECT p1.first_name, p1_last_name 
  FROM Person p1, Person P2, Relationship r 
  WHERE p1.id = ? AND 
        p1.id = r.relative_id AND
        p2.id = r.related_id;
  </code></pre>
  
  <p>I tried so by doing this: On my query() method (shown above) I have a new case, called GET_RELATIVES: </p>
  
  <pre><code>case GET_RELATIVES:
          db = dbHelper.getWritableDatabase();
          queryBuilder.setTables(PersonModel.TABLE_PERSON + " p1, "
                  + PersonModel.TABLE_PERSON + " p2, "
                  + RelationshipModel.TABLE_RELATIONSHIP + " r");
          queryBuilder.appendWhere("p2."+PersonModel.C_ID + "=" + uri.getLastPathSegment());
          queryBuilder.appendWhere("p2."+PersonModel.C_ID + "=" + "r.related_id");
          queryBuilder.appendWhere("p1."+PersonModel.C_ID + "=" + "r.relative_id");
  </code></pre>
  
  <p>so I defined a new PATH, CONTENT URI and add it to the UriMatcher, like this: </p>
  
  <pre><code>private static final String PATH_GET_RELATIVES = "get_relatives";
  public static final Uri CONTENT_URI_GET_RELATIVES = Uri
          .parse("content://" + AUTHORITY + "/"
                  + PATH_GET_RELATIVES);
  private static final int GET_RELATIVES = 22;
  
  private static final UriMatcher sURIMatcher = new UriMatcher(
      UriMatcher.NO_MATCH);
  static {
  ...
  sURIMatcher.addURI(AUTHORITY, PATH_GET_RELATIVES, GET_RELATIVES);
  }
  </code></pre>
  
  <p>but this does not seem to work so I think I'm probably defining something wrong on my content provider or inside the query method.</p>
  
  <p>2) I am not quite sure what is the point on having for each table a constant called TABLE_ID and adding it to the switch-case. What is that used for? How do I call it?  </p>
  
  <p>Hope anyone can help me with this, thanks in advance!    </p>