N:N relations and foreignKey

[csillag]

Dear Isomorphic,

What is the recommended way of implementing N:N bi-directional associations? Is there a way to have automatic GUI for editing such relationships? (I am using the built-in SQL back-end.)

[csillag]

What is the recommended way of implementing N:N bi-directional associations?

By the way, is there a summary somewhere of the exact consequences of setting foreignKey true on a given field in a DS?

Does this imply automatic reference integrity checks?
What about the UI? (What to I have to do so that I can get a picker to chose one of the items in the referenced DS?)

What about addressing data with XPath expressions? Is this only supported with a hibernate backend, or is something like this available for the SQL backend?

(ps1. Sorry for posting more than one question in one post.)
(ps2. I did try to look through the examples and the docs, but I could not find clean answers to these question. Sorry to bother you with such questions.)
(ps3. As a tiny little compensation, I will buy my license in a few days.)

[Isomorphic]

Hi csillag,

We've moved this to it's own thread - can you do so in the future on your own whenever your question is about specific feature usage?

For N:N relationships, this example shows the general pattern. In this case the project teams are not actually realized as a separate entity but the pattern is the same. This is something you could capture in a reusable class for use with any set of DataSources.

There isn't a singular doc discussing all the impacts of foreignKey, although searching for it will turn up the right list of materials.

[csillag]

What about addressing data with XPath expressions? Is this only supported with a hibernate backend, or is something like this available for the SQL backend?

I am still interested to know this.

[Isomorphic]

What would an XPath mean in the context of SQL, a join? Joins are already supported via SQL Templating, see this sample.

[csillag]

What would an XPath mean in the context of SQL, a join? Joins are already supported via SQL Templating, see this sample.

I am not necessarily interested in the underlying mechanism, but probably yes, a join. Even if there is already supported via a different route, it would have been nice if this would work this way, too.

My assumption (or hope) was that DataSources work more or less the same way, regardless of the underlying backend. After reading "Choosing a Data Integration Approach" and "SqlVsJPA" multiple times, it did not occur to me that there are things which DataSources built on the hibernate backend can do by default, but I have to do manually when using the SQL backend.

[Isomorphic]

It wouldn't exactly be correct to say that Hibernate does this "automatically" or that there is anything less capable about the SQL connector. Realize that to get the equivalent of the join you see in that SQL example, with Hibernate you need two bean classes that do not need to exist for SQL, and then you would get a much less efficient join (generally one SQL query per object fetched).

[csillag]

What would an XPath mean in the context of SQL, a join? Joins are already supported via SQL Templating, see this sample.

As it turns out, the Custom SQL feature is only available with the Power and Enterprise Editions, and I have the Pro Edition.

How am I supposed to use a simple Join, then?

[csillag]

How am I supposed to use a simple Join, then?

This seems to be a pretty basic use-case to me; I'm sure there is a recommended approach for Pro users, too. Dear @Isomorphic, could you please point me to the right direction?

Even if the best thing would be to "go Power", there has to be a second best solution, too. Should I just use OptionDataSource, or something else?

Thank you for your help!

[Isomorphic]

If you are trying to avoid buying Power, you can:

1. create views in the database for joins and use those via the SQL connector

or

2. write your own JDBC code in Java for join cases

[csillag]

If you are trying to avoid buying Power, you can:

I wouldn't say I am trying to avoid buying Power; it's just that I would like to have a clearer picture of my possibilities on the Pro level before making this decision.

[csillag]

... Joins are already supported via SQL Templating, see this sample.

How can I implement a join which would use two records from the same table? (Is there a mapping for the SQL "AS" keyword in some clause of OperationBinding?)

[Isomorphic]

Perhaps if you showed the SQL you have in mind, and what you want to be displayed?

[csillag]

Something like

SELECT * FROM definitions def, groups g1, groups g2 WHERE def.GROUP1_ID = g1.GROUP_ID AND def.GROUP2_ID = g2.GROUP_ID;

;

[Isomorphic]

Without Power, same answer as before:

1. create views in the database for joins and use those via the SQL connector

or

2. write your own JDBC code in Java for join cases

With Power, you could either override the entire SQL (<customSQL> tag) or override the <fromClause> to add the additional tables and <whereClause> to add the join criteria (using $defaultWhereClause to retain the default where clause).