Android Union Query

I have the following problem: I have a table called planing it has the following fields: idplaning, month, year, shop, target, sales so the table will contain info like this (ign

Solution 1:

use rawquery in content provider class where you override query method pass necessary column in query method with uri . and in provider class match appropriate uri and execute your raw query method you already have db object out there.

getActivity().getContentResolver().query(MyFirstProvider.CONTENT_URI_RSLDTL, col, where, null, null);

where you can retrive col and where in override query methods parameter .

public class ExampleProvider extends ContentProvider {
....
    public Cursor query(
            Uri uri,
            String[] projection,
            String selection,
            String[] selectionArgs,
            String sortOrder) { ...
            /*
             * Choose the table to query and a sort order based on the code returned for the incoming
             * URI. Here, too, only the statements for table 3 are shown.
             */
            switch (sUriMatcher.match(uri)) {

                  break;

                default:
                ...
                    // If the URI is not recognized, you should do some error handling here.
            }
             /*
         * Gets a writeable database. This will trigger its creation if it doesn't already exist.
         *
         */
        db = mOpenHelper.getWritableDatabase();
        //execute raw query
        db.rawQuery(...);

        }

Solution 2:

I haven't found any way to use a query instead of rawquery when you want to do a UNION query.

I'm brought to think it doesn't currently exist by the fact that SQLiteQueryBuilder class contains methods to build a UNION query, but it doesn't contain any method to execute it, and I wouldn't expect to find it anywhere else.

More specifically, if you look at SQLiteQueryBuilder class, you'll find that buildUnionQuery method returns a String and there's no query method that takes a custom query String as a parameter.

That said, the solution I adopted in my code is this:

Inside your query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) switch statement...

SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
builder.setTables(TABLE_NAME);
HashSet<String> columnsPresentInTable = new HashSet<>(<columns in your table>);
String firstProjection = "shop, target as Ian, 0 as Feb, 0 as Mar, 0 as Apr, 0 as Mai, 0 as Iun, 0 as Iul, 0 as Aug, 0 as Sep, 0 as Oct, 0 as Noi, 0 as Decu";
String firstSelectSubQuery = builder.buildUnionSubQuery(
        null,
        firstProjection,
        columnsPresentInTable,
        0,
        getType(uri),
        selection,
        new String[]{
            String.valueOf(2015),
            String.valueOf(1)
        },
        null
);
[...]
String lastProjection = "shop, 0 as Ian, 0 as Feb, 0 as Mar, 0 as Apr, 0 as Mai, 0 as Iun, 0 as Iul, 0 as Aug, 0 as Sep, 0 as Oct, 0 as Noi, target as Decu";
String lastSelectSubQuery = builder.buildUnionSubQuery(
        null,
        lastProjection,
        columnsPresentInTable,
        0,
        getType(uri),
        selection,
        new String[]{
            String.valueOf(2015),
            String.valueOf(12)
        },
        null
);
String unionQuery = builder.buildUnionQuery(
        new String[]{
            firstSelectSubQuery,
            [...]
            lastSelectSubQuery
        },
        sortOrder,
        null
);
cursor = db.rawQuery(unionQuery, null);

This approach can seem to you a little more tricky than just rawQuery and maybe not the answer to your question, but I'm using it and recommending it anyway for two reasons: because when there's a method to do something I always prefer to use it, and because I believe it allows me to support future modifications, for example if a query approach is implemented, with minimum effort.