ManyToOne on column that is not unique-CodePudding

I have one table Message in which I keep both incoming and outgoing messages. A column "category" is kept to differentiate, that can have 1 = INCOMING, 2 = OUTGOING. Outgoing messages can have attachments. The following is my Message entity class:

@Entity("MESSAGE")
class Message{
  Long id; //primary key
  String additionalStringId;
  private Long senderId;
  private Long receiverId;
  Category messageCategory;
  @OneToMany(
            mappedBy = "message",
            cascade = CascadeType.ALL,
            orphanRemoval = true)
  private List<AttachmentEntity> outgoingAttachments= new ArrayList<>();
}

The problem is that AttachmentEntity is linked with the message via the "additionalStringId" and I can not impact the database schema.

class AttachmentEntity{
  @ManyToOne
  @JoinColumn(name = "additionalStringId", referencedColumnName = "additionalStringId")
  private Message message;
}

I know it's not working snipping code but my problem appears when sender = receiver, when I try to send a message to myself. In that case in my database I will have two messages, with the same additionalStringId but with category = 1 and the other with category = 2. My attachment entity list tries to link to message but two messages are visible. What can I do to fix this problem?

I tried separating the two categories into separate entities with @Where and @DiscriminatorFormula and @DiscriminatorValue but I could not get it to work. What can I do?

Table DDLs:

CREATE TABLE "MESSAGE" 
   (    "ID" NUMBER NOT NULL ENABLE, 
    "MESSAGE_ID" VARCHAR2(60) NOT NULL ENABLE,  
    "CATEGORY" NUMBER, 
    "SENDER_ID" NUMBER, 
    "RECEIVER_ID" NUMBER    )

CREATE TABLE "OUTBOX_ATTACHMENT" 
   (    "ID" NUMBER NOT NULL ENABLE, 
    "MESSAGE_ID" VARCHAR2(60) NOT NULL ENABLE, 
    )

CodePudding user response：

@ManyToOne(cascade = CascadeType.PERSIST)
@JoinColumn(name = "additionalStringId", referencedColumnName = "id", nullable = false)
private Message message;


@OneToMany(mappedBy = "message", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
private List<AttachmentEntity> outgoingAttachments = new ArrayList<>();

something like this might work.

CodePudding user response：

I think the problem you are facing is not at the JPA level, but at the table definition level (DDL).

If you change the table definition as shown below, the FK relationship becomes easy to establish:

CREATE TABLE "MESSAGE" 
   (    "ID" NUMBER NOT NULL, 
    "MESSAGE_ID" VARCHAR2(60) NOT NULL,  
    "CATEGORY" NUMBER, 
    "SENDER_ID" NUMBER, 
    "RECEIVER_ID" NUMBER,
    constraint uq1 unique (category, message_id)
   );

CREATE TABLE "OUTBOX_ATTACHMENT" 
   (    "ID" NUMBER NOT NULL , 
    category number not null constraint chk1 check (category = 2),
    "MESSAGE_ID" VARCHAR2(60) NOT NULL ,
    constraint fk1 foreign key (category, message_id) 
      references message (category, message_id)
   );

As you can see the FK includes two columns: category, and message_id. But, as per requirements, in the attachement table category can only accept attachment to outgoing messages (value 2).