Using SQLiteQueryBuilder buildUnionSubQuery() method

Question

I have 2 tables that look like the following:

Table1

_id | _key | _mime_type | _hash
---------------------------

and

Table2

_id | _path | _mimetype
-------------------

I can get a valid cursor with the result set i'm trying to get by executing a UNION query via the following in my content providers query() method:

String unionQuery = " SELECT "
                        + Table1._ID + " AS _id, "
                        + Table1.QUICK_KEY + " AS _key, "
                        + Table1.MIME_TYPE + " AS _mime,"
                        + Table1.HASH + " AS _hash,"
                        + "NULL as _path_to_file"
                        + " FROM " + Table1
                        + " UNION "
                        + " SELECT "
                        + Table2._ID + " AS _id, "
                        + "NULL AS _key, "
                        + Table2.MIME_TYPE + " AS _mime,"
                        + "NULL AS _hash,"
                        + Table2.PATH + " as _path_to_file"
                        + " FROM " + Table2;

                return db.rawQuery(unionQuery, null);

Rather than manually writing the statement above, I would like to use the buildUnionSubQuery() method from the SQLiteQueryBuilder class.

The buildUnionSubQuery() documentation is located at http://developer.android.com/reference/android/database/sqlite/SQLiteQueryBuilder.html

I have been trying to use the method, but with no luck in creating the SQL statement that I manually write.

