0

I am querying an SQL database but for some reason the result items are coming back randomly. Here's my code:

for items in searchFriendEmailArrayNew {

        let query = table.query(with: NSPredicate(format: "email == '\(items)'"))

        query.selectFields = ["isriding"]

        query.read { (result, error) in
            if let err = error {

                print("ERROR ", err)

            } else if let items = result?.items {

                for item in items {

                    let theItem = item["isriding"] as! Bool
                    let newItem = String(theItem)

                    self.searchFriendIsRidingArray.add(newItem)

                    loopCount = loopCount+1

                    if loopCount == self.friendsArray.count {

                        self.tableView.reloadData()
                        self.activityIndicator.isHidden = true
                        self.activityIndicator.stopAnimating()
                    }
                }

            }

        }

    }

The searchFriendEmailArrayNew is an array of email addresses so that when I query the database table it uses the email to look up that user. The array is always consistent and in the same order:

  • user1@email.com
  • user2@email.com
  • user3@email.com
  • user4@email.com

And the query is always done in that order.

I then query the selected field of the user, in this case I am querying the 'isriding' field. This field is a bool returning true or false.

However, when I get to 'for item in items' the results come back in a random order. For example let's say user1 'is riding = true' but all the other users false, the items returned can look like this:

  • isriding false
  • isriding false
  • isriding true
  • isriding false

If I then run the code again it might look like this:

  • isriding true
  • isriding false
  • isriding false
  • isriding false

Can anyone advise as to why they might be coming back in a random order even though when the table is queried it is always queried in a specific order.

Thanks for any help.

Chris
  • 247
  • 1
  • 4
  • 17
  • Looking into this, but as a side note why do you use the loop count to determine if you're at the end of the list, can't you just put that code outside the loop? – Mike Sep 09 '16 at 13:45
  • I'm not sure what you're using (I haven't done SQL in swift) but could it be that you need to add an order param to your query, otherwise the query might return results in some other order, like the order those objects were inserted, for example? – Mike Sep 09 '16 at 13:48
  • You need to use an `ORDER BY` in SQL if you want a specific order. Rows in a relational database are ***NOT*** "sorted" in any way. But as you question contains no SQL code whatsoever, it's hard to tell where and what you need to change. –  Sep 09 '16 at 14:06
  • Thanks a_horse_with_no_name. There is no SQL code because I'm querying an SQL DB on Azure so essentially all I need to do is query the table to return the results. – Chris Sep 09 '16 at 14:10

1 Answers1

1

You likely need to specify an order when you build the query, otherwise the order of the results is likely not going to be in the same order that you you provided.

The order of a query can be forced by using an 'Order by' Clause in the statement. A SQL Database does not actually understand what order you put things in, or store the data in a given order. This means that you need to tell SQL what order you want the items in.

Why do results from a SQL query not come back in the order I expect?

Community
  • 1
  • 1
Mike
  • 9,765
  • 5
  • 34
  • 59
  • Thanks for the replies Mike. It's a good point and something I had thought about, however as it was only a true and false return I needed the results returned in the order they are in the table because otherwise I can only sort ascending or descending. Having said that there might be a way to use ordering if I change some other aspects of my code. Much appreciated. – Chris Sep 09 '16 at 14:08
  • np - I believe this can be done by using something akin to the SQL `FIELD()` function. See http://stackoverflow.com/a/396771/3132984 as that may be relevant. I believe that lets you order by the input, but I'm absolutely an SQL noob. – Mike Sep 09 '16 at 14:15