Realm implementing GROUP BY query equivalent in swift

by

Realm GROUP BY query equivalent can be achieved in swift very effectively. Going ahead in the post I will create an imaginary requirement and I will guide you through the necessary solution.

Let us start with our realm data model

import UIKit
import RealmSwift
@objcMembers final class Room:Object{
    dynamic var roomID:String? = ""   //Room ID
    dynamic var attendantID:String? = "" //Room attendant ID
    dynamic var roomStatus:String? = ""  //room status CLEANED INSPECTED DIRTY INPROGRESS 
}

The model above contains simple information about Room collection attendantID and roomStatus.

Now suppose we want to show the total number of CLEANED, INSPECTED, DIRTY, INPROGRESS for every attendant in a tableview with one cell per attendant. It would have been very simple in SQLIte using SUM and GROUP BY but in realm we will have to improvise a bit. I will use a combination of distinct and fetch to get the desired result.

//1.Fetch all unique attendantID
 let attendantIDs = Set(realm!.objects(Room.self).value(forKey: "attendantID") as! [String])
//2. Loop through attendantIDs array.
 attendantIDs?.forEach({ (attendantID) in
//3. fetch result for each attendantID
 let attendantIDResult = realm?.objects(Room.self).filter("attendantID = '\(attendantID)'")
//4. Now fetch count for each CLEANED DIRTY INSPECTED INPROGRESS
// every loop will give data for a single attendantID save it
  let cleanedCount = attendantIDResult?.filter("roomStatus = 'CLEANED'").count
  let dirtyCount = attendantIDResult?.filter("roomStatus = 'DIRTY'").count
  let inspectedCount = attendantIDResult?.filter("roomStatus = 'INSPECTED'").count
  let inprogressCount = attendantIDResult?.filter("roomStatus = 'INPROGRESS'").count
})

Walkthrough of steps above creating GROUP BY in realm

  1. I have used SET to fetch distinct attandentID from the collection
  2. Then I use foreach on resulting attandantID  array
  3. In the foreach block, I fetch all the results for given attandentID
  4. Realm allows us to save a result and query it further which i use in step 4 to get a total number of individual status

The above example was given to give you an effective GROUP BY alternative for realm using swift. Hope it helps

 

 

 

A pat on the back !!