SQLite.Swift how to get random row never repeating

Question

I'm experimenting with SQlite.Swift in iOS and I'm trying to randomly pick a row from a table in an SQLite database.

The table is movies and its columns are movieID, title genre and seen.

Problem: Movies should be picked randomly but as soon as I set seen to "yes" they should no longer be picked.

func randomRow() -> Int {

       let path = NSSearchPathForDirectoriesInDomains(
                 .documentDirectory,
                 .userDomainMask, true)
                 .first!

       let database = try! Connection("\(path)/movies.sqlite3")

       var randomRow = Int(arc4random_uniform(UInt32(try!
                       database.scalar(movies.where(seen != "yes")
                       .select(movieID.distinct.count)))))

       while previousNumber == randomRow {
             randomRow = Int(arc4random_uniform(UInt32(try!
             database.scalar(movies.where(seen != "yes")
             .select(movieID.distinct.count)))))
           }
             previousNumber = randomRow
             return randomRow
   }

      let destRow = randomRow()

Now I want to use this random row number to be catched from the table to lead the title and genre of the random movie into let ("dispMovie" and "movieGenre") to be output into a UITextField.

          let path = NSSearchPathForDirectoriesInDomains(
                     .documentDirectory,
                     .userDomainMask, true)
                     .first!

          let database = try! Connection("\(path)/filme.sqlite3")

          for movie in try! database.prepare(
                            self.movies.where(
                            seen != "yes" && movieID == "\(destRow)")) {

dispMovie = movie[title]
movieGenre = movie[genre]
                }

    outputTextField.text = "\(dispMovie), \(movieGenre)"

This code works fine. The only problem is that movies I have seen will still be diplayed because I have this .count in the random row function.

I've also tried to call Raw SQLite with this one:

let newMovie = try! database.scalar(
                    "SELECT title, genre FROM movies WHERE seen != yes
                    ORDER BY RANDOM() LIMIT 1") as! String

outputTextField.text = newMovie

But this only displays the title and not the genre and looking for the genre in an extra line is not possible because the movie is picked from a random row. I've tried to reference the movie title in the Raw code but that crashes the app.

Thanks for help and hints.

Answer 1

No Christmas 'till this is done!

After days of thinking and running against walls suddenly the good old while loop came to my mind. It turned out working so I'm leaving this as an answer to my own question:

Forget about the randomRow(). No need for this. I focused on the SQLite RAW in the first place and continued with a combination of scalar, where and select from the SQLite.Swift library. That finally got me this:

Posting my whole UIViewController so you can test yourself:

class TestView: UIViewController {

//Table
    let movies = Table("movies")

//Expressions
    let movieId = Expression<String>("ID")
    let movieTitle = Expression<String>("movieTitle")
    let genre = Expression<String>("genre")
    let seen = Expression<String>("seen")

//to find with database connection
    var randomMovie = String()
    var movieDetails = [String]()

//Array of all movies seen
    var seenMovies = [String]()

//TestLabel to check everything
    @IBOutlet weak var testLabel: UILabel!

    @IBAction func testButton(_ sender: Any) {

//Open database connection
        let path = NSSearchPathForDirectoriesInDomains(
            .documentDirectory, .userDomainMask, true
            ).first!

        let database = try! Connection("\(path)/movies.sqlite3")

//Check column "seen" and add all movie Titles to Array seenMovies        
        for movie in try! database.prepare(self.movies.where(seen == "yes")) {
            self.seenMovies.append(movie[movieTitle])
        }

//Select random row from movies Table with SQLite RAW as String
        self.randomMovie = "\(try! database.scalar("SELECT movieTitle FROM movies WHERE seen != yes ORDER BY RANDOM() LIMIT 1"))"

//Create Array of details for that randomly picked movie
//Use scalar, where and select to find details from the picked movie above
        self.movieDetails = ["\(randomMovie!)",
            "\(try! database.scalar(movies.where(movieTitle == "\(randomMovie!)").select(genre)))"]

//And here's the "unique" thing...
//while randomMovie is also an element in seenMovies, loop until it's not
        while seenMovies.contains("\(randomMovie!)") {
            randomMovie = "\(try! database.scalar("SELECT movieTitle FROM movies WHERE seen != yes ORDER BY RANDOM() LIMIT 1"))"
        }
//let testLabel show the results and see that no seen movie will be picked        
        testLabel.text = "\(details)\n\n\(erraten)"

//To prevent the Array from being appended over and over:
        self.seenMovies.removeAll()

        } 
    }

Since I still consider myself a beginner (started coding in October '17) please feel free to optimize this if possible. All my tests went very well and no movie was ever repeated when turned to "seen".

Answer 2

Found a solution from SQLite.Swift issue filed in 2016.

let db = Connection(databasePath)
let movies = Table("movies")
let query = movies.order(Expressions<Int>.random()).limit(1)

for row in try! db.prepare(query) {
  // handle query result here
}