SQL Templating in SmartGWT

Question

I am trying to use SQL templating in smartgwt to load data from my database into a listgrid but I am not able to get the desired result. This is the raw SQL query which I am trying to adopt in SmartGWT to get the result

SELECT
dbo.province.provincename as province,
dbo.province.capital,
dbo.province.code,
dbo.province.telcode,
dbo.province.taxcode,
dbo.county.countyname as county,
dbo.district.districtname as district,
dbo.zone.alternateName as zone,
dbo.neighbourhood.alternateName as neigbhour,
dbo.city.cityname as city,
dbo.city.taxcode,
dbo.city.fdocode,
count(dbo.customer.customeraltname) as countCustomer
FROM
dbo.county
INNER JOIN
dbo.province
ON
(
    dbo.county.provinceID = dbo.province.id)
INNER JOIN
dbo.district
ON
(
    dbo.county.id = dbo.district.countyID)
INNER JOIN
dbo.city
ON
(
    dbo.district.id = dbo.city.districtID)
INNER JOIN
dbo.zone
ON
(
    dbo.city.id = dbo.zone.cityId)
INNER JOIN
dbo.neighbourhood
ON
(
    dbo.zone.id = dbo.neighbourhood.zoneId)
INNER JOIN
dbo.customer
ON
(
    dbo.neighbourhood.id = dbo.customer.neighbourhoodId)
    Group By dbo.province.provincename,
dbo.province.capital,
dbo.province.code,
dbo.province.telcode,
dbo.province.taxcode,
dbo.county.countyname,
dbo.district.districtname,
dbo.zone.alternateName,
dbo.neighbourhood.alternateName,
dbo.city.cityname,
dbo.city.taxcode,
dbo.city.fdocode

Below is my ds.xml

<DataSource ID="CusNeiGroupDS" serverType="sql">
<fields>
    <field name="id" type="integer" />
    <field name="provincename" title="province" type="text"/>
    <field name="capital" title="capital" type="text"/>
    <field name="code" title="code" type="text"/>
    <field name="telcode" title="telcode" type="text"/>
    <field name="taxcode" title="taxcode" type="text" />
    <field name="provinceId" type="integer" tableName="county"/>
    <field name="countyname" title="county" type="text" tableName="county"/>
    <field name="district" title="district" type="text" />
    <field name="city" title="city" type="text"  />
    <field name="zone" title="zone" type="text"  />
    <field name="neighbour" title="neighbour" type="text"  />
    <field name="taxcodecity" title="taxcodecity" type="text"
        />
    <field name="fdocode" title="fdocode" type="text" />
    <!-- <field name="countCustomer" title="countCustomer" type="int" /> -->
</fields>


<operationBindings>
    <operationBinding operationId="summary"
        operationType="fetch">
        <selectClause>
            districtname as
            district,
            alternateName as zone,
            alternateName as neigbhour,
            cityname
            as city,
            fdocode
        </selectClause>
        <tableClause>province, county, district, zone, neighbourhood, city
        </tableClause>
        <whereClause>
            province.Id = county.provinceId
        AND district.countyId = county.Id
        AND city.districtId = district.Id
        AND neighbourhood.zoneId = zone.Id
        </whereClause>
    </operationBinding>
</operationBindings>

the error I get is

Execute of select: SELECT COUNT(*) FROM CusNeiGroupDS WHERE ('1'='1') on db: SQLServer threw exception: java.sql.SQLException: Invalid object name 'CusNeiGroupDS'. - assuming stale connection and retrying query.

But when I put the table name in the datasource like this,I get output but only from that table which I mention and not from the other tables which are joined with FK.

<DataSource ID="CusNeiGroupDS" serverType="sql" tableName="province">

Answer 1

I was able to achieve this with using includeFrom and foreignKey tags in the datasources. Then create another datasource where i use it to include all the coumns I need from the different tables.Like so

<DataSource ID="neighbourDS_1" serverType="sql" tableName="neighbourhood"     inheritsFrom="neighbourDS">
<fields>
    <field name="provincename" includeFrom="provinceDS.provincename" />
    <field name="capital" includeFrom="provinceDS.capital" />
    <field name="code" includeFrom="provinceDS.code" />
    <field name="telcode" includeFrom="provinceDS.telcode" />
    <field name="countyname" includeFrom="countyDS.countyname" />
    <field name="district" includeFrom="districtDS.districtname" />
    <field name="city" includeFrom="cityDS.cityname" />
    <field name="taxcodecity" includeFrom="cityDS.taxcodecity" />
    <field name="fdocode" includeFrom="cityDS.fdocode" />
    <field name="zone" includeFrom="zoneDS.zone" />
</fields>
</DataSource>