Realm implementing GROUP BY query equivalent in swift

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 !!