Question 127835 in Android Studio Question posted in
The official documentation can be found here.

Android Studio – How can I retrieve multiple elements by random in a database using sqlstatement with sqlite?

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
}
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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 */ ;" +
                ";"

    Additional

    The following is an adaptation of the above that will work for any number of topics (obviously 0 topics will result in 0 rows/questions). Adding and removing topics doesn’t require the code to be changed :-

    @SuppressLint("Range")
fun getAllQuestions(orderBy: String?): ArrayList<MathQuestions> {

    val qList = ArrayList<MathQuestions>()
    val db: SQLiteDatabase = this.readableDatabase
    var sb: StringBuilder = StringBuilder() // Going to use a StringBuilder to build the SELECT/UNION statements
    /* extract unique topic ID's i.e. the number of topics */
    var csr = db.rawQuery("SELECT DISTINCT $T_IDColumn FROM $TopicTableName",null)
    var afterFirst = false // first topic will not have UNION keyword
    var currentTopicId: Int // variable for the current topic
    // Loop through extracted topic ID's (no need to moveToFirst Do While as moveToNext will return false if move not possible)
    while (csr.moveToNext()) {
        // If not the first row then add the UNION keyword
        if (afterFirst) {
            sb.append("UNION ")
        }
        afterFirst = true // from now on not the first row
        currentTopicId = csr.getInt(csr.getColumnIndex(QTopicColumn_ID)) // get the topic id
        // Add the appropriate SELECT for the current topic
        sb.append("SELECT * FROM $QuestionTableName WHERE rowid IN (SELECT rowid FROM $QuestionTableName WHERE $QTopicColumn_ID = '$currentTopicId'  ORDER BY random() LIMIT 2) ")
    }
    /* If provided add the ORDER BY clause with the specified order column */
    if (orderBy != null && orderBy.isNotEmpty()) {
        sb.append(" ORDER BY $orderBy" )
    }
    sb.append(";") /* add end of statement (not required) */
    /* execute the built query */
    csr = db.rawQuery(sb.toString(),null)
    while (csr.moveToNext()) {
        /* add to the results directly to the arraylist */
        qList.add(
            MathQuestions(
                csr.getInt(csr.getColumnIndex(QIDColumn)), /* rather than hard code column index, get them according to the extracted column names */
                csr.getInt(csr.getColumnIndex(QTopicColumn_ID)),
                csr.getString(csr.getColumnIndex(QQuestionColumn)),
                csr.getString(csr.getColumnIndex(QOption1Column)),
                csr.getString(csr.getColumnIndex(QOption2Column)),
                csr.getString(csr.getColumnIndex(QOption3Column)),
                csr.getString(csr.getColumnIndex(QOptionRightColumn))
            )
        )
    }
    csr.close() // Close the cursor. No need to close the database (inefficient to open and close the database)
    return qList
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search