Many-to-many link table with additional columns

Hi there,

Is there a sample project using many-to-many link table with additional columns?

I need to implement this feature with editable table on Browse screen. If you can help with ideas would be great.

Thank you!

Click tutorial :slight_smile:

https://www.cuba-platform.com/tutorials/displaying-relationships-between-entities-part-2

Hello, does not really help in case there is many-to-many link table with additional fields.

Hi,

What is the problem, exactly?
I’ve created a small project to demonstrate the simplest example of a many-to-many link table with additional columns. If it’s not your case, please specify your question.

demo.zip (92.7 KB)

Also, you may be interested in this example, too: How do I display One-To-Many and Many-To-Many attribute by strategy?
It contains quite an interesting example of many-to-many relationship.

Hi @shiryaeva!

Thank you for the answer. I cannot open the “demo” project: “Version of Platform used in project (6.9.0.BETA3) not supported.”

I am currently using 6.8.2. Please advise!

Regards!

Sorry, re-zipped with 6.8.2 demo.zip (91.6 KB)

Hi @shiryaeva!

I will give explanation to the problem I do face with “demo” example you provided.

What I need to do is to put in “Group Editor” screen a table with list of all Customers in this group. In this table “additional field” should be shown as well.
I should be able to add more Customers from this screen and edit their “additional field” from here.

I tried many times but still cannot find a solution. I would appreciate if you can help!

Thank you in advance!

Hi,

You can add an extra datasource to your Group editor and a Customers table bound to it, for example:

<collectionDatasource id="customerGroupsDs"
                      class="com.company.demo.entity.CustomerGroup"
                      view="customerGroup-view">
    <query>
        <![CDATA[select e from demo$CustomerGroup e
        where e.group.id = :ds$groupDs]]>
    </query>
</collectionDatasource>
<table id="customersTable"
       width="100%">
    <actions>
        <action id="create"/>
        <action id="edit"/>
        <action id="remove"/>
    </actions>
    <columns>
        <column id="additionalField"/>
        <column id="customer.name"/>
    </columns>
    <rows datasource="customerGroupsDs"/>
    <buttonsPanel id="buttonsPanel">
        <button id="createButton"
                action="customersTable.create"/>
        <button id="editButton"
                action="customersTable.edit"/>
        <button id="removeButton"
                action="customersTable.remove"/>
    </buttonsPanel>
</table>

This will enable you to add new Customers through adding CustomerGroups.

Hi again,

There is a better solution for your use case:
Add composition attributes that will reference your link table and create a custom action that will enable to select a Customer and create a new link (CustomerGroup entity) in your Group editor.

Data model

  1. Customer
@Table(name = "DEMO_CUSTOMER")
@Entity(name = "demo$Customer")
public class Customer extends StandardEntity {

    @Composition
    @OnDelete(DeletePolicy.CASCADE)
    @OneToMany(mappedBy = "customer")
    protected List<CustomerGroup> customerGroups;
    
}
  1. Group
@Table(name = "DEMO_GROUP")
@Entity(name = "demo$Group")
public class Group extends StandardEntity {

    @Composition
    @OnDelete(DeletePolicy.CASCADE)
    @OneToMany(mappedBy = "group")
    protected List<CustomerGroup> customerGroups;

}
  1. CustomerGroup
@Table(name = "DEMO_CUSTOMER_GROUP")
@Entity(name = "demo$CustomerGroup")
public class CustomerGroup extends StandardEntity {

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "CUSTOMER_ID")
    protected Customer customer;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "GROUP_ID")
    protected Group group;

    @Column(name = "ADDITIONAL_FIELD")
    protected String additionalField;

}

group-edit.xml:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<window xmlns="http://schemas.haulmont.com/cuba/window.xsd"
        caption="msg://editorCaption"
        class="com.company.demo.web.group.GroupEdit"
        datasource="groupDs"
        focusComponent="fieldGroup"
        messagesPack="com.company.demo.web.group">
    <dsContext>
        <datasource id="groupDs"
                    class="com.company.demo.entity.Group"
                    view="group-view">
            <collectionDatasource id="customerGroupsDs"
                                  property="customerGroups">
            </collectionDatasource>
        </datasource>
    </dsContext>
    <dialogMode height="600"
                width="800"/>
    <layout expand="windowActions"
            spacing="true">
        <fieldGroup id="fieldGroup"
                    datasource="groupDs">
            <column width="250px">
                <field property="name"/>
            </column>
        </fieldGroup>
        <groupBox id="customerGroupsBox"
                  caption="msg://com.company.demo.entity/Group.customerGroups">
            <table id="customerGroupsTable"
                   height="200px"
                   width="100%" editable="true">
                <actions>
                    <action id="addCustomer" invoke="addCustomer"/>
                    <action id="create"/>
                    <action id="edit"/>
                    <action id="remove"/>
                </actions>
                <columns>
                    <column id="customer"/>
                    <column id="additionalField" editable="true"/>
                </columns>
                <rows datasource="customerGroupsDs"/>
                <buttonsPanel>
                    <button action="customerGroupsTable.addCustomer" caption="Add customer"/>
                    <button action="customerGroupsTable.create"/>
                    <button action="customerGroupsTable.edit"/>
                    <button action="customerGroupsTable.remove"/>
                </buttonsPanel>
            </table>
        </groupBox>
        <frame id="windowActions"
               screen="editWindowActions"/>
    </layout>
</window>

GroupEdit.java:

public class GroupEdit extends AbstractEditor<Group> {

    @Inject
    private Datasource<Group> groupDs;
    @Inject
    private CollectionDatasource<CustomerGroup, UUID> customerGroupsDs;
    @Inject
    private Metadata metadata;

    public void addCustomer() {
        openLookup(Customer.class, items -> {
            if (!items.isEmpty()) {
                Customer customer = (Customer) items.iterator().next();
                CustomerGroup newLink = metadata.create(CustomerGroup.class);
                newLink.setCustomer(customer);
                newLink.setGroup(groupDs.getItem());
                customerGroupsDs.addItem(newLink);
            }
        }, WindowManager.OpenType.DIALOG);

    }
}

You can try this approach in the sample attached:

demo-with-composition.zip (93.5 KB)

1 Like

Thanks for the help, @shiryaeva!