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:
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:
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
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
CCM_CATEGORY.ID = 1
(the WHERE close should be a parameter)
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.
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).