ListGird datasource with serverType="generic" and includeFrom returning 0 rows

[jbrau01]

GWT : : v9.1p_2014-10-20/PowerEdition Deployment (built 2014-10-20)

Firefox : v24.0

I have a ListGrid configured with a SelectItem for an editable Users column. The ListGrid is configured with a ds.xml (accounts.ds.xml) and the SelectItem has its own users.ds.xml. Both ds.xml are configured for specific RPC calls for fetch and update , so we use serverType="generic". I want the Accounts data set returned from the server to contain the UserID, and not the UserName, and have the UserName displayed in the SelectItem of the Users Column, using a join with the users datasource. I am not able to get this to function properly using the includeFrom property in the accounts datasource. I am able to make some progress using two foreignKey columns, but if I swap out the foreignKey attribute with includeFrom, the accounts grid returns 0 rows (even though the server request is unchanged).

If have two issues currently:

1) includeFrom returns 0 rows. Two foreign key columns sort of works but I know that's not the out-of-the-box solution. I don't want to go this route long term if its not the correct approach.
2) Passing criteria for the accounts callback does not flow through to the users callback. How do I pass criteria into the users fetch? Because of the join operation I am trying to accomplish, I never explicitly calling the users fetch, so how do I configure the criteria that gets passed into it?

<!--users.ds.xml-->
<DataSource
        ID="users"
        serverType="generic"
>
    <operationBindings>
        <binding operationType="fetch" serverMethod="fetchAccountUsers">
            <serverObject lookupStyle="spring" bean="usersService"/>
        </binding>
    </operationBindings>
    
    <fields>
        <field name="AccountUserId"        type="sequence"   hidden="true"  primaryKey="true" />     
        <field name="AccountUserName"      type="text" title="Account User"  required="true" />
    </fields>
</DataSource>

<!--accounts.ds.xml-->
<DataSource
        ID="accounts"
        serverType="generic"
>
    <operationBindings>
        <binding operationType="fetch" serverMethod="fetchAccounts">
            <serverObject lookupStyle="spring" bean="accountService"/>
        </binding>
        <binding operationType="update" serverMethod="updateAccount">
            <serverObject lookupStyle="spring" bean="accountService"/>
        </binding>
    </operationBindings>

    <fields>

        <field name="AccountID"        type="integer"      hidden="true"   primaryKey="true"   />
        <field name="AccountName"   type="text"         />

        <field name="AccountUserID" title="User" type="integer" foreignKey="users.AccountUserId" displayField="AccountUserName"  canEdit="true"   editorType="SelectItem"    />

<!--    foreignKey here functions correctly, returns data
        
         <field name="AccountUserName"  foreignKey="users.AccountUserName"                    hidden="true"     />
-->

        <!-- includeFrom here does not work, 0 rows returned -->
        <field name="AccountUserName"  includeFrom="users.AccountUserName"                 hidden="true"      />


    </fields>
</DataSource>

Thanks!

[Blama]

Hi jbrau01,

1) Your 2nd FK definitely needs to stay commented out - in the parent table, only AccountUserId is a PK, so you can't link to AccountUserName.
Besides of this, your DS should work for SQL Databases and I don't think that this is different for other types. Your field name="AccountUserName" is correct.

2) I don't understand the question. If this is about the actual datalist of the SelectItem when editing and defining criteria for this list, you'll need to define a SelectItem in Java and configure its optionDataSource etc.

Best regards
Blama

[jbrau01]

Hi Blama, thanks for the response. These datasources are not connected directly to the databases, the serverType is "generic". The actual data fetches come through a service layer, but the underlying data is relational and very straight forward. The User has a UserID(PK) and UserName. The Account has a UserID field. I would like the grid to display the UserNames in the editable SelectItem, pre-populating the SelectItem value using only the Account's UserID. I am unable to achieve this relationship however, using the includeFrom field. Whenever I introduce the includeFrom field anywhere in the datasource, the # rows returned goes to 0.

Regarding the criteria, I think you right. I was hoping to avoid having to define the datasource in Java in configuring the criteria again, as I already configure the criteria for the Accounts DS call. The request criteria for the Users DS call can take the same criteria as the Accounts, but I don't believe this is possible through the simple XML configuration.

Thanks!

[Blama]

Hi,

see the includeFrom docs. They say something about non-SQL Datasources in the middle.
Perhaps look those docs up locally for your 4.1-version.