An example (I've tried about 10 different things) of the code I have tried is: SQLiteQueryBuilder cloudQueryBuilder = new SQLiteQueryBuilder(); SQLiteQueryBuilder localQueryBuilder = new SQLiteQueryBuilder();

                String typeDiscriminatorColumn = "_table";

                Set<String> cloudColumns = new HashSet<String>();
                cloudColumns.add(FileInfos.column(Table1._ID));
                cloudColumns.add(FileInfos.column(Table1.QUICK_KEY));
                cloudColumns.add(FileInfos.column(Table1.MIME_TYPE));
                cloudColumns.add(FileInfos.column(Table1.HASH));

                cloudQueryBuilder.setTables(Table1);
                String cloudTableQuery = cloudQueryBuilder.buildUnionSubQuery(typeDiscriminatorColumn, projection, cloudColumns, 0, Table1, selection, null, null);

with that code, this is the result I get when I log the query statement:

SELECT quickkey, NULL AS _id, NULL AS hash, NULL AS created, NULL AS mimetype, NULL AS _id, NULL AS _mimetype, NULL AS _path FROM Table1

A few other examples I've tried end up with the same result where nearly all table columns are "NULL AS "

But my goal is to have the query statement (for each SELECT statement in the UNION query) to match my manually written union statement.

Column order doesn't matter.

Any help is appreciated.

EDIT:

I know that buildUnionQueries() is used to add multiple SELECT statements, but the problem is that the SELECT statements are NOT being created as I expect them to (which means that I am using the buildUnionSubquery() method incorrectly which is what I am seeking assistance with)

EDIT:

The following code works for the most part, except that sorting does not seem to work:

SQLiteQueryBuilder cloudQueryBuilder = new SQLiteQueryBuilder();
SQLiteQueryBuilder localQueryBuilder = new SQLiteQueryBuilder();

String typeDiscriminatorColumn = "type";

                String[] cloudUnionColumns = {
                        FileInfos._ID,
                        FileInfos.QUICK_KEY,
                        FileInfos.MIME_TYPE,
                        FileInfos.CREATED,
                        FileInfos.HASH,
                        LocalItems.PATH,
                };

                Set<String> cloudColumns = new HashSet<String>();
                cloudColumns.add(FileInfos._ID);
                cloudColumns.add(FileInfos.QUICK_KEY);
                cloudColumns.add(FileInfos.MIME_TYPE);
                cloudColumns.add(FileInfos.HASH);
                cloudColumns.add(FileInfos.CREATED);

                cloudQueryBuilder.setTables(Tables.FILE_INFOS);
                String cloudTableQuery = cloudQueryBuilder.buildUnionSubQuery(typeDiscriminatorColumn, cloudUnionColumns, cloudColumns, 0, "cloud", selection, null, null);

                String[] localUnionColumns = {
                        LocalItems._ID,
                        FileInfos.QUICK_KEY,
                        LocalItems.MIME_TYPE,
                        LocalItems.CREATED,
                        FileInfos.HASH,
                        LocalItems.PATH,
                };
                Set<String> localColumns = new HashSet<String>();
                localColumns.add(LocalItems._ID);
                localColumns.add(LocalItems.PATH);
                localColumns.add(LocalItems.MIME_TYPE);
                localColumns.add(LocalItems.CREATED);

                localQueryBuilder.setTables(Tables.LOCAL_ITEMS);
                String localTableQuery = localQueryBuilder.buildUnionSubQuery(typeDiscriminatorColumn, localUnionColumns, localColumns, 0, "local", selection, null, null);

                Log.i(TAG, "cloud sub query - " + cloudTableQuery);
                Log.i(TAG, "local sub query - " + localTableQuery);

                String[] subQueries = {
                        cloudTableQuery,
                        localTableQuery,
                };
                sortOrder = "created DESC";
                String unionQuery = cloudQueryBuilder.buildUnionQuery(subQueries, sortOrder, null);
                Log.i(TAG, "query - " + unionQuery);

                return db.rawQuery(unionQuery, null);

example rows I get back:

[_id: 33][quickkey: null][mimetype: image/jpeg][created: 1413327563000][hash: null][_path: /storage/emulated/0/WhatsApp/Media/WhatsApp Images/IMG-20141014-WA0001.jpg]

[_id: 47][quickkey: null][mimetype: image/jpeg][created: 1415751020000][hash: null][_path: /storage/emulated/0/Download/dota_2___clockwerk_by_sheron1030-d855txk.jpg]

I know these rows are local items because quickkey = null and hash = null

[_id: 181][quickkey: 88hcvu3t3y832p4][mimetype: image/gif][created: 1414021932000][hash: 86a15752bb550259d89e184bb5930306a9f3c8b390837b6f9b387816b6dcd461][_path: null]

[_id: 257][quickkey: 57b35uy9f12a462][mimetype: image/png][created: 1410831493000][hash: 3ad838b6a5543760b588655ad9a1714c19ecb1b91a326a21282596b2fb650c48][_path: null]

I know these rows are cloud items because path = null

Sorting does not work though, and column names are not aliased (nor do I know how to) because I expect the "created" column to be sorted, but they are not sorted for the entire result set. instead the sorting is done for each statement separately.

A union *sub* query is just 1 part of the union, you'll need to combine multiple of those via [#buildUnionQuery(subQueries, ...)](http://developer.android.com/reference/android/database/sqlite/SQLiteQueryBuilder.html#buildUnionQuery%28java.lang.String[],%20java.lang.String,%20java.lang.String%29) — zapl, Dec 03 '14 at 20:22
@zapl yes I know, but the problem is that the SELECT statement is saying NULL AS for most columns I select (I have multiple SQLiteQueryBuilders and then I am adding. I'll add a note to the original post) — clockwerk, Dec 03 '14 at 20:23

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

2

It seems that x AS y is not supported by that method. At least not in the way you need it.

`typeDiscriminator{Column|Value}`

These parameters allow you to have a column in your result that allows you to distinguish from which union subquery a row stems:

SELECT
   'one' AS sourceTable
   ...
   FROM table1
UNION SELECT
   'two' AS sourceTable
  ...
  FROM table2

where

typeDiscriminatorColumn is "sourceTable",
typeDiscriminatorValue is "one" in the first unionSubQuery, "two" in the second
unionColumns is constant for both and must include "sourceTable"

`unionColumns` vs `columnsPresentInTable`

unionColumns is more or less the final projection. The columns get modified though, most importantly depending on columnsPresentInTable. But all it does is to either keep it untouched or replace it with "NULL AS " + unionColumnEntry if that column isn't present. That allows you to do union queries on tables that have the exact same column names:

SELECT
   column1,
   column2,
   NULL AS column3
   ...
UNION SELECT
   NULL AS column1,
   column2,
   column3,
   ...

by having the following:

unionColumns as { "column1", "column2", "column3" }
columnsPresentInTable as { "column1", "column2" } in the first subquery, { "column2", "column3" } in the second.

NULL AS <whatever> in your case is because you don't have the same column names at all. And mapping via AS is unfortunately not the intention of this method.

What you can do is to use the slightly manual #buildQuery method while manually handling the NULL and AS parts

String[] projectionIn = {
   Table1._ID + " AS _id",
   ...
   "NULL as _path_to_file"
}
cloudQueryBuilder.buildQuery(projectionIn, null, null, ...);

or you write yourself a similar method that actually does what you want.

References:

sourcecode of buildUnionSubQuery

edited Jun 20 '20 at 09:12

Community

1
1

answered Dec 03 '14 at 20:53

zapl

63,179
10
123
154

@crn14 what sorting? a `Set` is unsorted, the column sort order needs to be the same in each union subquery and so column order is defined by `cloudUnionColumns`. Or is that not what you mean? ps `typeDiscriminatorColumn` needs to be part of `cloudUnionColumns` if you want to have that. Edit: oh there is an "ORDER BY" term I've overlooked. – zapl Dec 03 '14 at 23:10
@crn14 I don't see anything wrong. The ORDER BY part of a UNION query can not be applied separately: *"In a compound SELECT, all the constituent SELECTs must return the same number of result columns. As the components of a compound SELECT must be simple SELECT statements, they may not contain ORDER BY or LIMIT clauses. ORDER BY and LIMIT clauses may only occur at the end of the entire compound SELECT"* (unless you turn the subqueries into sub-sub queries like http://stackoverflow.com/a/9091211/995891 ) Does the query look good when you print it? – zapl Dec 04 '14 at 02:36
No, the query does not look good insofar as aliasing. I've tried using setProjectionMap() as well. I think maybe there is no answer. I've resolved this question by simply using buildQuery() with a projectionIn with aliasing. – clockwerk Dec 04 '14 at 21:32
1

@crn14 There is no answer with this method. If you look at the sourcecode, you can see that it has two mentions of `AS`, one for the typedescriminator, one for the `NULL AS` part. It can't do any further aliasing, and it would have a hard time figuring it out because you provide just a bunch of column names without telling it which ones belong where. The method would need a `Map` or something similar as parameter. – zapl Dec 04 '14 at 22:07
1

I ended up just using several: SQLiteQueryBuilder.buildQuery() and passing in my own projection with AS statements and it works fine when I use SQLiteQueryBuilder.buildUnionQuery(). What a shame that I can't use aliasing with their method in conjunction with setProjectionMap() – clockwerk Dec 05 '14 at 22:29

Using SQLiteQueryBuilder buildUnionSubQuery() method

1 Answers1

typeDiscriminator{Column|Value}

unionColumns vs columnsPresentInTable

`typeDiscriminator{Column|Value}`

`unionColumns` vs `columnsPresentInTable`