ios - SQLite.Swift 如何获得从不重复的随机行

Question

我正在 iOS 中试验 SQlite.Swift，我试图从 SQLite 数据库的表中随机选择一行。

表格是movies，它的列是movieID、title genre 和seen.

问题:电影应该是随机选择的，但是一旦我将 seen 设置为“yes”，它们就不会再被选择。

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()

现在我想使用从表中捕获的这个随机行号来将随机电​​影的title和genre导入let(“dispMovie”和“movieGenre”)输出到 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)"

这段代码工作正常。唯一的问题是我看过的电影仍然会被播放，因为我在随机行函数中有这个.count。

我也试过用这个调用 Raw SQLite:

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

outputTextField.text = newMovie

但这只显示标题而不显示类型，并且不可能在额外的行中查找类型，因为电影是从随机行中挑选的。我尝试在原始代码中引用电影标题，但这会使应用程序崩溃。

感谢您的帮助和提示。

Answer 1

在这完成之前没有圣诞节!

经过数天的思考和反复思考，我突然想到了古老的 while 循环。结果证明有效，所以我将其作为我自己问题的答案:

忘记 randomRow()。不需要这个。我首先关注 SQLite RAW，然后继续使用 SQLite.Swift 中的 scalar、where 和 select 的组合 库。这终于让我明白了:

发布我的整个 UIViewController 以便您可以测试自己:

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()

        } 
    }

由于我仍然认为自己是初学者(17 年 10 月开始编码)，请尽可能优化它。我的所有测试都进行得非常顺利，并且当转到“已看过”时，没有电影会重复出现。