Hi,

I am trying to get a one-to-many join with the PHP libs working but I am having issues setting it up.
For some reason the link method does not set up the joins as i expect

What i am trying to do:
- I have table MATERIALS containing all my materials with fields ID, DESCRIPTION, etc
- a table CHECKLIST containing regulated materials with fields ID, MATERIAL_ID (links to table MATERIALS), etc
- and a table CHECKLIST_ALTERNATIVES containing alternatives materials for a regulated material with fields CHECK_ID (links to table CHECKLIST) andMATERIAL_ID(links to tableMATERIALS`)

The query I am trying to recreate (I think) is:

SELECT DISTINCT MATERIALS.ID as 'dteditor_pk',
    ALTERNATIVES.DESCRIPTION
FROM MATERIALS
JOIN CHECKLIST
    ON CHECKLIST.MATERIAL_ID = MATERIAL.ID
JOIN CHECKLIST_ALTERNATIVES
    ON CHECKLIST_ALTERNATIVES.CHECK_ID = CHECKLIST.ID
JOIN MATERIALS ALTERNATIVES
    ON ALTERNATIVES.ID = CHECKLIST_ALTERNATIVES.MATERIAL_ID

because i am referencing the MATERIALS table twice i need to alias the second instance.
This seems to return the data correctly if i run the query directly on the database:

dteditor_pk DESCRIPTION
6443 test material 1
6447 test material 2
etc

but this requires three joins which link method does not support.
So i create a VIEW combining CHECKLIST_ALTERNATIVES and MATERIALS on field MATERIAL_ID:

SELECT DISTINCT VDSTAM.ID as 'dteditor_pk',
    ALTERNATIVES.DESCRIPTION
FROM MATERIALS
JOIN CHECKLIST
    ON CHECKLIST.MATERIAL_ID = MATERIALS.ID
JOIN CHECKLIST_ALTERNATIVES_VIEW ALTERNATIVES
    ON ALTERNATIVES.CHECK_ID = CHECKLIST.ID

This still returns the correct data when checked running the query.

Implementing this with the PHP libs:

$editor = Editor::inst($db, 'MATERIALS', "ID");

$fields = array();
$fields[] = Field::inst('MATERIALS.ID');
$fields[] = Field::inst('MATERIALS.DESCRIPTION');
$fields[] = Field::inst('CHECKLIST.MATERIAL_ID');

$editor->fields($fields);
$editor->leftJoin('CHECKLIST', 'CHECKLIST.MATERIAL_ID', '=', 'MATERIALS.ID');

$m = Mjoin::inst('MATERIALS');
$m->link('MATERIALS.ID', 'CHECKLIST.MATERIAL_ID');
$m->link('CHECKLIST_ALTERNATIVES_VIEW.CHECK_ID', 'CHECKLIST.ID');

$fields = array();
$fields[] = Field::inst('DESCRIPTION'); // should be from CHECKLIST_ALTERNATIVES_VIEW

$m->fields($fields);

$editor->join($m);

The generated SQL query is:

SELECT DISTINCT [MATERIAL].[ID] as 'dteditor_pkey',
    [MATERIALS].[OMSCHRIJVING1] as 'OMSCHRIJVING1' 
FROM  [MATERIALS]
JOIN [CHECKLIST] 
    ON [MATERIALS].[ID] = [CHECKLIST].[MATERIAL_ID]   
JOIN [MATERIALS] 
    ON [MATERIALS].[CHECK_ID] = [CHECKLIST].[ID]

This generates an SQL error because it is trying to set up a self-referencing table MATERIALS without an alias.
But this is not the query i am looking for anyway compared to the query above.

It seems the second link is not used (correctly):

$m->link('CHECKLIST_ALTERNATIVES_VIEW.CHECK_ID', 'CHECKLIST.ID');

should not lead to:

JOIN [MATERIALS] 
    ON [MATERIALS].[CHECK_ID] = [CHECKLIST].[ID]

Can you provide any insight into how to deal with this issue?
I hope everything is clear, if not i can add more info