Hey guys, I am writing a new application using AngularJS on the front end, with CI providing both the MVC for the single page apps and the AJAX responses for the angular.service calls. The backend database is going to by MySQL (but could just as easily be MSSQL, Oracle, or postgress - as long as it is RDBMS) Data is passed between the client and server using JSON notation, but in many cases the JSON contains nested objects. For example a list of customer orders
Code:
{
"customer":
{
"customer_name": "Bananaman",
"address":
{
"line1":"29 Acacia Road",
"line2":"Nuttytown"
}
}
"orders":
{
{
"order_id":27,
"order_date":"2014-01-01"
"order_lines":
{
"line_id":1,
"item_id": "ABC123",
"qty": 3
},
{
"line_id":2,
"item_id": "XYZ345",
"qty": 1
}
},
{
"order_id":39,
"order_date":"2014-05-25"
"order_lines":
{
"line_id":1,
"item_id": "FFF333",
"qty": 2
},
{
"line_id":2,
"item_id": "GGG444",
"qty": 1
}
}
}
}
So this data comes from three tables Customers, Orders [FK on CustomerID] and OrderLines [FK on OrderID]
The example above contains one customer record, followed by two orders, each with two lines
I am looking for a stategy to build these models that is consistent and performant.
One option is the get the customer list from the customer model as result_array then foreach through the array and query for the orders for that customer and from the array of orders, foreach through the orders to get the order lines and simply iteratively build the nested array and convert it to json using json_encode but this could require several hundred database calls and could be slow as PHP is responsible for the record associations.
A second option would be to return a flat result array by joining the customers, orders and lines into a single query and then iterate through the results set changing the nesting level each time the dimensional value (customer or orderID) changes. This becomes very unweildly if you are more than 3 levels deep
A third option would be to get the customers results, customerOrders results and customerOrdersLines and use some array intersect logic to force the subsets into their outer sets but again this gets unweildy with more levels and means lots of duplicated data in memory at the same time.
Which method is considered the most performant or are there any other alternatives.
The ultimate objective is to build the models so that order_model has a function to return the nested objects (i.e. is the responsibility of the model to contruct the array rather than the controller to call the separate models and construct the array)
One other option is to actually store the json objects pre-rendered in the database. Once an order is completed it is a fixed dataset and so safe to be json'ed
