in EE: HowTo: many-to-many relationship, settable via drag'n'drop ?

[Kah0ona]

Hello,

For some reason I cannot figure out how to do the following.
Consider the scenario where there is a grid of Persons, and persons can be assigned Hobbies. However, multiple persons can share the same hobby, it is many-to-many.

I am using SmartGWT Pro/EE and using MySQL as a database.

I want the solution to look like this:
In the detailed view of One Person, there is a grid listing all the hobbies (Hobbies can be created in a different tab, so there's a read-only list here).

Beneath that, there is a grid, where hobbies can be dragged into. This way, one could assign hobbies to a person.

I worked it out partially, but not completely yet.

I think that in the classical way you would have tables like this:

Persons(id,name)
Hobbies(id,name)
Person2Hobby(person_id,hobby_id).

Thus, I thought it would make sense to have 3 .ds.xml files:
Person:

<DataSource ID="Persons" serverType="sql" tableName="Persons"> 
  <fields> 
    <field name="id" type="sequence" hidden="true" primaryKey="true"/>  
    <field name="name" title="Name" type="text" length="128" /> 
 </fields>
</DataSource>

Hobby:

<DataSource ID="Hobbies" serverType="sql" tableName="Hobbies"> 
  <fields> 
    <field name="id" type="sequence" hidden="true" primaryKey="true"/>  
    <field name="name" title="Name" type="text" length="128" /> 
 </fields>
</DataSource>

Person2Hobby:

<DataSource ID="Person2Hobby" serverType="sql" tableName="Person2Hobby"> 
  <fields> 
    <field name="id" type="sequence" primaryKey="true" hidden="true"/>  
    <field name="person_id" type="integer" foreignKey="person.id"/>  
    <field name="hobby_id" type="integer" foreignKey="hobby.id"/>  
    <field name="name" type="text" tableName="Hobbies" customSQL="true"/>  
  </fields>  
  <operationBindings> 
    <operationBinding operationType="fetch" customValueFields="name" customCriteriaFields="name"> 
      <tableClause>Person2Hobby, Hobbies</tableClause>  
      <whereClause>Hobbies.id = Person2Hobby.hobby_id AND ($defaultWhereClause)</whereClause> 
    </operationBinding> 
  </operationBindings> 
</DataSource>

Firstly, is this the correct way? This avoids having redundant data in the Hobby2Model table by specifying the table name.

Now, when implementing the drag'n'drop, 3 things I cannot work out, maybe it's simple but I just don't see it :-).

1) How to make sure the person_id also gets set in Person2Hobby table? Now it is NULL. If i drop a hobby in the grid, the hobby_id IS set.

2) How to make sure that if I delete a selected hobby for a specific person (ie. person X stopped having 'volleyball' as a hobby), the hobby is not deleted from the Hobby table ? (this happens now).

3) If i delete a hobby altogether, how to update all the persons who have this hobby for not having this hobby anymore? I normally would do a foreign key in the database with ON DELETE CASCADE, is this settable in the .ds.xml? Or is this a 'manual sql process'?

Should I create operation-bindings for the other CRUD operations in this case? I actually don't really get why that should be there, can't it be inferred from the foreignKey statements in the Hobby2Model table?

Help appreciated! Since it is a pretty common use-case, I reckon there is a not too difficult solution for it (ie. not having to write complicated server-code, but let SmartGWT do the job?)

[Isomorphic]

You've got the DataSources right, now most of your issues have to do with how you structured the UI (which you didn't show). This is the most nearly analogous example, look at it in depth.

On cascading deletion - you could either do this in SQL, or add it as DMI logic that, after the initial deletion occurs, creates a new DSRequest() to do the cascaded delete.

[Kah0ona]

thanks for the quick reply. I will try this first thing in the morning (GMT+1 here). This example helps.

