[csillag]

[...] override the <fromClause> to add the additional tables and <whereClause> to add the join criteria (using $defaultWhereClause to retain the default where clause).

Could you please show me an example of doing this? The unclear parts are these:
- How do I specify which record is fetched from which table?
- what to add to CustomValueFields and CustomCriteriaFields
- how to address the resulting fields from the DS? (If I need two records from the same table, how do I reference the same field from the two different records?)

[Isomorphic]

You seem to be overcomplicating things bit..

Your SQL join returns a set of rows. Those become the DataSource records. The fields you should declare are the columns of the join.

Make sense?

[csillag]

You seem to be overcomplicating things bit..

Probably true, but irrelevant in this case.

Your SQL join returns a set of rows. Those become the DataSource records. The fields you should declare are the columns of the join.

Make sense?

Generally, yes. In my specific case, no.

If you look at the SQL example I posted above, you can see that I would like to construct DataSource records based on several different rows of the same table (among other things). So, group1 and group2 are different rows in the same table, but I need to construct a single record in my DataSource based on the def record in one table, and the two joined group1 and group2 records from the groups table. Thus, the data in both records (group1 and group2) will be part of the one same resulting DataSource record.

Obviously, originally both record has the same columns, but I need to individually reference them in my datasource.

See my problem now?

[Isomorphic]

No, don't see the problem. You want one Record per row in the result of the SQL query - great, that's the default. As previously indicated, your next step is to declare fields for the DataSource matching the columns of the join. Why not try it?

[csillag]

I still can not do this properly.
Could you please look through the below example?


Let's suppose I have a table of users (called users), with the following columns:

USER_ID, USER_NAME

Let's suppose I have an other tables about messages between users (called messages), with the following columns:

MESSAGE_ID
SENDER_ID
RECEIVER_ID
MESSAGE_TEXT
MESSAGE_DATE

Now let's suppose would like a ListGrid to edit the lists the messages.
Naturally, I would like to resolve both the SENDER_ID and RECEIVER_ID
fields to the names of the relevant users, using the users table.
(Like in the "large value map" example.)

So in the DS XML, I write this:

....

<field name="SENDER_ID" foreignKey="users.USER_ID" />
<field name="sender_USER_NAME" canEdit="false" customSQL="true" tableName="sender" title="Sender" type="text"/>
<field name="RECEIVER_ID" foreignKey="users.USER_ID" />
<field name="receiver_USER_NAME" canEdit="false" customSQL="true" tableName="receiver" title="Receiver" type="text"/>
....

<operationBindings>
<operationBinding customCriteriaFields="" customValueFields="" operationType="fetch">
<selectClause>sender.USER_NAME as sender_USER_NAME, receiver.USER_NAME as receiver_USER_NAME, $defaultSelectClause</selectClause>
<tableClause>users receiver, users sender, messages</tableClause>
<whereClause>messages.SENDER_ID = sender.USER_ID AND messages.RECEIVER_ID = receiver.USER_ID AND ($defaultWhereClause)</whereClause>
</operationBinding>
</operationBindings>


And in the code, I do this with my ListGrid:

I hide fields "sender_USER_name" and "receiver_USER_NAME"

on the ListGridField "SENDER_ID":

setEditorType(new SelectItem());
setDisplayField("sender_USER_NAME")

.

With this, I can get a good static display of values, with names instead of IDs.
However, if I want to edit the SENDER_ID field, the names revert back to IDs,
because the ListGrid can not query the data from the users field, because
the given field name (sender_USER_NAME) does not work for that table.

If I want to have proper editing I have to say

setDisplayField("USER_NAME").

If I do this, I get proper editing (combo box with names), but then the static display is wrong,
because it can not find the field "USER_NAME", since it's called sender_USER_NAME.

So, I can not get correct static and edit modes at the same time.

This is caused by the fact that I had to rename the field from USER_NAME to sender_USER_NAME (and receiver_USER_NAME),
but I guess I must do this, because I can not have two fields with the same name, right?

So, could you please tell me how to do this properly?
Thank you very much in advance!

[Isomorphic]

See the docs for ListGridField.optionDataSource - it addresses this scenario. In addition to your displayField setting on the listGridField, you want an optionDataSource on the SelectItem (but *not* an optionDataSource on the listGridField).

[csillag]

I seem to remember the docs saying optionDataSource is only adequate for small data sets. Is this not relevant here?

[Isomorphic]

This is true of ListGridField.optionDataSource specifically. It is not true of SelectItem / ComboBoxItem optionDataSource. Again, see the docs for ListGridField.optionDataSource.