Lost in joins

BIZ TMS Aurelius
1

Hi.

Please forgive me for my questions if they sound obvious. I am a beginner with XData and Aurelius (or any ORM framework). I am searching the internet and the TMS documentations already for 3 days and don't find a working answer.

I have to write a client/server application. The database structure looks like this:
image

I have created the DB by writing Aurelius classes. So this is going well.
Thus we have a category that has multiple components, each components has multiple attributes.

At the client side now, I would like to get a dataset that would return (for a specific category) the list of all the components and their attributes.

In SQL, it would look like this:

SELECT
CCM_CATEGORY.ID AS CAT_ID,
CCM_CATEGORY.NAME AS CAT_NAME,
CCM_CATEGORY.TEMPLATE AS CAT_TEMPLATE,
CCM_CATEGORY.PARENT_ID AS CAT_PARENTID,
CCM_COMPONENT.ID AS COMP_ID,
CCM_COMPONENT.NAME AS COMP_NAME,
CCM_COMPONENT.TEMPLATE AS COMP_TEMPLATE,
CCM_ATTRIBUTE.ID AS ATTRIB_ID,
CCM_ATTRIBUTE.NAME AS ATTRIB_NAME,
CCM_ATTRIBUTE.DESCRIPTION AS ATTRIB_DESCRIPTION,
CCM_EDITOR.ID AS EDIT_ID,
CCM_EDITOR.NAME AS EDIT_NAME
FROM
CCM_CATEGORY
LEFT JOIN CCM_CATEGORY_COMPONENT ON CCM_CATEGORY_COMPONENT.CATEGORY_ID=CCM_CATEGORY.ID
LEFT JOIN CCM_COMPONENT ON CCM_COMPONENT.ID=CCM_CATEGORY_COMPONENT.COMPONENT_ID
LEFT JOIN CCM_COMPONENT_ATTRIBUTE ON CCM_COMPONENT_ATTRIBUTE.COMPONENT_ID = CCM_COMPONENT.ID
LEFT JOIN CCM_ATTRIBUTE ON CCM_ATTRIBUTE.ID = CCM_COMPONENT_ATTRIBUTE.ATTRIBUTE_ID
LEFT JOIN CCM_EDITOR ON CCM_EDITOR.ID = CCM_ATTRIBUTE.EDITOR_ID
WHERE
CCM_CATEGORY.ID = 1
(the WHERE close should be a parameter)

First question:
In order to get such a dataset, I thought I should define an entity that returns the data. But if I do this, a new table is created, which is not what I want.

Second question:
Can you help in how the joins should be defined? All the examples I found are always based on a one to many relationship (which is done automatically by Aurelius). But here, I need to go from a many to one (Category_Component to Component) back to a one to many (Component to Component Attribute).

2

You don't need to create your own class. In the majority of the cases, you will get the existing objects, and since they have associations, you get data from the other object (table) by reading the associated properties. For example, CategoryComponent.Component..Name will get the name of component from the CategoryComponent intermediate table.

For that you should simply use the ManyValuedAssociation attribute to map the other side of the association. This allows you to go from CCM_CATEGORY to CCM_CATEGORY_COMPONENT.

From TMS Data Modeler (which looks you are using), they are not created automatically, but you can enable them in specific tables in the mappings tab:

Additional references:

3

Thank you Wagner.
I will for sure come back to this. But due to the dead line, I will fall back to normal queries for now.

1 Like