Why does this other example (http://www.smartclient.com/smartgwtee/showcase/#sql_user_specific_data) use the operation binding xml settings if the foreignKey is automatically interpreted?

I don't get the purpose of this operation binding then...

[Isomorphic]

What operationBinding settings are you referring to? If you mean the <tableClause>, joins are never automatically performed based on foreignKey. Setting foreignKey does, however, cause a bunch of intelligence about how values are put together for saving (eg, what happens on drop and drop). See eg the docs for RecordDrop.

[Kah0ona]

Yes I meant the table clause, and the where clause, that specifies a join.

So, in my case: do I need it?

Especially I wonder how to make sure the person_id is also stored in the Person2Model table, since it is not in the dragged item: one drags a hobby record into this join grid. The hobby record does not contain the person_id obviously.

So how to also fetch the person_id (which is the id of the record currently shown?)

Is this where the operationBinding will help me out, and where I have to somehow make a join? As far as I can see this is where a bit of the diference lies with the example, correct? Because in the drag/drop case of the example you gave the dragged_from grid has the info it needs, that it, it has the employeeID info.

[Isomorphic]

You seem to be referring to a UI where we have neither the code nor a screenshot, so we can't really tell what you're asking about.

[Kah0ona]

Fair enough. Examine the issue I got with the code below. In the Person2Model table, the person_id is not stored (in Person2Hobby table, it's now set to NULL). What should be changed to make this happen? I tried to trim down this example, reducing number of columns (which are in Dutch anyway) for clarity.

This would match the .ds.xml datasource file described above.

public VLayout getPersonHobbyConnector()
{
VLayout ret = new VLayout();

// all elements from the target reference, to be picked from to make a 
//connection
ListGrid hobbyGrid = new ListGrid();
ret.setWidth100();
ret.setHeight(200);

hobbyGrid.setCanEdit(false);
hobbyGrid.setAlternateRecordStyles(true);
hobbyGrid.setCellHeight(22);
hobbyGrid.setAutoFetchData(true);
hobbyGrid.setShowFilterEditor(true);
hobbyGrid.setCanRemoveRecords(false);
hobbyGrid.setCanDragRecordsOut(true);
hobbyGrid.setDragDataAction(DragDataAction.COPY);

DataSource d = DataSource.get("Hobbies");
hobbyGrid.setDataSource(d);

ListGridField nameField = new ListGridField("name","name");

hobbyGrid.setFields(nameField);

//show a grid where everything can be dropped in. This way, we can connect a hobby to a person. This grid shows the hobbies belonging to this person.

ListGrid refGrid = new ListGrid();

refGrid.setWidth100();
refGrid.setHeight(200);
refGrid.setCanEdit(false);
refGrid.setAlternateRecordStyles(true);
refGrid.setCellHeight(22);
DataSource r = DataSource.get("Person2Hobby);
refGrid.setDataSource(r);
refGrid.setAutoFetchData(true);
refGrid.setShowFilterEditor(false);
//!! should not remove the hobby all together, only the connection with this person
refGrid.setCanRemoveRecords(true); 
refGrid.setCanAcceptDroppedRecords(true);

ListGridField nameRefField = new ListGridField("name", "name");

refGrid.setFields(nameRefField);

ret.addMember(coupledGrid);
Label l = new Label();

l.setContents("Drag a Hobby to the grid below to connect a Hobby to a Person");
ret.addMember(l);
ret.addMember(refGrid);

return ret;
}

[Kah0ona]

Any chance of helping me out here? Would be greatly appreciated!

[Isomorphic]

This is basically exactly like the sample. The missing piece is that you should call fetchData() on the grid connected to the Person2Hobby table, passing the current personId.

This is both the right way to load data, and provides a clue to the drag and drop system that when a hobby is dropped on this grid, the personId can be taken from the current criteria (the grid understands that you want the dropped data to "conform to" the grid's current criteria).

[Kah0ona]

awesome. thanks for the support guys!

[Kah0ona]

Okay, so almost there. In the grid connected to Person2Model, the fetchData() call with the criteria added (see below), now fetches only hobbies belonging to this person, which was intended. That is: if I manually set the person_id in the database, because, when dropping something in the grid connected to Person2Model, the Person_id is not saved (it's NULL)

I implemented it as follows:

DataSource r = DataSource.get("Person2Hobby");
refGrid.setDataSource(r);
refGrid.setAutoFetchData(true);
Criteria c = new Criteria();
c.addCriteria("Person_id", form.getValueAsString("id")); //form holds the current selected Person (it has Person datasource connected to it)
refGrid.fetchData(c);

How should I modify the above bit (or the Person2Hobby.ds.xml ?) to actually add the 'clue' to the drag'n'drop mechanism that Person_id should be fetched from the DynamicForm 'form', and this Person_id should be inserted in the database table Person2Hobby?

[Isomorphic]

It's automatic, but it looks like your FK definition is wrong:

    <field name="person_id" type="integer" foreignKey="person.id"/>

The actual ID of the DataSource is "Persons" so that should be "Persons.id".

[Kah0ona]

Hm, in fact, this error was caused by the trimming down of the example, in my system such relations are alright (i'm generating the code in fact).

Okay so, it should be automatically working. Then that is strange.


Well, at least I know that something like this must be wrong. But what I do wonder is this: how does it know i want to put the current person_id in there? How does it know what is 'current'? Is it because of the criteria like below?

Criteria c = new Criteria();
c.addCriteria("Person_id", form.getValueAsString("id")); //form holds the current selected Person (it has Person datasource connected to it)

[Isomorphic]

Yes, it's because of the criteria. It knows you want to create a new record that can be displayed in the current grid. So it has to have a person_id value that conforms to the current criteria or else it would be immediately eliminated by the criteria.

[Kah0ona]

Okay then. Then it means I have hard times finding why it does not work...

What is the correct way of pulling the id out of a DynamicForm?

form.getValueAsString("id")

?

Also, the id of person is specified in Persons.ds.xml like so:

<field name="id" type="sequence" hidden="true" primaryKey="true"/>

Or what else could be wrong to look out for?