Ndtyjky

Database consist of levels and each level consist of several tests.
Level 1 consist of 3 tests
Level 2 has 10 tests. And so on...
When I open level 2 it shows me 10 test as it should be, but the problem is that first 3 tests are actually from level 1, and only from test 4 my level 2 starts. Where I forgot to filter, or do it not right way?

public class QuizDbHelper extends SQLiteOpenHelper {





.......skip here....



@Override

public void onConfigure(SQLiteDatabase db) {

    super.onConfigure(db);

    db.setForeignKeyConstraintsEnabled(true);

}



private void addJLPTLevel(JLPTLevel jlptLevel) {

    ContentValues cv = new ContentValues();

    cv.put(JLPTLevelsTable.COLUMN_NAME, jlptLevel.getName());

    cv.put(JLPTLevelsTable.COLUMN_JLPTLevel_ID, jlptLevel.getLevelID());

    db.insert(JLPTLevelsTable.TABLE_NAME, null, cv);

}



private void addQuizList(ListQuiz listQuiz) {

    ContentValues cv = new ContentValues();

    cv.put(QuizListTable.COLUMN_NAME, listQuiz.getName());

    cv.put(QuizListTable.COLUMN_JLPTLevel_ID, listQuiz.getLevelID());

    db.insert(QuizListTable.TABLE_NAME, null, cv);

}



private void addQuestion(Question question) {

    ContentValues cv = new ContentValues();

    cv.put(QuestionsTable.COLUMN_QUESTION, question.getQuestion());

    cv.put(QuestionsTable.COLUMN_OPTION1, question.getOption1());

    cv.put(QuestionsTable.COLUMN_OPTION2, question.getOption2());

    cv.put(QuestionsTable.COLUMN_OPTION3, question.getOption3());

    cv.put(QuestionsTable.COLUMN_OPTION4, question.getOption4());

    cv.put(QuestionsTable.COLUMN_ANSWER_NB, question.getAnswerNB());

    cv.put(QuestionsTable.COLUMN_QUIZ_LIST_ID, question.getListTest());

    db.insert(QuestionsTable.TABLE_NAME, null, cv);

}





public ArrayList<ListQuiz> getNListQuiz(int NListID) {

    ArrayList<ListQuiz> nQuizList = new ArrayList<>();

    db = getReadableDatabase();



    String selection = QuizListTable.COLUMN_JLPTLevel_ID + " = ? ";



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



    Cursor c = db.query(

            QuizListTable.TABLE_NAME,

            null,

            selection,

            selectionArgs,

            null,

            null,

            null

    );



    if (c.moveToFirst()) {

        do {

            ListQuiz nQuizLevel = new ListQuiz();

            nQuizLevel.setId(c.getInt(c.getColumnIndex(QuizListTable._ID)));

            nQuizLevel.setName(c.getString(c.getColumnIndex(QuizListTable.COLUMN_NAME)));

            nQuizLevel.setLevelID(c.getInt(c.getColumnIndex(QuizListTable.COLUMN_JLPTLevel_ID)));

            nQuizList.add(nQuizLevel);

        } while (c.moveToNext());

    }

    c.close();

    return nQuizList;

}





public ArrayList<Question> getNLevelQuestions(int quizListID) {

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

    db = getReadableDatabase();



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



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



    Cursor c = db.query(

            QuestionsTable.TABLE_NAME,

            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;

}

}

StartScreenActivity code

@Override

protected void onCreate(Bundle savedInstanceState) {

    super.onCreate(savedInstanceState);

    setContentView(R.layout.activity_starting_screen);



    JLPTList1 = new ArrayList<>();



    Intent intent = getIntent();

    int JLPTListID1 =  intent.getIntExtra(StartingScreenActivity1.EXTRA_LEVEL_ID, 0);

    String quizListName =  intent.getStringExtra(StartingScreenActivity1.EXTRA_LEVEL_NAME);







    QuizDbHelper dbHelper = QuizDbHelper.getInstance(this);

    JLPTList1 = dbHelper.getJLPTLevels(JLPTListID1);

    categorySize = dbHelper.getAllListQuiz().size();

    categoryName = dbHelper.getAllListQuiz();



private void startQuiz(int QuizListID, String quizListName) {

    Intent intent = new Intent(StartingScreenActivity.this, MainActivity.class);

    intent.putExtra(EXTRA_DIFFICULTY_ID, QuizListID);

    intent.putExtra(EXTRA_DIFFICULTY_NAME, quizListName);

    startActivityForResult(intent, REQUEST_CODE_QUIZ);

}

At a guess you are expecting the definition of a foreign key to automatically extract data according to the defined foreign keys. That will not be the case. All defining a foreign key does is add a constraint (rule) that must be followed saying that to insert a row into the table (child) that references another table MUST (if foreignKeyConstraintsEnabled is true). As such you have to JOIN tables ON the reference to get data from both tables.

Assuming that you have two tables

quizlist which has;

• a column for the id of the individual quizlist (_id in the picture),


• a column for the name, and


• a column for the level (not sure why an id in your picture, it just appears that a number suffices as the picture shows rows have the same level)

and question which has;

• a column for the id of the individual question


• 
  

  a column that references the quizlist (and therefore level).

  
  
  
  
  
  • (minimal columns included for brevity/demonstration)
  
  
  
  

Then using the following to populate the tables :-

DROP TABLE IF EXISTS question;

DROP TABLE If EXISTS quizlist;



CREATE TABLE IF NOT EXISTS quizlist (ID INTEGER PRIMARY KEY, level INTEGER, name TEXT);

CREATE TABLE IF NOT EXISTS question (ID INTEGER PRIMARY KEY, question TEXT, quizlist_reference REFERENCES quizlist(id));



INSERT INTO quizlist (level,name) VALUES (1,'P 1'), (1,'P 2'),(1,'P 3'),(2,'P 4'),(2,'P 5'),(3,'P 6'),(3,'P 7');

INSERT INTO question (question, quizlist_reference) VALUES

    ('Q1',1),('Q2',6),('Q3',2),('Q4',4),('Q5',3),('Q6',1),('Q7',1),('Q8',1),('Q9',3),('Q10',3),('Q11',3),

    ('Q12',2),('Q13',4),('Q14',5),('Q15',3),('Q16',4),('Q17',6);

Will result in two tables :-

quizlist :-

question :-

As can be seen no the level and the name of the level are not included data is gathered. Instead if you ran a SELECT query using (only user friendly data is the question, it's level name and it's level):-

SELECT question, name, level 

    FROM question 

        JOIN quizlist ON quizlist.id = quizlist_reference 

    ORDER BY question ASC;

The result would be :-

• Note sorting (ORDER BY) by question isn't at all helpful in this case.


• As can be seen for each question the respective level and the level name is now obtained.

It's now a simple step to select all questions for a level, all that is needed is a WHERE clause e.g. WHERE level = 1 to be included.

So using :-

SELECT question  

FROM question 

    JOIN quizlist ON quizlist.id = quizlist_reference 

WHERE level = 1 

ORDER BY question ASC;

Will result in :-

Similarly :-

SELECT question  

FROM question 

    JOIN quizlist ON quizlist.id = quizlist_reference 

WHERE level = 2 

ORDER BY question ASC;

Will result in :-

It's impossible to provide a more specific answer without guessing, as there is no indication in the questions as to the foreign keys, column definitions etc. As such you will need to apply the principles shown appropriately.

Applying the Query to Android

Note that to use the SQLiteDatabse query method the table along with the JOIN are supplied via the first parameter as a String.

Additionally if you have like named columns (e.g. in the example above both tables have an id column) a Cursor does not include the table name, therefore the Cursor will have columns with the same name and the getColumnIndex method will not necessarily get the right data (I believe it will get the last such column). As such it is advisable to give the columns specific names with an AS clause (this as part of the second parameter).

As such using adapting your code to suit the above tables a method such as the following could get questions at a particular level :-

public ArrayList<Question> getQuestionsByLevel(int level) {

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

    db = getReadableDatabase();



    String table = QuestionsTable.TABLE_NAME + 

         " JOIN " + QuizListTable.TABLE_NAME + 

         " ON " + QuizListTable.COLUMN_ID  + "=" + QuestionsTable.COLUMN_QUESTIONS_QUIZ_LIST_REFERENCE;

    String columns = new String{

            QuestionsTable.COLUMN_ID + 

            " AS " + QuestionsTable.TABLE_NAME + "_" + QuestionsTable.COLUMN_ID, //<<<<<<<< make returned ID column (from questions table) unique name

            QuestionsTable.COLUMN_QUESTION,

            QuizListTable.COLUMN_ID + 

            " AS " + QuizListTable.TABLE_NAME + "_" + QuizListTable.COLUMN_ID, //<<<<<<<<<< make returned ID column (from quizlist table) unique name

            QuizListTable.COLUMN_LEVEL,

            QuizListTable.COLUMN_NAME

        };

    String selection = QuizListTable.COLUMN_QUIZ_LIST_LEVEL + "= ?";

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



    Cursor c = db.query(

            QuestionsTable.TABLE_NAME,

            columns,

            selection,

            selectionArgs,

            null,

            null,

            null

    );

    ...............

• 
  Note the above is intended only as an in-principle example. It has not been tested, so may contain some errors. Additionally :-
  
  
  
  
  • Constants are along the lines of how they may be coded based upon what appear to be conventions (wasn't going to spend time trying to decipher the limited code provided)
  
  
  • Additional columns have been added for demonstration, the above would equate to SELECT question.id AS question_id,question,quizlist.id AS quizlist_id, level, name FROM question JOIN quizlist ON quizlist.id = quizlist_reference WHERE level = 2