Using the buildUnionSubQuery () method of SQLiteQueryBuilder

Question

Using the buildUnionSubQuery () method of SQLiteQueryBuilder

I have 2 tables that look like this:

Table1

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

and

Table2

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

I can get the correct cursor with the result set I am trying to get by executing a UNION query via the following in my query () method of the content 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);

Instead of manually writing the expression 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 tried using this method but had no luck in generating the SQL statement that I manually wrote.

Example (I've tried about 10 different things) of the code I've tried: 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 this code, this is the output I get when I log the request:

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

Several other examples I've tried end up with the same result when almost all of the columns in the table are "NULL AS"

But my goal is for the query statement (for every SELECT statement in the UNION query) to match my hand-written union declaration.

The order of the columns doesn't matter.

Any help is appreciated.

EDIT:

I know that buildUnionQueries () is used to add multiple SELECT statements, but the problem is that SELECT statements are NOT generated as I expect them (this means that I am using the buildUnionSubquery () method incorrectly, which is what I am looking for help)

EDIT:

The following code works for the most part, except that sorting doesn't 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);

Examples of lines that I am returning:

[_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 lines are local elements 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 lines are cloudy because path = null

Sorting doesn't work and the column names are not sorted (and I don't know how to do this) because I expect a sorted column to be created, but they are not sorted for the entire result set. instead, sorting is done on a per-statement basis.

+3

android android-sqlite

clockwerk 03 dec. 14 at 20:10

source to share

1 answer

zapl · Accepted Answer · 2014-12-03T20:53:50+0000

Seems x AS y

not to be supported by this method. At least not the way you want it.

`typeDiscriminator{Column|Value}`

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

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

Where

typeDiscriminatorColumn

- "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 final projection

. The columns change, though, most importantly, depending on columnsPresentInTable

. But all it does is either keep it intact, or replace it with "NULL AS " + unionColumnEntry

if that column is missing. This allows you to make join queries on tables with the same column names:

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

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 it is because you don't have the same column names. And displaying through AS

is unfortunately not the intent of this method.

What you can do is use a manual #buildQuery when handling NULL

and AS

parts manually

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.

Literature:

source buildUnionSubQuery

Using the buildUnionSubQuery () method of SQLiteQueryBuilder

typeDiscriminator{Column|Value} (adsbygoogle = window.adsbygoogle || []).push({});

unionColumns (adsbygoogle = window.adsbygoogle || []).push({}); vs columnsPresentInTable (adsbygoogle = window.adsbygoogle || []).push({});

More articles:

`typeDiscriminator{Column|Value}`

`unionColumns`

vs `columnsPresentInTable`