Multiple join on the same table

[boentp]

Hi Isomorphic,
I am currently evaluating SmartClient (v110_p2016-10-01 evaluation)
I wanted to get DataSource definition (ds.xml) for simple SQL command as follow
Suppose I have two table as follow

table a, consists of 3 fields : a_key, b1 and b2
table b, consists of 2 fields : b_key, b_name
both fields a.b1 and a.b2 are foreign key to table b.b_key

SELECT
a.a_key,
b1.b_name as b_name_1,
b2.b_name as b_name_2
FROM
a
LEFT JOIN b AS b1 ON (a.b1 = b1.b_key)
LEFT JOIN b AS b2 ON (a.b2 = b2.b_key)

I tried to use customSQL="true" but could not get the syntax right, despite of searching the doc.

Thank you,

[Blama]

Hi boentp,

that is pretty easy and will be fully generated for you. Search this document for foreignKey (you'll need it twice, at b1 and b2 fields) and includeFrom (you'll need it twice, at b1_name and b2_name fields) and includeVia (you'll need it twice, at b1_name and b2_name fields).
If you want a certain generated name as rename for table b in the generated SQL (perhaps needed if you do custom stuff in the WHERE clause or a customSelectExpression), also see relatedTableAlias.

Best regards
Blama

[boentp]

Thanks Blama,

I've got it. For my above SQL, the corresponding ds.xml would be :

SELECT
a.a_key,
b_1.b_name as b_name_1,
b_2.b_name as b_name_2
FROM
a
LEFT JOIN b AS b_1 ON (a.b1 = b_1.b_key)
LEFT JOIN b AS b_2 ON (a.b2 = b_2.b_key)

I changed the table alias name (b1 to b_1 and b2 to b_2), as not to be confused with field name of table a.

the resulting ds.xml would be :

<field name="b1" foreignKey="b.b_key" relatedTableAlias="b_1"/>
<field name="b2" foreignKey="b.b_key" relatedTableAlias="b_2"/>
<field name="b_name_1" includeFrom="b.b_name" includeVia="b1"/>
<field name="b_name_2" includeFrom="b.b_name" includeVia="b2"/>

Regards,

[Blama]

Hi,

Good you got it working.

W.r.t your initially shown SQL: For the id fields you'll also want joinType="outer" (in the same docs).

If editing in a GUI element you'll also want displayField="theIncludedField" and then use this field in the GUI.

Best regards
Blama

[boentp]

Thanks Blama,
I've got that working.