SQL and Parse Query Compared

Here is the object we will work with.

COMMENT
objectId	member	text	createdAt	updatedAt	ACL
VZ9cibW0Oe	rAtRskgtyp	Nicely done!	2015-08-07T04:46:43.738Z	2015-08-07T04:46:43.738Z	Public Read, rAtRskgtyp
ay9bvW2b1U	uYZKeAGinV	Good job.	2015-08-08T14:15:59.571Z	2015-08-08T14:15:59.571Z	Public Read, uYZKeAGinV
ogm7EIAiY9	vRtVykXTFn	Needs more work.	2015-08-10T15:05:53.314Z	2015-08-10T15:05:53.314Z	Public Read, vRtVykXTFn

This is a table in SQL and called a "class" in Parse.

---

Note

In all of the Parse queries below assume this code replaces the ellipses (...). Because it doesn't change that much I don't want to keep repeating it:

  (rows: [AnyObject]?, error: NSError?) -> Void in
  if error == nil {
      if let rows = rows as? [PFObject] {
          for row in rows {
              println(row)
          }
      }
  } else {
      println(error)
  }

---

Constraints

---

SQL

SELECT * 
FROM COMMENT

PARSE - SWIFT

var query = PFQuery(className:"COMMENT")
query.findObjectsInBackgroundWithBlock {...}

---

SQL - ORDER BY

SELECT member, text 
FROM COMMENT
ORDER BY 
    updatedAt,
    member,
    createAt DESC,
    objectId DESC

PARSE - SWIFT

var query = PFQuery(className:"COMMENT")
query.selectKeys(["member", "text"])
query.orderByAscending("updatedAt")
query.addAscendingOrder("member")
query.orderByDescending("createdAt")
query.addDescendingOrder("objectId")
query.findObjectsInBackgroundWithBlock {...}

---

SQL - TOP, WHERE

SELECT TOP 10 member, text 
FROM COMMENT 
WHERE objectId = "VZ9cibW0Oe"

PARSE - SWIFT

var query = PFQuery(className:"COMMENT")
query.selectKeys(["member", "text"])
query.limit = 10 // Default: 100, Valid Values: 1 - 1000
query.whereKey("objectId", equalTo: "VZ9cibW0Oe")
query.findObjectsInBackgroundWithBlock {...}

---

SQL - WHERE, OR

SELECT * 
FROM COMMENT 
WHERE 
    createdAt < "2015-08-08"
    OR
    createdAt > "2015-08-10"

PARSE - SWIFT

var lessThanDate = PFQuery(className:"COMMENT")
lessThanDate.whereKey("createdAt", lessThan: "2015-08-08")

var greaterThanDate = PFQuery(className:"COMMENT")
greaterThanDate.whereKey("createdAt", greaterThan: "2015-08-10")

var query = PFQuery.orQueryWithSubqueries([lessThanDate, greaterThanDate])
query.findObjectsInBackgroundWithBlock {...}

---

SQL - WHERE, AND

SELECT * 
FROM COMMENT 
WHERE 
    createdAt > "2015-08-08"
    AND
    createdAt < "2015-08-09"
    AND
    text IS NOT NULL

PARSE - SWIFT

var query = PFQuery(className:"COMMENT")
query.whereKey("createdAt", greaterThan: "2015-08-08")
query.whereKey("createdAt", lessThan: "2015-08-09")
query.whereKeyExists("text")
query.findObjectsInBackgroundWithBlock {...}

---

Query Execution

---

These are the different ways you can execute a query besides using findObjectsInBackgroundWithBlock.

SQL - TOP 1

SELECT TOP 1 * 
FROM COMMENT

PARSE - SWIFT

var query = PFQuery(className:"COMMENT")
query.getFirstObjectInBackgroundWithBlock {...}

// Alternative (Does not happen in background):
if let row: PFObject = query.getFirstObject() {
    // A result was returned.
}

