Many to many with aditional field

Hello Cuba team.

My case is:
Two tables linked by many to many relationship,
but with an additional field in the help table,
that the user needs to fill out from the interface.

User entity, has a List of help table:

@Composition
@OneToMany(mappedBy="user")
protected List <RoomUser> userRooms;

Room entity, has a List of help table too:

@Composition
@OneToMany(mappedBy="room")
protected List <RoomUser> roomUsers;

The help table, RoomUser has:

@EmbeddedId
protected RoomUserAssociationPK id;

@JoinColumn(name = "room_id")
@ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
@MapsId("roomId")
private Room room;

@JoinColumn(name = "user_id")
@ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
@MapsId("userId")
private User user;

@Column(name = "sequence")
private Integer sequence;

In @Embeddable class RoomUserAssociationPK has:

@MetaClass(name = "app$RoomUserAssociationPK")
@Embeddable
public class RoomUserAssociationPK extends EmbeddableEntity {

private static final long serialVersionUID = 8138145888925579602L;

@Column(name = "room_id")
private Long roomId;

@Column(name = "user_id")
private Long userId;
	
}

This is the basic structure.

My first question, is the written here is right about this case?
Do I miss something important?

Now.
The Room edit view, has a list and table of help table RoomUser,
which has user Id and sequence field, which User needs to fill.
The sequence field will be editable column in the table.
The roomUserTable has an Add button, which is overrided and redirect to the user browser view
to choose one user:

public void onAddButtonClick() {
        roomUsersTableAdd = new AddAction(roomUsersTable);
        roomUsersTableAdd.setWindowId("app$User.browse");
        roomUsersTable.addAction(roomUsersTableAdd);
 }

And now, my logic is to choose a user id and be populate in the help table roomUserTable back into
room edit view, but a room id to be the current room. The sequence field will be filled by the user.
Then save it in the database.
And the same thing with user edit view, and his list of roomUsers.

My problem is when I choose a user in user browser and I need to go back to the room edit view and help table, which be filling in the required information.

Does the logic seem right to you? And how to implement it?
Or there is a sample project with this functionality?

Thank you.

Hi,

The task you described makes perfect sense, and your solution with link entity is fine, except the composite PK that looks a bit as an overengineering to me. A similar model User - UserRole - Role in the platform security uses entities with simple keys.
However, I don’t quite understand what works and what doesn’t. Could you make a small test project with the data model and UI to the point that works for you?

Hi Konstantin,

My first mistake was that I did not take a look at systemic user_role examples.
Yes, the composite id in this case is overengineering. Although in the JPA examples, composite id is one of the solutions.

This example works, but when the link entity inherits StandardEntity. But I need my link entity’s Id to be Integer or Long and do not has other column. I tried to inherit:

BaseLongIdEntity,
BaseIntIdentityIdEntity,
BaseGenericIdEntity<Integer>,
BaseIntIdentityIdEntity,

but in this case it does not work.

The error is:
“PSQLException: ERROR: null value in column “id” violates not-null constraint
Detail: Failing row contains (2, 26, 4, null).”

In this case, the Cube does not find the link entity’s Id for persisting.
What is the solution in this case?

Thank you.

If you take BaseLongIdEntity, the framework will assign ID to the new instances of your entity if you create it using metadata.create(YourEntity.class) method. Please check the code creating this entity.

Thank you very much!

In postInit() I put this line of code and works withBaseLongIdEntity.

   @Override
    protected void postInit() {
        metadata.create(RoomUser.class);
    }

And for colection data sorce I use IdProxy:

   @Inject
    private CollectionDatasource<RoomUser, IdProxy<Integer>> roomUsersDs;

But now, I need the id to be @GeneratedValue(strategy = GenerationType.IDENTITY).
I replace parent class with BaseIntIdentityIdEntity and nothing else, but the same error is coming.
Any suggestions?

Could you provide a test project demonstrating your data model and code?

Hello, Konstantine

I found the error. I had to manually replace the current id to the type serial.
Now it works correctly.

Thank you very much for your time.

I’m sending you an example project.
I would like to know your opinion if there is anything in the code that I could optimize or remove.

Thank you very much.