DataSource includeFrom

[edulid]

I want to use the "includeFrom" in a DataSource definition but I have some problems.

I have two Tables: "t_users", and "t_notes".

The t_notes table has 2 rows which save the creator and modifier user, and these fields are foreign keys of the t_notes table: f_username_created and f_username_last_change

notes:

<DataSource ID="notizen" serverType="sql" tableName="t_notizen">
	<fields>
		<field name="f_id" type="sequence" primaryKey="true" hidden="true" />
		<field name="f_notiz" type="text" required="true" />
		<field name="f_date_created" type="creatorTimestamp" />
		<field name="f_username_created" type="creator" foreignKey="users.f_username"/>
		<field name="f_date_last_change" type="modifierTimestamp" />
		<field name="f_username_last_change" type="modifier" foreignKey="users.f_username" />
		
               <field name="fullname_created" type="text"
			includeFrom="users.fullname" />
		<field name="fullname_last_change" type="text"
			includeFrom="users.fullname" />
	</fields>


</DataSource>

In the users DataSource, I have a DERIVED field "fullname":

<DataSource ID="users" serverType="sql" tableName="t_users">
	<fields>
		<field name="f_username" type="text" primaryKey="true" hidden="true" />
		<field name="f_name" type="text" />
		<field name="f_vorname" type="text" />
		<field name="f_system_user" type="boolean" sqlStorageStrategy="number" />
		
		<field name="fullname" type="text" customSelectExpression="CASE WHEN f_vorname IS NOT NULL  THEN f_vorname + ' ' ELSE '' END    + CASE WHEN f_name IS NOT NULL THEN f_name ELSE '' END " />
	</fields>

</DataSource>

But I cannot include the "fullname" field for two reasons:
1) the field "fullname" ist a DERIVED field, so it doesn't exist in the real table.
2) I cannot distinguish the two "includeFrom" fields from each other:

<field name="fullname_created" type="text"
			includeFrom="users.fullname" />
		<field name="fullname_last_change" type="text"
			includeFrom="users.fullname" />

How can I use an includeFrom with derived fields, and distinguishing more than one "includeFrom" field from each other?

I am using SmartGWT 3.0p EE.

[Isomorphic]

includeFrom is for simple 1-1 and 1-many joins, this is a use case for SQL Templating.

[edulid]

Yes, that was my first try:

<operationBindings>
		<operationBinding operationType="fetch">
			<tableClause>
				t_notizen

				left join t_users as user_erstellt
				on
				user_erstellt.f_username =
				t_notizen.f_username_created

				left join
				t_users as user_last_change
				on user_last_change.f_username =
				t_notizen.f_username_last_change

			</tableClause>
		</operationBinding>
	</operationBindings>

But when trying to declare the derived fields, there is a problem, since in the
"fullname" only exists in the DataSource , but not in the table.

<field name="name_created" type="text" customSelectExpression="user_erstellt.fullname" />
<field name="name_last_change" type="text"
	customSelectExpression="user_last_change.fullname" />

What am I doing wrong?

[Isomorphic]

Sorry, we're not quite following. When you use this SQL templating approach, what problem do you encounter? Can you show the complete log of what happens?

[edulid]

Well, I have a "users" DataSource with a derived field "fullname". This field does NOT exist on the table

<DataSource ID="users" serverType="sql" tableName="t_users">
	<fields>
		<field name="f_username" type="text" primaryKey="true" hidden="true" />
		<field name="f_name" type="text" />
		<field name="f_vorname" type="text" />
		<field name="f_system_user" type="boolean" sqlStorageStrategy="number" />
		
		<field name="fullname" type="text" customSelectExpression="CASE WHEN f_vorname IS NOT NULL  THEN f_vorname + ' ' ELSE '' END    + CASE WHEN f_name IS NOT NULL THEN f_name ELSE '' END " />
	</fields>

</DataSource>

Then I have another datasource "notizen", where I would like to use the derived field "fullname" from the previous datasource "users", but I don't know how:

<field name="name_created" type="text" customSelectExpression="user_erstellt.fullname" />

...

<operationBindings>
		<operationBinding operationType="fetch">
			<tableClause>
				t_notizen

				left join t_users as user_erstellt
				on
				user_erstellt.f_username =
				t_notizen.f_username_created

				left join
				t_users as user_last_change
				on user_last_change.f_username =
				t_notizen.f_username_last_change

			</tableClause>
		</operationBinding>
	</operationBindings>

As you see, in the datasource "notizen" I have customSelectExpression="user_erstellt.fullname, so I am trying to use the "fullname" field from the datasource "users". But "user_erstellt" is actually the table t_users (because of left join t_users as user_erstellt), and the field "fullname" doesn't exist in "t_users", since it only exists in the datasource "users".

So the error I am getting is that the field "fullname" does not exist in the table "t_users", but that is correct, since the field "fullname" only exists in the datasource "users" (derived field).
What I don't know is how to include this derived field in the datasource "notizen".

[Isomorphic]

"customSelectExpression" is literal SQL going directly to the database, it cannot use a name of a DataSourceField ("fullname"), the database doesn't know about this definition.

To do this kind of join you will just need to duplicate your customSelectExpression ("CASE ...") on the name_created and name_last_changed fields, qualifying the column name using your table aliases (user_erstellt, user_last_change).

There are of course a couple of other approaches (such as using a database view) but the above is the most straightforward, even if it duplicates a small amount of SQL.