OQL Join without reference

1
I have a parent child self-association, let's say B (parent) <- A (child). For performance reasons, there is only one owner. Now, in my OQL, I want to join some information of the siblings of a child. I use FROM A, JOIN B, which works perfectly. However, I want to go from B to its children again. In SQL this should be no problem, since a join ON is based on a shared value between two rows in seperate tables. Mendix uses join based on references, which makes me dependent on the direction of the association.  How can I go from the child to its siblings, without making the reference owner 'both'?
asked
1 answers
2

AFAIK as I know you can you use that assocation in both ways without changing the owner. The OQL is transformed in a SQL statement that does a 2 step join (item  to item_item table and item_item to item).

Selecting children of a parent

FROM MySecondModule.Item CHILD
WHERE CHILD/MySecondModule.Item_Item = $PARENT 
SELECT CHILD/Name As Name

Selecting children with parent

FROM MySecondModule.Item ITEM
LEFT JOIN ITEM/MySecondModule.Item_Item/MySecondModule.Item PARENT
SELECT 
ITEM/Name AS ITEMNAME,
PARENT/Name AS PARENTNAME

If you have a non-assocation reference from one entity to another (classic database foreign keys) you can make oql like.

FROM Integratie.ImportWerkorder AS IRC  
LEFT JOIN Contracten.Werkorder AS IR ON (IRC/Nummer = IR/Nummer)

you can trick this with an autonumber which you have to copy (sorry) to the children of the item

answered