I
ITistic
Hi everyone,
Just curious as to how much of you OOP developers are handling this
scenario. Say I have an Order and OrderItem table. Example schema
below:
Order
==
order_id int (PK)
customer_id int
CreatedOn datetime
ModifiedOn datetime
OrderItem
==
order_item_id int (PK)
order_id int (FK)
quantity int
description varchar(50)
unit_price money
CreatedOn datetime
ModifiedOn datetime
What I need to do is query the database to display all orders within a
given date range and display these orders and brief details regarding
the items belonging to each. In my business object layer I have Order
and OrderItem objects. I could easily query the database for each
order and THEN query the database separately for each Order's
OrderItem objects and this would solve the problem, but performance
would be horrible. The only idea I've come up with is to create a view
that contains the fields from the Order table AND the OrderItem table.
I could then query the view based upon the given date range and then
write code to transform the view records returned from the view into
my business objects. I guess I'd use a For loop to loop through the
records and somehow (multiple ways of handling it) create the proper
Order objects and populate each Order object's OrderItems with the
proper items.
I guess what I'm wanting to know is if my solution above is common. If
not, what are the rest of you doing? It just seems like I can't come
up with a solution that really makes sense. It seems there has to be a
better solution. I'm curious as to what the rest of you are doing and
how you handle this problem between your business object layer and
data layer.
Any suggestions, comments, concerns, etc. are welcome. Thanks in
advance for your time and replies.
-- Shawn
Just curious as to how much of you OOP developers are handling this
scenario. Say I have an Order and OrderItem table. Example schema
below:
Order
==
order_id int (PK)
customer_id int
CreatedOn datetime
ModifiedOn datetime
OrderItem
==
order_item_id int (PK)
order_id int (FK)
quantity int
description varchar(50)
unit_price money
CreatedOn datetime
ModifiedOn datetime
What I need to do is query the database to display all orders within a
given date range and display these orders and brief details regarding
the items belonging to each. In my business object layer I have Order
and OrderItem objects. I could easily query the database for each
order and THEN query the database separately for each Order's
OrderItem objects and this would solve the problem, but performance
would be horrible. The only idea I've come up with is to create a view
that contains the fields from the Order table AND the OrderItem table.
I could then query the view based upon the given date range and then
write code to transform the view records returned from the view into
my business objects. I guess I'd use a For loop to loop through the
records and somehow (multiple ways of handling it) create the proper
Order objects and populate each Order object's OrderItems with the
proper items.
I guess what I'm wanting to know is if my solution above is common. If
not, what are the rest of you doing? It just seems like I can't come
up with a solution that really makes sense. It seems there has to be a
better solution. I'm curious as to what the rest of you are doing and
how you handle this problem between your business object layer and
data layer.
Any suggestions, comments, concerns, etc. are welcome. Thanks in
advance for your time and replies.
-- Shawn