How to avoid fetching double data from sqlite in a spinner?

Question

I'm trying to fetch data from sqlite to set them into a spinner. The user creates rows of travel dates and set them into the database (COLUMN_DATE_DEPART). In the first spinner I'd like to get the list of the years existing in the COLUMN_DATE_DEPART. In the second one, I'd like to get the list of months of the year selected in the first spinner.

The problem is as followed: In the first spinner i get the years as expected. In the second spinner i get the months but in double so that if the user created two travels on the 1st and the 31st of January, I'll have the month January twice rather than once.

My question is: What did i miss ?

Find attached my code.

Fragment Activity Java

List<String> an = helper.ListeAnneeTrajet(et_iduser);
                final ArrayAdapter<String> adapterA= new ArrayAdapter<>(this.getActivity(), R.layout.spinner_item, an);
                adapterA.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
                annee.setAdapter(adapterA);
                annee.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {

                    @Override
                    public void onItemSelected(AdapterView<?> parentView, View selectedItemView, int position, long id) {

                        List<String> mo = helper.ListeMoisTrajet(et_iduser, annee.getSelectedItem().toString().trim());
                        ArrayAdapter<String> adapterB = new ArrayAdapter<>(getActivity(), R.layout.spinner_item, mo);
                        adapterA.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
                        mois.setAdapter(adapterB);
                        mois.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {

                            @Override
                            public void onItemSelected(AdapterView<?> parentView, View selectedItemView, int position, long id) {
                                // your code here
                            }
                            @Override
                            public void onNothingSelected(AdapterView<?> parentView) {
                                // your code here
                            }
                        });
                    }
                    @Override
                    public void onNothingSelected(AdapterView<?> parentView) {
                        // your code here
                    }
                });

DatabaseHelper

public List<String> ListeAnneeTrajet(String et_iduser) {
        List<String> an = new ArrayList<>();
        String selectQuery = "SELECT DISTINCT * FROM " + TABLE_NAME3 + " WHERE (" + COLUMN_ID_CHAUFFEUR + " = '" + et_iduser + "' AND " + COLUMN_DATE_DEPART + " >= DATE('NOW')) GROUP BY SUBSTR(" + COLUMN_DATE_DEPART + ",1,4) ORDER BY " + COLUMN_DATE_DEPART + " ASC";
        db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        if (cursor.moveToFirst()) {
            do {
                an.add(cursor.getString(cursor.getColumnIndex(COLUMN_DATE_DEPART)).substring(0,4));
            }
            while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return an;
    }

    public List<String> ListeMoisTrajet(String et_iduser, String et_annee) {
        List<String> mo = new ArrayList<>();
        String selectQuery = "SELECT DISTINCT * FROM " + TABLE_NAME3 + " WHERE (" + COLUMN_ID_CHAUFFEUR + " = '" + et_iduser + "' AND " + COLUMN_DATE_DEPART + " >= DATE('NOW') AND SUBSTR(" + COLUMN_DATE_DEPART + ",1,4) = '" + et_annee + "') GROUP BY SUBSTR(" + COLUMN_DATE_DEPART + ",6,7) ORDER BY " + COLUMN_DATE_DEPART + " ASC";
        db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        if (cursor.moveToFirst()) {
            do {
                mo.add(cursor.getString(cursor.getColumnIndex(COLUMN_DATE_DEPART)).substring(5,7));
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return mo;
    }

Just'in Pointinini · Accepted Answer · 2017-12-15 23:23:06Z

1

@Mike T you deserve a golden statue on the champs Elysées in Paris however, because i can't build that for you so far, receive please my eternal thankfulness. Actually i changed my query as you said into :

public List<String> ListeMoisTrajet(String et_iduser, String et_annee) {
        List<String> mo = new ArrayList<>();
        String selectQuery = "SELECT DISTINCT " + COLUMN_DATE_DEPART + " FROM " + TABLE_NAME3 + " WHERE (" + COLUMN_ID_CHAUFFEUR + " = '" + et_iduser + "' AND " + COLUMN_DATE_DEPART + " >= DATE('NOW') AND SUBSTR(" + COLUMN_DATE_DEPART + ",1,4) = '" + et_annee + "') GROUP BY SUBSTR(" + COLUMN_DATE_DEPART + ",6,2) ORDER BY " + COLUMN_DATE_DEPART + " ASC";

I can't believe i spent weeks ont that sole point... :-)

answered Dec 15, 2017 at 23:23

Just'in Pointinini

357 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MikeT · Accepted Answer · 2017-12-15 01:38:13Z

DISTINCT applies to the entire result set, thus specifying * means that only entire rows that are duplicated for all values are removed according to the DISTINCT. However GROUP BY has a similar effect so that is what is in fact restricting the results (DISTINCT in both uses does nothing as id will always be unique, assuming it's an alias of rowid, unsure? then check here and/or here).

I think you are also mixing up SQLite's SUBSTR function with the Java substring method. The latter uses indexes so 5,7 will return 2 characters. Whilst the SQLite SUBSTR function uses offset and the number of characters so 6,7 will return up to 7 characters.

I believe you could try something like :-

String selectQuery = "SELECT DISTINCT SUBSTR(" + COLUMN_DATE_DEPART + "6,2) AS month FROM  " + TABLE_NAME3 + " WHERE (" + COLUMN_ID_CHAUFFEUR + " = '" + et_iduser + "' AND " + COLUMN_DATE_DEPART + " >= DATE('NOW') AND SUBSTR(" + COLUMN_DATE_DEPART + ",1,4) = '" + et_annee + "') ORDER BY " + COLUMN_DATE_DEPART + " ASC";

Note the above will return a cursor with just 1 column named month so

mo.add(cursor.getString(cursor.getColumnIndex(COLUMN_DATE_DEPART)).substring(5,7));

would be changed to :-

mo.add(cursor.getString(cursor.getColumnIndex("month")));

or

mo.add(cursor.getString(0));

Alternately changing GROUP BY SUBSTR(" + COLUMN_DATE_DEPART + ",6,7) to GROUP BY SUBSTR(" + COLUMN_DATE_DEPART + ",6,2) may also work (i.e. why the YEARS works but the MONTHS doesn't).

Note the above hasn't been checked so may have the odd error. It's not clear what the actual substr's are but the assumption is that the format of the COLUMN_DATE_DEPART column is YYYY/MM/DD.

Thanks for your reply. I'll try to fix my problem according to what you suggested and I'll be back very soon @Mike T

Collectives™ on Stack Overflow

How to avoid fetching double data from sqlite in a spinner?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related