Association to multiple attributes of an entity

[helpmesap]

Hello experts,

I'm implementing a message service using the two entities Users and Messages. Given the natural definition of the latter entity,

entity Messages {
    key id: Integer;
    sender: Association to Users;
    recipient: Association to Users;
    subject: String;
    body: String;
    timestamp: Timestamp;
}

the obvious schema for the Users would use an association to Messages on multiple attributes since a user may be either the sender or the recipient of a message. Hence Users may be defined as

entity Users {
    key id: UUID;
    messages: Association to many Messages on messages.sender = $self or messages.recipient = $self;
}

CDS does not flag this disjunction and compiles it just fine. However, it does not work as expected. No received messages can be queried through the user entity's interface. It seems as if the latter part or messages.recipient = $self is ignored, i.e. GET /Users('1000')/messages returns only the messages sent by user #1000 but not those received.

Is there a way in CAP to express this association between entities where multiple attributes are involved? And if that's not possible what would be the canonical way to model this relationship?

Thank you and kind regards
Stan

[hjb]

Hi Stanislaw,

very nice example, thank you for writing such a nice inquiry 🙂

I can't comment on the runtime capabilities, but from a modelling perspective nothing speaks against such an ON condition.

Please read my explanation on managed associations here: https://answers.sap.com/answers/13302664/view.html. This allows you to transform the $self expression into an 'explicit' ON condition. Admittedly, this becomes inconvenient for larger key tuples and you loose automatic recalculation but at least it gets you going:

messages.sender.id = id or messages.recipient.id = id

Your ON condition translates just fine into SQL when used in a static query:

entity Messages {
    key id: Integer;
    sender: Association to Users;
    recipient: Association to Users;
    subject: String;
    body: String;
    timestamp: Timestamp;
}
entity Users {
    key id: UUID;
    messages: Association to many Messages on messages.sender = $self or messages.recipient = $self;
    messages2: Association to many Messages on messages2.sender.id = id or messages2.recipient.id = id;
}

view Vself as select from Users { key id, messages.id as mid };
view Vexplicit as select from Users { key id, messages2.id as m2id };

produces two identical views:

CREATE VIEW Vexplicit AS SELECT
  Users_0.id,
  messages2_1.id AS m2id
FROM (Users AS Users_0 LEFT JOIN Messages AS messages2_1 ON messages2_1.sender_id = Users_0.id OR messages2_1.recipient_id = Users_0.id);

CREATE VIEW Vself AS SELECT
  Users_0.id,
  messages_1.id AS mid
FROM (Users AS Users_0 LEFT JOIN Messages AS messages_1 ON (messages_1.sender_id = Users_0.id) OR (messages_1.recipient_id = Users_0.id));

Regards, Hans-Joachim