Inter connected joins in sql- replication in ds.xml

[marwan12]

Hi All,

In my main datasource, data is fetched from table a(which has primary key as a_id)
table b has column a_id which is foreing key to a_id of table a
table c has b.name which is present in table b as well as foreign key
we want a variable which does group_concat(table_c.b.name) as variable in ds.xml of table_a,

the sql query for the same looks like this
Select
group_concat(table_c.b.name)from table_a
LEFT OUTER JOIN table_b b on table_a.a_id=table_b.a.id
LEFT OUTER JOIN table_c c on table_b.b_name=table_c.b_name

How can we fetch the variable evaluated from "group_concat(table_c.b.name)" in ds.xml?

[Blama]

See the docs.serverds.datasourcefield Package. You are looking for primaryKey, foreignKey (+joinType for outer join, +relatedTableAlias for constant joined table names), includeFrom attributes, as well as a customSelectExpression on the computed field in the related child table. I'd have the relatedTableAlias of the father table field and the tableName of the child DS the same, so that the CSE also works if the child DS is used alone.

Best regards
Blama

[marwan12]

Tried something like this :
<field name="a_id" foreignKey="table_b.a_id" joinType="outer" relatedTableAlias="table_b_1" hidden="true" />
<field name="atr_id" title="hdw line id" includeFrom="table_b.a_id" includeVia="a_id" width="100"/>
<field name="table_b_1.b_name" foreignKey="table_c.b_name" joinType="outer" relatedTableAlias="table_c_1" hidden="true" />

But not working

[Isomorphic]

You appear to have read some, but not all, of what Blama wrote. In particular you missed his suggestion of a customSelectExpression. Further, see the Server Summaries overview, as you may be able to have the whole query generated without adding any custom sql (the "concat" summaryFunction may be close enough to what you want).

Ultimately however, you may just want to use <customSQL> to achieve this particular query.

Finally, if you are going to complain that something is not working, you do need to say what is not working.