Model and Query Data
 Create and Display Many-to-Many Relationships
This lesson is part of the Developer Online Class for OutSystems 9 course.

in this lesson we'll see how to model many-to-many relationships
and display them on the screen. This will allow us to display the
items of an order. So first of all we'll need to go here into the Data tab
and we will need to create an Entity
to model this many-to-many relationship let me just
open here the diagram and
the Entity that we want to create is in fact
the Order Item Entity, and lets
add the Entity to our diagram here
and now we'll need to set
a few attributes in this Entity
as we said, this Entity is going to represent
the items of an order so we will need to identify
what is the Order and what is the item
in that Order, so what is the Product that this item refers to
and we also need to identify
the quantity of that product and the total price
for this OrderItem
noticed that when we created the OrderId
attribute and the ProductId attribute we were in fact creating
relationships to the Order and to the Product Entity
so the many-to-many relationship between Order
and Product Entities is actually created
by defining an extra Entity - OrderItem
that establishes two one-to-many relationships with the Order entity
and the Product Entity. Okay so
this is the many-to-many relationship let me just
finish here defining the entity, I want to make all these attributes
Quantity is mandatory as well and the TotalPrice
and I want to make sure that the user
if the user tries to add the same product
to the same Order, he will get an error
and to make this happen I will define here
in the Entity I will define and Index
so create here in new Index
I'm going to call it 'Unique Products'
in the Order, I'm going to set the unique
parameter to True, to Yes in this case
and I want to make sure that the same ProductId
doesn't repeat itself for the same
OrderId. So indexes are normally used to improve the database performance
but you can also use them to ensure that
two records don't have attributes
with the same values. And this is what we're doing
with this index in the owner item entity
okay so we're done modeling the many-to-many relationship. Let's go back to our
Order Detail screen here
and now we want to display this many-to-many relationship
here in this detail page. Basically
we want to have a table with the items
of this order right after the form
where we have the details of the order. So to have the items
we will need first to fetch them from the database so this means that
here in the Preparation we will need to get
the OrderItem. So let's add an Aggregate here
and let's drag-and-drop the OrderItem notice that
the Product Entity comes along because we have a Foreign Key
to the Product Entity. Actually this is handy because
I want to sort the OrderItems by product name so
let's sort this from A to Z. I also want to filter
the OrderItems because I only want the items
of a specific Order and this means I have to create here a filter
so that the OrderId attributes of the OrderItem
this one here matches
the OrderId that we have as input parameter on the page
on the screen
and that's it. We're done let's go back
to the screen and add a Table Records
let's bind this Table Records to the Aggregate
that we have on the Preparation so here
this List property of the GetOrderItemsByOrderId
and now let's drag-and-drop
the order item into the Table Records
notice that the platform as replaced here
or has placed here actually the Product name
the Product Name attribute
that is from the Product Entity that we also have
on the Aggregate instead of showing the Foreign Key
it is showing the name of the product. Okay so
this is done let's publish and see how this looks in the browser
okay let's enter our order and
and here we have the table with the order items for now the its
empty because we we haven't added any products to our order
and actually in the next lesson we will see how to manage this many to many
and create order items for our orders
that's it