How to get data field from another table?

[liudi]

hi,

after a bit readings and browsing the showcase, it's saying that if you want to retrieve values from another table other than the default one, use 'tableName' to indicate the value is from that table.

i m trying to use a ListGrid to display values from 2 tables, there's a 'parent_carrier' field in the "order" table, which is a foreign key from the 'carrier' table, pointing to the carrier's primary key. The .ds.xml file is like this:

<fields>
......
		<field name="service_level" type="text" length="255" required="true" title="Service Level"></field>
		<field name="service_type" type="text" length="255" required="true" title="Service Type"></field>
		<field name="parent_carrier" type="number" title="Carrier" hidden="true" foreignKey="carrier.pk_carrier_id"></field>
		<field name="short_name" type="text" tableName="transact_carrier" title="Carrier"></field>
</fields>

then i have a Operation Binding:

<operationBinding operationType="fetch">
			<tableClause>
				order, carrier
			</tableClause>  
			<whereClause>
				order.parent_carrier = carrier.pk_carrier_id 
				AND ($defaultWhereClause)
			</whereClause>
</operationBinding>

in which 'short_name' is one field of table 'carrier'

i expect to see all 'order' table fields, and carrier short_name in the same ListGrid, but i got this exception:

Unknown column '[b]carrier.short_name[/b]' in '[b]field lis[/b]t'

If i add customSQL='true', and change the operationBinding to: (the Java Doc says this is not a good way for simple table joining)

		<operationBinding operationType="fetch" customValueFields="short_name" customCriteriaFields="short_name">
			<tableClause>
				order, carrier
			</tableClause>  
			<whereClause>
				order.parent_carrier = carrier.pk_carrier_id 
				AND ($defaultWhereClause)
			</whereClause>
		</operationBinding>

and the ListGrid calls the default fetch, it works, but if i call a customized 'fetch' the field 'short_name' doesn't get any value.

Apart from the exception, i also have some questions.

1. if there are more than one field added from other tables, how do these fields use the right <operationBinding> to retrieve the values?

2. when use customSQL='true', the docs says " indicates that this field should be omitted by default from all SQL or Hibernate operations, and will only be used with custom queries.", does this mean i have to use <customSQL> to enable the added fields?

3. When i user 'tableName' or 'customSQL=true' in the 'field' tag, will the table join be applied every time i call an customized operation?

4. Is it possible to show ComboBoxItem/SelectItem which pointing to the foreign table? This would be fantastic for record editing functions.

Thanks!

[Isomorphic]

It looks like you're jumping in without reading the QuickStart Guide sections that cover the intended usage in detail - start there for an overview.

On your last question - yes, and in fact that's what automatically used when you declare DataSourceField.foreignKey.

[liudi]

yeah, i have to admit you are right, i have just briefly read the quick start guide, but obviously im getting too quick....

[liudi]

I got it. i think i just misunderstood the way how it works. I thought if i define a 'tableName' in field tag, the field would be automatically loaded when querying on the primary table. That's why i had the question 'how does the datasource know the link between the current table and the another table'.

so silly : p