Bdtjtk

My database consist of 3 tables.

1 Levels (1 Level, 2 Level, 3 Level)

2 Test groups (5 Tests for Level 1, 10 Tests for Level 2...)

3 Test questions

There is problem when open tests from Level 2 and so on. Inside should be from related Level, but instead there will be tests from Level 1, then Level 2 and so on.

For example below I hardcoded: String selection = "quiz_list_id" + " = ? " + " AND " + "LevelID" + " = 2";.
So when I open Level 2 I see right number of Tests, but first 5 will be empty and from 6 to 10 I have tests for Level 2 from 1 to 4.

I understand that the problem is somehow related to that my Level 2 first Test has id 6 and first 5 are taken by Tests for Level 1.
How can I get those tests to start from beginning? Why previous Level tests are counted and therefore shift next Levels' Tests?

 DBHelper.java



  ...



public ArrayList<Question> getNLevelQuestions(int quizListID) {

    ArrayList<Question> questionList = new ArrayList<>();

    db = getReadableDatabase();



    String table = QuestionsTable.TABLE_NAME + " JOIN " + QuizListTable.TABLE_NAME +

            " ON " + QuizListTable.QUIZ_ID + " = " + QuestionsTable.COLUMN_QUIZ_LIST_ID;



        //String selection = QuestionsTable.COLUMN_QUIZ_LIST_ID + " = ? ";

        //Below hardcoded version of string selection

    String selection = "quiz_list_id" + " = ? " + " AND " + "LevelID" + " = 2";



    String selectionArgs = new String{String.valueOf(quizListID)};



    Cursor c = db.query(table,

            null,

            selection,

            selectionArgs,

            null,

            null,

            null

    );



    if (c.moveToFirst()) {

        do {

            Question question = new Question();

            question.setId(c.getInt(c.getColumnIndex(QuestionsTable._ID)));

            question.setQuestion(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_QUESTION)));

            question.setOption1(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION1)));

            question.setOption2(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION2)));

            question.setOption3(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION3)));

            question.setOption4(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION4)));

            question.setAnswerNB(c.getInt(c.getColumnIndex(QuestionsTable.COLUMN_ANSWER_NB)));

            question.setListTest(c.getInt(c.getColumnIndex(QuestionsTable.COLUMN_QUIZ_LIST_ID)));

            questionList.add(question);

        } while (c.moveToNext());

    }



    c.close();

    return questionList;

}

}

QuizContractor.java

public final class QuizContract {



private QuizContract() {

}

public static class JLPTLevelsTable implements BaseColumns{

    public static final String TABLE_NAME = "test_level";

    public static final String COLUMN_NAME = "LevelName";

    public static final String COLUMN_JLPTLevel_ID = "LevelID";



}



public static class QuizListTable implements BaseColumns{

    public static final String TABLE_NAME = "quiz_list";

    public static final String COLUMN_NAME = "name";

    public static final String COLUMN_JLPTLevel_ID = "LevelID";

    public static final String QUIZ_ID ="quiz_id";

}



public static class QuestionsTable implements BaseColumns {

    public static final String TABLE_NAME = "quiz_questions";

    public static final String COLUMN_QUESTION = "question";

    public static final String COLUMN_OPTION1 = "option1";

    public static final String COLUMN_OPTION2 = "option2";

    public static final String COLUMN_OPTION3 = "option3";

    public static final String COLUMN_OPTION4 = "option4";

    public static final String COLUMN_ANSWER_NB = "answer_nb";

    public static final String COLUMN_QUIZ_LIST_ID = "quiz_list_id";

}

}

I

How can I get those tests to start from beginning? Why previous Level tests are counted and therefore shift next Levels' Tests?

Effectively there is no beginning. You are basically misusing/misconceiving the concept of id's and how they should be used.

An id should be considered only as a means of uniquely identifiying a row. Considering it to be a specific value, such as 1,2,3... will only cause issues.

Assuming that what you want is to display the extracted questions along the lines of :-

1. What bird laid the Golden egg?
   
   
   
   
   1. Cuckoo
   
   
   2. Chicken
   
   
   3. Goose
   
   
   4. Sparrow
   
   
   
   


2. Who killed the Sparrow?
   
   
   
   
   1. The robin.
   
   
   2. Another sparrow.
   
   
   3. The finch.
   
   
   4. The eagle.
   
   
   
   

and you are getting :-

5. What bird laid the Golden egg?
   
   
   
   
   1. Cuckoo
   
   
   2. Chicken
   
   
   3. Goose
   
   
   4. Sparrow
   
   
   
   


6. Who killed the Sparrow?
   
   
   
   
   1. The robin.
   
   
   2. Another sparrow.
   
   
   3. The finch.
   
   
   4. The eagle.
   
   
   
   

And as such want 5 to be 1 and 6 to be 2 then:-

Instead of using the id you could use a sequence number set elsewhere.

As an example you could add another variable/member to the Question object say int sequence along with a setter such as setSequence(int sequence) and a getter such as int getSqeuence() and then use :-

if (c.moveToFirst()) {

    do {

        Question question = new Question();

        question.setSequence(csr.getPosition() + 1); //<<<<<<<<<< ADDED

        question.setId(c.getInt(c.getColumnIndex(QuestionsTable._ID)));

        question.setQuestion(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_QUESTION)));

        question.setOption1(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION1)));

        question.setOption2(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION2)));

        question.setOption3(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION3)));

        question.setOption4(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION4)));

        question.setAnswerNB(c.getInt(c.getColumnIndex(QuestionsTable.COLUMN_ANSWER_NB)));

        question.setListTest(c.getInt(c.getColumnIndex(QuestionsTable.COLUMN_QUIZ_LIST_ID)));

        questionList.add(question);

    } while (c.moveToNext());

}

And then instead of using the getter getId() use getSequence().