---

SQL - COUNT

SELECT COUNT(member) 
FROM COMMENT 
WHERE member = "VZ9cibW0Oe"

PARSE - SWIFT

var query = PFQuery(className:"COMMENT")
query.whereKey("member", equalTo: "VZ9cibW0Oe")
query.countObjectsInBackgroundWithBlock  {
  (count: Int, error: NSError?) -> Void in
  if error == nil {
    println("Member commented \(count) times.")
  }
}

---

Multiple Tables

---

Let's step things up by showing you how to query data from multiple tables. Parse doesn't have JOINs so how you approach your solution might look a little different.

Let's include the Member table/class now.

MEMBER
objectId	realname	username	deletedAt	createdAt	updatedAt	ACL
VZ9cibW0Oe	John Doe	JohnDoe		2015-08-07T04:46:43.738Z	2015-08-07T04:46:43.738Z	Public Read, VZ9cibW0Oe
ay9bvW2b1U	Jane Smith	JaneSmith	2015-09-01T10:15:14.571Z	2015-08-08T14:15:59.571Z	2015-08-08T14:15:59.571Z	Public Read, ay9bvW2b1U
ogm7EIAiY9	Bill Ford	BillFord		2015-08-10T15:05:53.314Z	2015-08-08T15:05:53.314Z	Public Read, ogm7EIAiY9

SQL - INNER JOIN, WHERE FROM ONE TABLE

SELECT 
    MEMBER.realname, 
    COMMENT.text
FROM 
    COMMENT
    INNER JOIN MEMBER ON COMMENT.member = MEMBER.objectId
WHERE

    COMMENT.objectId = "ogm7EIAiY9"

PARSE - SWIFT

var query = PFQuery(className:"COMMENT")
query.selectKeys(["member", "text"]) // Note, you cannot access fields from the MEMBER table in the selectKeys, just fields from COMMENT.

// includeKey Tips
// Include the whole MEMBER row/object.
// This is the field name from COMMENT, not the table name.
// You must also make sure the field you are using "includeKey" is also in the "selectKeys" array.
query.includeKey("member")

query.whereKey("objectId", equalTo: "ogm7EIAiY9")
query.findObjectsInBackgroundWithBlock {
    (rows: [AnyObject]?, error: NSError?) -> Void in
    if error == nil {
        if let rows = rows as? [PFObject] {
            for row in rows {
                let text = row["text"] as! String
                if let member = row["member"] as? PFObject {
                    let username = member["username"] as! String
                }
            }
        }
    } else {
        println(error)
    }
}

---

SQL - INNER JOIN, WHERE FROM TWO TABLES, IS [NOT] NULL

SELECT 
    MEMBER.realname, 
    COMMENT.text
FROM 
    COMMENT
    INNER JOIN MEMBER ON COMMENT.member = MEMBER.objectId
WHERE

    COMMENT.text IS NOT NULL
    AND
    MEMBER.deletedAt IS NULL

PARSE - SWIFT

var memberQuery = PFQuery(className:"MEMBER")
memberQuery.whereKeyDoesNotExist("deletedAt") // IS NULL

var commentQuery = PFQuery(className:"COMMENT")
commentQuery.selectKeys(["member", "text"])
commentQuery.includeKey("member")
commentQuery.whereKeyExists("text") // IS NOT NULL

// The first param is the field from COMMENT that represents the MEMBER object.
commentQuery.whereKey("member", matchesQuery: memberQuery)

commentQuery.findObjectsInBackgroundWithBlock {
    (rows: [AnyObject]?, error: NSError?) -> Void in
    if error == nil {
        if let rows = rows as? [PFObject] {
            for row in rows {
                let text = row["text"] as! String
                if let member = row["member"] as? PFObject {
                    let username = member["realname"] as! String
                }
            }
        }
    } else {
        println(error)
    }
}

---

For more info on doing queries in Parse, go here.