在SQLite.swift中将多个表中的数据查询到自定义结构中

Question

我有一个数据库，里面有表"订单"以及"；订单详细信息"；以及一个自定义结构Order，我想将这些表中的数据查询到该结构中。我了解如何在SQLite.swift中从单个表中查询数据。我对如何从多个表中查询感到困惑。

这是我将数据查询到结构中的代码。

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

就我对联接函数的理解而言，基本上是将"；订单详细信息"；页面中的OrderID相同，但当我运行应用程序时，表视图中没有数据。我知道这个问题在这个代码块的某个地方，因为我有另外两个表视图，可以完美地从一个表中查询。问题是我是怎么写的；。加入"；语句或我如何引用"；订单"；初始化器？我几周前刚开始使用SQLite.swift，所以如果有人能很好地解释我的错误，我将不胜感激！

Answer 1

我通过在.filter((函数创建的表中循环一个嵌套循环来解决这个问题。

这是对我有效的代码：

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

请遵循以下

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
}

因此，您将使用以下代码获得表记录数组：

let finalValues = dbTables_ChatMessage_GetOfflineData()