How can I retrieve multiple elements by random in a database using sqlstatement with sqlite?-CodePudding

I'm trying to create a quiz app with a database with two tables one for topics and one for the questions. The topicsID is a Foreign key in my questions table. I want to retrieve 2 random questions from each topicID there is 7 topics, so far I have only tried retrieving one topic but Idk how to retrieve the rest in the same function.

fun getAllQuestions(): ArrayList {

    val qList = ArrayList<MathQuestions>()
    val db: SQLiteDatabase = this.readableDatabase
    var sqlStatement = "SELECT * FROM $QuestionTableName WHERE $QTopicColumn_ID = '1' ORDER BY RAND() LIMIT 2"
    val cursor: Cursor = db.rawQuery(sqlStatement, null)

    if (cursor.moveToFirst())
        do {
            val id: Int = cursor.getInt(0)
            val tId: Int = cursor.getInt(1)
            val question: String = cursor.getString(2)
            val option1: String = cursor.getString(3)
            val option2: String = cursor.getString(4)
            val option3: String = cursor.getString(5)
            val optionR: String = cursor.getString(6)


            val p = MathQuestions(id, tId, question, option1, option2, option3, optionR)
            qList.add(p)
        } while (cursor.moveToNext())

    cursor.close()
    db.close()

    return qList
}

CodePudding user response：

One way is a recursive CTE where it will loop through your topics and then can use each topic from the loop to perform your ORDER BY RAND() LIMIT 2 query in the union all.

CTE - Common Table Expressions

CodePudding user response：

You could use UNION's to combine multiple SELECTS.

However, the ORDER BY and LIMIT follow the UNION's (i.e. is for the SELECT overall) as per :-
- In a compound SELECT, all the constituent SELECTs must return the same number of result columns. As the components of a compound SELECT must be simple SELECT statements, they may not contain ORDER BY or LIMIT clauses. ORDER BY and LIMIT clauses may only occur at the end of the entire compound SELECT, and then only if the final element of the compound is not a VALUES clause.
so you would need the ORDER and LIMITs embedded in sub queries which provide values for an IN clause in the SELECT/UNION. The subqueries per SELECT/UNION selecting the rowid columns of the 2 random questions which drives the actual rows selected by the SELECT/UNION thus allowing the ORDER BY AND LIMIT per select.
- rowid is a column, normally hidden,that is unique and exists in ALL tables with the exception of those defined with WITHOUT ROWID tables or Virtual tables.

The following does what I believe you want and additionally ORDERs the entire set by topic :-

val SQLStatment = "SELECT * FROM $QuestionTableName WHERE rowid IN (SELECT rowid FROM $QuestionTableName WHERE $QTopicColumn_ID = '1'  ORDER BY random() LIMIT 2)\n"  
                "UNION SELECT * FROM $QuestionTableName WHERE rowid IN (SELECT rowid FROM $QuestionTableName WHERE $QTopicColumn_ID = '2' ORDER BY random() LIMIT 2)\n"  
                "UNION SELECT * FROM $QuestionTableName WHERE rowid IN (SELECT rowid FROM $QuestionTableName WHERE $QTopicColumn_ID = '3' ORDER BY random() LIMIT 2)\n"  
                "UNION SELECT * FROM $QuestionTableName WHERE rowid IN (SELECT rowid FROM $QuestionTableName WHERE $QTopicColumn_ID = '4' ORDER BY random() LIMIT 2)\n"  
                "UNION SELECT * FROM $QuestionTableName WHERE rowid IN (SELECT rowid FROM $QuestionTableName WHERE $QTopicColumn_ID = '5' ORDER BY random() LIMIT 2)\n"  
                "UNION SELECT * FROM $QuestionTableName WHERE rowid IN (SELECT rowid FROM $QuestionTableName WHERE $QTopicColumn_ID = '6' ORDER BY random() LIMIT 2)\n"  
                "UNION SELECT * FROM $QuestionTableName WHERE rowid IN (SELECT rowid FROM $QuestionTableName WHERE $QTopicColumn_ID = '7' ORDER BY random() LIMIT 2)\n"  
                "ORDER BY $QTopicColumn_ID /* OPTIONAL if you want the rows sorted according to topic */ ;"  
                ";"