Developer Forum

Dear community,

I guess I have a rather complicated problem, and I hope you can at least
understand it:

I have a database with some kind of tree-like master-detail relations. And
for security reasons, most database user have access not to the base tables
directly, but only to a view which combines the tree via joins to a flat
table, like that (just for illustration):

root_id | branch_id | leaf_id
1 | 1.1 | 1.1.1
1 | 1.1 | 1.1.2
1 | 1.2 | 1.2.1
1 | 1.2 | 1.2.2
2 | 2.1 | 2.1.1
2 | 2.1 | 2.1.2
2 | 2.2 | 2.2.1
2 | 2.2 | 2.2.2

I would like to use only one component (e.g., a SimpleDataSet, or maybe an
SQLDataSet, preferably in "ctTable" mode) and a DataSource as one-shot
connection to the view, and several ClientDataSets with DataSetProviders and
DataSources as connection to the data-aware components to display each tree
level (and its corresponding information, which is not shown in the
illustration above, but provided in additional view fields).

My success so far: I was able to use a SimpleDataSet as data providing
connection (as "DataSet" property for the DataSetProviders), and to cascade
the ClientDataSets to establish the tree structure (DataSource_Root as
"MasterSource" for the ClientDataSet_Branch on root_id as "MasterField",
DataSource_Branch as "MasterSource" for the ClientDataSet_Leaf on branch_id
as "MasterField").

My problem here: ClientDataSet_Root provides as many datasets as there are
distinct leaf_id's, but I would instead like to rather have only as many
datasets as there are distinct root_id's; similar problem for
ClientDataSet_Branch with multiple branch_id's (as many datasets, as there
are distinct leaf_id's per root_id).

Is it possible at all to have only distinct datasets in ClientDataSets (each
of them containing only a subrange of fields in the "Field editor", by the
way)? That would be great, because it could cache a lot of database queries
that way; if not, I would have to query the database with a "SELECT
DISTINCT" statement and parameters, each time the user selects a different
dataset in each tree level...