How make a polymorphic query with Hibernate JoinFormula Annotation?

I try to find the best way to implement a calculated property witch query the latest message into the chat entity.

The Message entity is implemented as a class hierarchy according to the type of the message (Image, Text, Url ...)

@Entity
@Table(name = MessageEntity.TABLE_NAME)
@Inheritance(strategy = InheritanceType.JOINED)
@DiscriminatorColumn(name = "Type", discriminatorType = DiscriminatorType.STRING)
@NamedEntityGraphs({
    @NamedEntityGraph(name = MessageEntity.GRAPH_MESSAGES_FULL, attributeNodes = {
        @NamedAttributeNode(value = "creator", subgraph = UserEntity.GRAPH_USER_BASIC),
        @NamedAttributeNode(value = "chat")
    })
})
@Getter
@Setter
public abstract class MessageEntity extends AbstractEntity

MessageImageEntity example

@Entity
@Table(name = MessageImageEntity.TABLE_NAME)
@DiscriminatorValue("IMAGE")
@Data
@Builder
@EqualsAndHashCode(callSuper = false)
@AllArgsConstructor
@NoArgsConstructor
public class MessageImageEntity extends MessageEntity

Therefore I have implemented the follow JoinFormula property for get last message into the Chat but the Message Entity that was get does not belong to a specific implementation

@ManyToOne(fetch = FetchType.LAZY)
    @JoinFormula("("
            + "SELECT M.Messages_id "
            + "FROM Messages M "
            + "LEFT OUTER JOIN Messages_Image MI ON M.Messages_id = MI.Messages_id "
            + "LEFT OUTER JOIN Messages_Text MT ON M.Messages_id = MT.Messages_id  "
            + "LEFT OUTER JOIN Messages_Url MU ON M.Messages_id = MU.Messages_id  "
            + "WHERE M.Chat_id = Chats_id "
            + "ORDER BY M.Created_at DESC "
            + "LIMIT 1"
            + ")")
    protected MessageEntity lastMessage;

Somebody know How Can I do this query? Thanks, Greetings

I'd say the best approach is to use DTOs for this, which is a perfect use case for Blaze-Persistence Entity Views.

Blaze-Persistence is a query builder on top of JPA which supports many of the advanced DBMS features on top of the JPA model. I created Entity Views on top of it to allow easy mapping between JPA models and custom interface defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure the way you like and map attributes(getters) via JPQL expressions to the entity model. Since the attribute name is used as default mapping, you mostly don't need explicit mappings as 80% of the use cases is to have DTOs that are a subset of the entity model.

A DTO mapping for your model could look as simple as the following

@EntityView(MessageEntity.class)
interface MessageEntityDto {
    Integer getId();
    // Other fields
}
@EntityView(Chat.class)
interface ChatDto {
    Integer getId();
    @Limit(limit = "1", order = "created DESC")
    @Mapping("messages")
    MessageEntityDto getLatestMessage();
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

ChatDto dto = entityViewManager.find(entityManager, ChatDto.class, id);

But the Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

It will only fetch the mappings that you tell it to fetch