Android SQLite grop_concat multiple column

Question

i am trying to run the following query in android. when i try to retrieve the value of the of column 'd' it return null. i can get a.* values though. :(

query:

select a.*,group_concat(b._id || ', ' || b.product_id || ', ' || b.no_of_units || ', ' || b.unit_price || ', ' || b.discount || ', ' || b.discount, '; ') as d
    from transactions a  join transactions b on a._id=b.local_ref_txn_id 
group by a.id

code:

mCursor = new MyDbHelper(mContext).getReadableDatabase().rawQuery("select group_concat(b._id || ', ' || b.product_id || ', ' || b.no_of_units || ', ' || b.unit_price || ', ' || b.discount || ', ' || b.discount, '; ') as d,a.* \n" +
                "from transactions a left join transactions b on a._id=b.local_ref_txn_id " +
                "group by a._id", null); 

String details  = cursor.getString(cursor.getColumnIndex("d"));

MikeT · Answer 1 · 2018-07-31T06:06:31.717

Your code above, will not work/is not working as expected, due to :-

First you are trying to access a Cursor with a name of cursor, when the Cursor's name that you have generated is mCursor.

rather you would appear to be accessing a previously created/extracted Cursor, which may well have the wrong data for a column d assuming that column d exists in that Cursor.

As such the code should be (ONLY TO CORRECT THE ABOVE ISSUE) :-

String details  = mCursor.getString(mCursor.getColumnIndex("d"));

The above would however then fail with an

android.database.CursorIndexOutOfBoundsException: Index -1 requested, with a size of 0

This is because there is no attempt to move to a row within the Cursor so the Cursor is at the "Before First Row" position (i.e position -1).

To correct the above you need to move to a valid row, if there is one. Assuming that you will extract an arbitrary number of rows then you could process each row using something like :-

private void SO51603383() {
    Cursor mCursor;
    SO51603383DBHelper MyDbHelper = new SO51603383DBHelper(this);
    mCursor = MyDbHelper.getReadableDatabase().rawQuery("select group_concat(b._id || ', ' || b.product_id || ', ' || b.no_of_units || ', ' || b.unit_price || ', ' || b.discount || ', ' || b.discount, '; ') as d,a.* \n" +
            "from transactions a left join transactions b on a._id=b.local_ref_txn_id " +
            "group by a._id", null);

    while (mCursor.moveToNext()) {
        String details = mCursor.getString(mCursor.getColumnIndex("d"));
    }
}

When/if you run then as you are using LEFT JOIN you may get null values, as per :-

If the join-operator is a "LEFT JOIN" or "LEFT OUTER JOIN", then after the ON or USING filtering clauses have been applied, an extra row is added to the output for each row in the original left-hand input dataset that corresponds to no rows at all in the composite dataset (if any). The added rows contain NULL values in the columns that would normally contain values copied from the right-hand input dataset.

SQL As Understood By SQLite - SELECT

So you may want to use JOIN instead.

Example Case

Using The following as the Database Helper :-

public class SO51603383DBHelper extends SQLiteOpenHelper {

    public static final String DBNAME = "SO51603383.db";
    public static final int DBVERSION = 1;

    public static final String TB_TRANSACTIONS = "transactions";
    public static final String COL_TRANSACTIONS_ID = BaseColumns._ID;
    public static final String COl_TRANSACTIONS_NAME = "name";
    public static final String COL_TRANSACTIONS_PRODUCTID = "product_id";
    public static final String COL_TRANSACTIONS_NO_OF_UNITS = "no_of_units";
    public static final String COL_TRANSACTIONS_UNIT_PRICE = "unit_price";
    public static final String COL_TRANSACTIONS_DISCOUNT = "discount";
    public static final String COl_TRANSACTIONS_LOCAL_REF_TXN = "local_ref_txn_id";

    SQLiteDatabase mDB;


    public SO51603383DBHelper(Context context) {
        super(context, DBNAME, null, DBVERSION);
        mDB = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String crtsql = "CREATE TABLE IF NOT EXISTS " + TB_TRANSACTIONS + "(" +
                COL_TRANSACTIONS_ID + " INTEGER PRIMARY KEY," +
                COl_TRANSACTIONS_NAME + " TEXT," +
                COL_TRANSACTIONS_PRODUCTID + " INTEGER, " +
                COL_TRANSACTIONS_NO_OF_UNITS + " INTEGER, " +
                COL_TRANSACTIONS_UNIT_PRICE + " INTEGER, " +
                COL_TRANSACTIONS_DISCOUNT + " INTEGER, " +
                COl_TRANSACTIONS_LOCAL_REF_TXN + " INTEGER" +
                ")";
        db.execSQL(crtsql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }

    public long add(String name, long productid, int units, int price, int discount, long localref) {
        ContentValues cv = new ContentValues();
        cv.put(COl_TRANSACTIONS_NAME,name);
        cv.put(COL_TRANSACTIONS_PRODUCTID,productid);
        cv.put(COL_TRANSACTIONS_NO_OF_UNITS,units);
        cv.put(COL_TRANSACTIONS_UNIT_PRICE,price);
        cv.put(COL_TRANSACTIONS_DISCOUNT,discount);
        cv.put(COl_TRANSACTIONS_LOCAL_REF_TXN,localref);
        return mDB.insert(TB_TRANSACTIONS,null,cv);
    }
}

Loading 2 rows using (see add method above) :-

    MyDbHelper.add("Test001",1,10,22,3,0);
    MyDbHelper.add("Test002",1,20,24,5,1);

And then running :-

    mCursor = MyDbHelper.getReadableDatabase().rawQuery(
            "select group_concat(b._id || ', ' || b.product_id || ', ' || b.no_of_units || ', ' || b.unit_price || ', ' || b.discount || ', ' || b.discount, '; ') " +
                    "as d,a.* \n" +
            "from transactions a left join transactions b on a._id=b.local_ref_txn_id " +
            "group by a._id", null);

    while (mCursor.moveToNext()) {
        String details = mCursor.getString(mCursor.getColumnIndex("d"));
        Log.d("DETAILS","Column D from query is :- " + details);
    }

results in the following output to the log (1 row with data, 1 row with null) :-

07-31 02:26:21.255 1580-1580/soanswers.soanswers D/DETAILS: Column D from query is :- 2, 1, 20, 24, 5, 5
    Column D from query is :- null

Changing to use :-

    mCursor = MyDbHelper.getReadableDatabase().rawQuery(
            "select group_concat(b._id || ', ' || b.product_id || ', ' || b.no_of_units || ', ' || b.unit_price || ', ' || b.discount || ', ' || b.discount, '; ') " +
                    "as d,a.* \n" +
                    //"from transactions a left join transactions b on a._id=b.local_ref_txn_id " +
                    "from transactions a join transactions b on a._id=b.local_ref_txn_id " +
            "group by a._id", null);

    while (mCursor.moveToNext()) {
        String details = mCursor.getString(mCursor.getColumnIndex("d"));
        Log.d("DETAILS","Column D from query is :- " + details);
    }

results in (after clearing the DB):-

07-31 05:56:38.872 2380-2380/soanswers.soanswers D/DETAILS: Column D from query is :- 2, 1, 20, 24, 5, 5

Using the first code (LEFT JOIN) and running a second time, thus adding another two rows (duplicates except the _id value) then the result is :-

07-31 05:59:04.095 2440-2440/? D/DETAILS: Column D from query is :- 2, 1, 20, 24, 5, 5; 4, 1, 20, 24, 5, 5
    Column D from query is :- null
    Column D from query is :- null
    Column D from query is :- null

Using the second (just JOIN) code and running twice then the result is :-

07-31 06:05:00.610 2565-2565/soanswers.soanswers D/DETAILS: Column D from query is :- 2, 1, 20, 24, 5, 5; 4, 1, 20, 24, 5, 5

thank you for the reply. i forgot to mention that the retrieval is done in a cursor adapter hence the cursor name and the value is coming in SQLite2009 Pro. — Mashfiqur Rahman, Jul 31 '18 at 04:44
@MashfiqurRahmanAninda then the last section re LEFT JOIN is the issue. — MikeT, Jul 31 '18 at 05:29
i think so, but i know the value exists. as its coming from mysql db sync with the sqlite db. — Mashfiqur Rahman, Jul 31 '18 at 05:34
**The value** may be the issue as there's a good chance that many values and thus rows could be extracted. Have you tried changing LEFT JOIN to JOIN? If not then I'd suggest editing your question to include SQL to create and populate the table(s)., then the actual results could be analysed/explained. — MikeT, Jul 31 '18 at 05:41
may be i have found the issue, once the value is synchronized local_ref_txn_id and _id are somehow mismatching though they are have values. strange though — Mashfiqur Rahman, Jul 31 '18 at 07:18

Android SQLite grop_concat multiple column

1 Answers1

Example Case