SQLiteOpenHelper translating SQLite query in Android

Question

I have below sqlite query:

select product.title, supermarket.title,
       datetime(price.timestamp,'localtime'), price.price
   from price inner join product on price.productid = product._id
   inner join supermarket on price.supermarketid = supermarket._id
where price.productid = 1
group by price.productid, price.supermarketid, price.timestamp
order by price.productid, price.supermarketid, price.timestamp

and I want to build the query with help of SQLiteOpenHelper so I am trying to translate it:

    SQLiteDatabase db = mDbHelper.getReadableDatabase();

    // Define a projection that specifies which columns to read from the database
    String[] projection = {
            DBContract.Product.COLUMN_NAME_TITLE,
            DBContract.Supermarket.COLUMN_NAME_TITLE,
            "datetime(DBContract.Price.COLUMN_NAME_TIMESTAMP, 'localtime')",
            DBContract.Price.COLUMN_NAME_PRICE
    };

    // Define 'where' part of query.
    String selection = DBContract.Price.COLUMN_NAME_PRODUCT_ID + " LIKE ?";

    // Specify arguments in placeholder order.
    String[] selectionArgs = { String.valueOf(productId) };

    // Define grouping by rows
    String groupBy = DBContract.Price.COLUMN_NAME_PRODUCT_ID
            .concat(DBContract.Price.COLUMN_NAME_SUPERMARKET_ID)
            .concat(DBContract.Price.COLUMN_NAME_TIMESTAMP);

    // Define order by clause
    String orderBy = DBContract.Price.COLUMN_NAME_PRODUCT_ID
            .concat(DBContract.Price.COLUMN_NAME_SUPERMARKET_ID)
            .concat(DBContract.Price.COLUMN_NAME_TIMESTAMP);

    Cursor cursor = db.query(
            DBContract.Product.TABLE_NAME,          // The table to query
            projection,                             // The columns to return
            selection,                              // The columns for the WHERE clause
            selectionArgs,                          // The values for the WHERE clause
            groupBy,                                // don't group the rows
            null,                                   // don't filter by row groups
            orderBy                                 // The sort order
    );

My problems are:

1. I do not know if it is correct to specify below in the projection:

   datetime(DBContract.Price.COLUMN_NAME_TIMESTAMP, 'localtime')

2. I do not know how to specify the inner joins using this syntax.

I know I can do it by building directly the query and execute it but I would like to do in this way so how could I get rid of this?

Answer 1

Group by queries require a great deal of overhead on any database so use them sparingly. Group by is used to get sub totals and unique results. Assume that timestamp is unique for productid and supermarketid which it probably is so we don't need the group by clause. SQL like keyword also requires a great deal of overhead on any database so use it sparingly.

select product.title, supermarket.title,timestamp, 
   datetime(price.timestamp,'localtime') lt, price.price
from price inner join product on price.productid = product._id
inner join supermarket on price.supermarketid = supermarket._id
where price.productid = ?
order by price.productid, price.supermarketid, price.timestamp

Notice how I select out a timestamp and a local timestamp. This is because most databases don't let you sort on a column that is not present in the query and we want to present local timestamp to the user. I give local time a name lt so I can actually use it later. I give product a parameter marker. More on this later we need to create a view.

Now change simplified SQL to a view.

create view supermarketproductprice as 
select product.title, supermarket.title,timestamp, 
   datetime(price.timestamp,'localtime') lt, price.price
from price inner join product on price.productid = product._id
inner join supermarket on price.supermarketid = supermarket._id

We need an index over the price table that exactly matches the order of the query to avoid the app not responding msg.

create index price_ix1 on price order by price.productid, price.supermarketid, price.timestamp

Ideally you would create the index and view when you create the database. I have the create view and create index statements as constants

db.execSQL(VERSION_66_CREATE_TABLE);
            db.execSQL(VERSION_66_CREATE_INDEX);
            db.execSQL(VERSION_66_CREATE_VIEW);

Now its a simple mater of selecting from the view.

// Define a projection that specifies which columns to read from the database
String[] projection = {
        DBContract.Product.COLUMN_NAME_TITLE,
        DBContract.Supermarket.COLUMN_NAME_TITLE,
 // extra column for processing sort     price.COULUM_NAME_TIMESTAMP,"datetime(DBContract.Price.COLUMN_NAME_TIMESTAMP, 'localtime')",
        DBContract.Price.COLUMN_NAME_PRICE
};

// Define 'where' part of query.
String selection = DBContract.Price.COLUMN_NAME_PRODUCT_ID + "= ?";

// Specify arguments in placeholder order.
String[] selectionArgs = { String.valueOf(productId) };


// Define order by clause
String orderBy = DBContract.Price.COLUMN_NAME_PRODUCT_ID + ","
        + DBContract.Price.COLUMN_NAME_SUPERMARKET_ID + ","
        + DBContract.Price.COLUMN_NAME_TIMESTAMP;

Cursor cursor = db.query(
        MYVIEWNAME,          // The view to query
        projection,                             // The columns to return
        selection,                              // The columns for the WHERE clause
        selectionArgs,                          // The values for the WHERE clause