Querying data from multiple tables into a custom struct in SQLite.swift

Question

I have a database that has the tables "Orders" and "OrderDetails" and a custom struct Order that I want to query data from these tables into. I understand how to query data from a singular table in SQLite.swift from am confused on how to query from multiple tables.

Here's the code where I query the data into the struct.

override func viewDidLoad() {
    super.viewDidLoad()
    //additional setup
    /*
     Need to build a query that reads info from the "Orders" Table and "OrderDetails"
     
     From Orders:
        - OrderID    -> Order.id
        - OrderDate  -> Order.date
        - CustomerID -> Order.customer
        - ShipperID  -> Order.shipper
     From OrderDetails:
        - ProductID  -> Order.item
        - Quantity   -> Order.quantity
     */
    do {
        let db = makeDBConnection()
        //Define the "Orders" and "OrderDetails" Tables
        let orders = Table("Orders")
        let details = Table("OrderDetails")
        //Deine the columns of the "Orders" Table
        let id = Expression<Int64>("OrderID")
        let date = Expression<String>("OrderDate")
        let customer = Expression<Int64>("CustomerID")
        let shipper = Expression<Int64>("ShipperID")
        //Define the columns of the "OrdrDetails" Table that are used
        let product = Expression<Int64>("ProductID")
        let quantity = Expression<Int64>("Quantity")
        let order_id = Expression<Int64>("OrderID")
        
        //JOIN fucn to add the columns from "OrderDetails" to "Orders" so that we can read the data into an Order obj
        let query = orders
            .select(orders[id], details[order_id])
            .join(details, on: orders[orders[id]] == orders[details[order_id]])
        
        for order in try db.prepare(query) {
            let order = Order(
                Int(order[id]),
                order[date],
                Int(order[customer]),
                Int(order[product]),
                Int(order[quantity]),
                Int(order[shipper])
            )
            
            ordersArray.append(order)
        }
    }
    catch {
        print(error)
    }
    tableView.delegate = self
    tableView.dataSource = self
    
    //for updating the tableView
    NotificationCenter.default.addObserver(self, selector: #selector(loadList), name: NSNotification.Name(rawValue: "load"), object: nil)
}

To my understanding the join function, basically lines up the rows from the "OrderDetails" page where the OrderID is the same, but when I run the application there's no data in my table view. I know that the issue is somewhere in this code block because I have two other table view that query from a single table perfectly. Is the issue how I wrote the ".join" statement or how I'm referencing the data in the "Order" initializer? I just started working with SQLite.swift a couple weeks ago so if anyone could provide a good explanation of where my mistake is that would be very much appreciated!

Answer 1

I figured this out by having a nested loop that loops through the tables that is created by the .filter() function.

Here's the code that worked for me:

for order in try db.prepare(orders) {
    let orderID = order[id]
    let orderDetails = details.filter(order_id == orderID)
            
    for details in try db.prepare(orderDetails) {
            let order = Order(
                Int(order[id]),
                order[date],
                Int(order[customer]),
                Int(details[product]),
                Int(details[quantity]),
                Int(order[shipper])
            )
            ordersArray.append(order)
        }
    }

Answer 2

please follow below

struct MessageModel: Codable,Hashable {
    var conversation_id: Int?
    var conversation_recipient_id: Int?
    var timestamp: Int?
    var content_type: String?
    var message: String?
    var user_id: Int?
    var user_name: String?
    var isOnline:String?
    var group_id:Int?
    var room_id:Int?
    var local_conversation_id:Int?
    var room_unique_id:String?
}


 func dbTables_ChatMessage_GetOfflineData() -> [MessageModel]{
        var modelArray = [MessageModel]()
        var model = MessageModel()
        
        let quey = " select * from message_list where isOnline = 'false' "
        do{
            let result = try DB.prepare(quey)
            for row in result{
                for (values,coumnName) in result.columnNames.enumerated(){
                    switch coumnName {
                    case "conversation_id":
                        model.conversation_id =  Int((row[values] as? Int64)!)
                    case "conversation_recipient_id":
                        model.conversation_recipient_id =  Int((row[values] as? Int64)!)
                    case "timestamp":
                        model.timestamp =  Int((row[values] as? Int64)!)
                    case "content_type":
                        model.content_type =  row[values] as? String
                    case "message":
                        model.message =  row[values] as? String
                    case "user_id":
                        model.user_id =  Int((row[values] as? Int64)!)
                    case "user_name":
                        model.user_name =  row[values] as? String
                    case "group_id":
                        model.group_id =  Int((row[values] as? Int64)!)
                    case "isOnline":
                        model.isOnline =  row[values] as? String
                    case "room_id":
                        model.room_id =  Int((row[values] as? Int64)!)
                    case "local_conversation_id":
                        model.local_conversation_id =  Int((row[values] as? Int64)!)
                    case "room_unique_id":
                        model.room_unique_id =   row[values] as? String
                    default:
                        break
                    }
                }
                modelArray.append(model)
            }
            
            
        } catch  {
            print(error.localizedDescription)
        }
    
    return modelArray
    }
    

so you will get array of table records by using following code:

let finalValues = dbTables_ChatMessage_GetOfflineData()