Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
Clear All
new posts

    DataSourceField includeFrom field with customSelectExpression

    Hi Isomorphic,
    maybe you will have some advice for the following example.

    We have 2 SQL datasources. Simple example:

    A.ds.xml with tableName="TABLE_A"
    Code:
    ...
    <field type="text" name="bId" nativeName="A_BID" foreignKey="B.B_id" optionDatasource="B" operator="equals"/>
    <field type="text" name="bFullName" includeFrom="B.fullName" operator="equals"/>
    ...
    B.ds.xml with tableName="TABLE_B"
    Code:
    ...
    <field name="id" type="text" hidden="true" nativeName="B_id" primaryKey="true" />
    <field name="firstName" type="text" nativeName="B_firstname"/>
    <field name="lastName" type="text" nativeName="B_lastname"/>
    <field name="fullName" type="text" customSelectExpression="concat(TABLE_B.B_firstname, concat(' ', TABLE_B.B_lastname))"/>
    ...
    When fetch operation is done, following SQL query is generated:
    Code:
    SELECT 
       ..., 
       TABLE_B.fullName AS bFullName
    FROM 
       TABLE_A, 
       TABLE_B 
    WHERE 
       ...
    CustomSelectExpression from field of B.ds.xml is not considered. Instead dataSourceField's name is used for DB column name. One working way is to have another customSelectExpression in the field bFullName in the A.ds.xml with select on TABLE_B returning concated fullname. In this case query has to be duplicated in two datasources and the right datasource - B.ds.xml is not used.

    I hope you understand what I try to achieve and I'll be thankful for your ideas.

    #2
    Duplicating that tiny bit of SQL is the right approach (or using a database-specific feature to create a virtual column if you prefer).

    As far as the framework's behavior, the current behavior of includeFrom (ignoring customized SQL) makes sense because the customized SQL may work for a normal select but not work in the context of a join. Your SQL happens to work in this case because you qualified it with the table name. But it would not work in cases where the generated join requires us to alias the table name (see for example dataSourceField.includeVia).

    While we could add a property to cause includeFrom to use customized SQL, this wouldn't provide a general solution, and just seems like more complexity than the other approaches you already have.

    Comment

    Working...
    X