Why don't you use a DB connection and serverType="sql" if you are using PowerEdition?
Workaround:
If you are not displaying too much data, I think you could also define a clientside optionDataSource directly on the ListGridField. But I assume that will result in a call per row (don't know that, though).

Best regards
Blama

[jbrau01]

Hi Blama, again thank you for the response.

The docs are the same between the two versions, so I think its safe to assume the functionality hasn't changed in a future version.

We don't use a "sql" datasource because we have the business logic kept in stored procedures, and the application utilizes those stored procedures through service and database layers. We use "generic" datasources exclusively.

So is it impossible to accomplish this through basic XML datasource configuration? It seems that having two basic datasources configured via a basic join (includeFrom) is a simple use case, regardless of the datasource serverType.

Thanks!

[Isomorphic]

With your architecture choice, the framework has no access to SQL or JPA/Hibernate APIs, so a true database join is impossible. Instead, the best that can be done is an extremely inefficient join algorithm performed in memory inside the JVM.

If you actually want to do this, and you think there may be a framework bug here, then we need to see a minimal, standalone test case demonstrating the problem, which has been tested against a fully patched version of a recent release (not 4.1; too old).

Better options include:

1. do a real database join yourself and return the joined data

Or

2. Fix your architecture so that you are using either SQL, JPA or Hibernate DataSources. You actually haven't named any issues that would prevent this, as business logic performed before or after database operations is completely possible in the recommended architecture, as are stored procedures. For more information, revisit the QuickStart Guide chapter on the Server Framework.

[jbrau01]

I don't think there is a framework bug. My question is if this is even possible to join two "generic" datasources using the "includeFrom" property, with once datasource supporting a ListGrid and one datasource inside the SelectItem of one of the ListGrid columns.

I'm not sure an "inefficient" join would be a problem here, but I can assess the efficiency once I have a working module, if its even possible. We are only talking about a couple hundred accounts and a handful of users at most.

I don't need a join to be done really at all, I just need the framework to be able to connect the accounts datasource (ListGrid) with the users datasource (SelectItem) via the UserID. Ideally this could be done through some simple XML config, what I was attempting to do with the 2 XML's from the first post.

Thanks!

[Isomorphic]

Yes, and, as the docs tell you, this is supported, and we have a variety of autotests showing that the feature is working. Once you've corrected your XML as directed by Blama, there is no obvious issue that would cause this to fail, but we only have partial information. Hence, the need for a test case if you suspect a framework issue.

[jbrau01]

Yes thank you. I was able to make some progress. The closest I have gotten to everything working correctly is by:

Accounts DS : serverType="sql"
Users DS : serverType="generic"

The request won't work if they are both generic or both sql.

Still pending, however, is the criteria issue. I pass specific data into the ListGrid fetch for the account data. The users are fetched alongside this call. The users request criteria, however, doesn't have the original ListGrid criteria, it only has the UserIDs that are present in the Accounts fetch.

Is it possible to configure the secondary requests (the join/includeFrom fetches) with specific criteria, or at least, criteria from the parent data fetch, without explicit calls to fetchData() ?


Thanks again!

[Isomorphic]

We continue to show all combinations of settings working as documented.

The includeFrom docs to which you were previously referred explain when criteria and sort are valid for the included field when doing an in-memory join (only with fetchMode:"basic").

[jbrau01]

I am able to reproduce the issue using the below details. I know we aren't supposed to upload ZIP files so I am providing minimal code snippets and a screenshot of the results. I had to use the trial version of the most recent software (EE) because we are currently using an older version.

-SmartGWT EE 6.0p (the free trial version)

-Java 1.7 , Tomcat 7

-One Accounts ListGrid:

DataSource accountDS = DataSource.get("accounts");
accountGrid = new ListGrid();
accountGrid.setDataFetchMode(FetchMode.BASIC);
accountGrid.setDataSource(accountDS);

-Request Criteria:

criteria.addCriteria("RequestUserID", 123);
criteria.addCriteria("RequestStartDate","2017-01-01");
criteria.addCriteria("RequestEndDate","2017-01-31");
criteria.addCriteria("RequestManagerID",567);

-fetchAccounts response:

{AccountUserID:111,AccountID:1,AccountName:"OneAccount"},
{AccountUserID:222,AccountID:2,AccountName:"TwoAccount"},
{AccountUserID:333,AccountID:3,AccountName:"ThreeAccount"}

-fetchUsers response:

{UserID:111,UserName:"Amy"},
{UserID:222,UserName:"Bob"},
{UserID:333,UserName:"Chris"},
{UserID:444,UserName:"Dave"},
{UserID:555,UserName:"Emily"}

-Account DS (serverType SQL) :

<DataSource ID="accounts" serverType="sql" >

    <operationBindings>
        <binding operationType="fetch" serverMethod="fetchAccounts">
            <serverObject lookupStyle="spring" bean="accountService"/>
        </binding>
        <binding operationType="update" serverMethod="updateAccount">
            <serverObject lookupStyle="spring" bean="accountService"/>
        </binding>
    </operationBindings>

    <fields>

        <field name="AccountID"        type="integer"      hidden="true"   primaryKey="true"   />
        <field name="AccountName"   type="text"   width="100"    />

        <field name="AccountUserID" type="integer" foreignKey="users.UserID" title="Account User"  canEdit="true" width="100"   displayField="UserName"   editorType="SelectItem"    />
            <field includeFrom="users.UserName"  hidden="true"     />

    </fields>
</DataSource>

-Users DS (serverType SQL) :

<DataSource ID="users" serverType="sql">

    <operationBindings>
        <binding operationType="fetch" serverMethod="fetchUsers">
            <serverObject lookupStyle="spring" bean="accountService"/>
        </binding>
    </operationBindings>

    <fields>
        <field name="UserID"        type="integer"   hidden="true"      primaryKey="true"                    />
        <field name="UserName"      type="text"  title="Users DS UserName"  required="true" width="100"         />
    </fields>
    
</DataSource>

The following issues still exist (see screenshots attached) :

1) Users callback not being executed until the record is selected and the SelectItem is activated
--This means that the "UserName" display field has a NULL value, and displays the UserID instead

