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!

I haven’t used sqlite.swift much but your join looks weird, shouldn’t it be more like `orders.join(details, on: order_id == ordes[id])` — Joakim Danielson, Feb 27 '22 at 19:19
@JoakimDanielson I changed my join to look like that, but I can't tell if it truly worked because there is still no data that appears when I run it. I think the problem is the way I reference "product" and "quantity" in the Order obj initializer. Any idea on how to correctly refrence them? — JonGrimes20, Feb 27 '22 at 19:33
@JoakimDanielson when I replace Int(order[product]) and Int(order[quantity]) with a number like 1 it works fine so I'm pretty sure it's the way I'm refrencing them — JonGrimes20, Feb 27 '22 at 19:36
Doesn’t `.select(orders[id], details[order_id])` mean you only want those two columns returned? What about removing that part completely? — Joakim Danielson, Feb 27 '22 at 20:14
@JoakimDanielson removing the .select() doesn't throw any errors but it still doesn't allow the data to be created in order to populate the table. — JonGrimes20, Feb 27 '22 at 20:22
@JoakimDanielson If I just set the products and quantity to 0 during the initial loop, is there a way I could just loop through the array and pull the data from the other table and set the products and quantity values in the second loop? — JonGrimes20, Feb 27 '22 at 20:25

score 0 · Answer 1 · answered Feb 27 '22 at 20:41

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)
        }
    }

score 0 · Answer 2 · edited Jul 10 '22 at 20:14

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()

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

2 Answers2