2) Users callback does not have the necessary criteria from the original Accounts request :
--The criteria included only has the "UserID" of the SelectItem that was selected
--Needs : RequestUserID, RequestStartDate, RequestEndDate, RequestManagerID



Can gladly provide more details if necessary.

Thanks,

[Isomorphic]

This testcase is invalid in multiple ways, partial aside from that, and you are reporting correct behavior as a bug. To explain all this..

1. invalid: you're setting serverType:"sql" but not actually using it; you've implemented the operations via DMI and the framework hasn't actually been given information necessary to do real queries. This is invalid; either really use serverType:"sql" (or "jpa" or "hibernate") or you are instead hand-implementing every operation, then use "generic" as that's the correct setting

2. invalid: no field declarations for fields used as criteria (this is sufficient to explain why they would be dropped)

3. invalid: some criteria which appear to be date-valued are provided as strings instead. Provide dates, and be sure to read the Date and Time Format and Storage overview.

4. partial: we don't have any code for your DMIs or the beans involved, so this is very very far from runnable.

5. non-bug: see formItem.fetchMissingValues: a formItem will fetch display values in situations where the value isn't available. This probably explains your issue with the ID staying visible; look in the RPC tab of the Developer Console and your server logs to see what happened to the request. Your server likely didn't respond as expected, causing this issue.

[jbrau01]

Thank you for your reply:

1) I only switched to "sql" because that was what was recommended to me earlier. I do not expect the framework to do the queries. "generic" has always worked fine for us, until I had to do something like this where two datasources need to be "joined". If I change both datasources to "generic", it begins to throw errors. It does work as expected, however, if I switch the Accounts to "sql" and leave the Users as "generic"

2) The criteria for the Users callback should be the same criteria as for the Accounts callback. The criteria used during the users callback is not supposed to come from the Accounts ListGrid/Datasource.

3) This is not an issue, we transfer date objects as strings without issue.

4) Would you like me to provide the sample project I used to verify this?

5) I see this function, and although it may be helpful, how does it help me configure the Accounts XML datasource? The FormItem (SelectItem) is never instantiated.


Thanks,

[Isomorphic]

1. it's totally invalid to use "sql" in this way - that is, where you don't actually want SQL behavior at all - so remove that setting and start telling us about the errors you said you encountered with "generic" (as that's the correct setting)

2. neither DataSource declares the relevant fields, which is invalid. BTW you are using the term "callback" oddly - it seems like you're using it to mean a call to the server? A "callback" in SmartClient is instead a function that you pass into an API which is then called when some asynchronous operation completes.

3. this remains invalid unless you've declared the fields as type "string" and always use strings for values and string-oriented filtering, and expect no date-related behavior anywhere in the system

4. since it's invalid in several ways, no. We would only want to see it when and if there is a possibility that it would demonstrate a framework bug

5. it's on by default, so you don't need to modify your XML, however, you do need to check that your server is correctly responding to the queries that are automatically sent, as previously explained.

[jbrau01]

1) Like I said, I never intended on using "sql". But I did make progress, however when switching the datasources from "generic" to "sql". If I switch them back to "generic", I am back to square one:





2) Apologies, I will use the term "datasource request" going forward. The Criteria for the accounts datasource request (RequestUserID, RequestStartDate,RequestEndDate,RequestManagerID) , needs to be present in the users datasource request also. None of these values should be coming from the Accounts DataSource/ListGrid

3) As I mentioned before, the strings/dates are not relevant to the issues here. I merely added them to show that the Account request Criteria is not being passed through to the Users datasource request.


5) When you say "correctly responding to the queries that are automatically sent", do you mean that the AccountsService would need to also provide the "UserName" field, in addition to the "UserID" field in the Accounts response? This is what I was hoping to avoid by having the accounts datasource "join" with the users datasource.


Despite what we have discussed, my initial issues have not changed:

1) Using serverType="generic" with includeFrom fields
2) Passing external criteria into the secondary ("joined") datasource request

Thanks,