Native SQL in data loader or anything else

Support
#1

I would like to ask for some help. I am a beginner in JMIX. I am experimenting with the latest JMIX version.
Data model: User → Participant ← Event
image

To load data for a table (DataGrid), I should use the following SQL command:

SELECT u.username, p.sign_up_date_time 
FROM User u 
    LEFT OUTER JOIN Participant p ON p.user_id = u.id AND p.event_id = :anEvent 
    LEFT OUTER JOIN Event e ON p.event_id = e.id AND e.id = :anEvent 
ORDER BY u.username ASC;

Unfortunately, I don’t know how to do this.
1.option) Rewrite SQL to JPQL, but I don’t know how. The SQL command seems too complex for translate to JPQL.
2.option) Load data with a native SQL command, but I couldn’t find any guide in the documentation, no example on how to do it.
3.option) Some other solution, other idea? …

Background information:
The goal is to list all users (User table) in a DataGrid table. Enter a specified event (from Event table) as an input parameter (anEvent), and for those users who have already signed up (have a Participant record), display the sign-up time, and for those who haven’t, display NULL.
Thank you in advance for your help.

#2

I am also not en expert but what you can look into is either use a KeyValue container defined in the screen: Key-Value Containers :: Jmix Documentation

Or you can use a loadDelegate for the data, you leave the query part out of the data container and write some code that provides the data, you can find some info here: Data Loaders :: Jmix Documentation
I think the query should work in JPQL.

Having a loadDelegate allows you to do “anything”, you can call a service that loads data by using SQL and not JPQL. For using SQL try reading up on entityManager and createNativeQuery().

For the second you should have an entity that can hold the data for example an entity with two fields username and signup_time. So that your loadDelegate can return them. Alternatively you can try to create the columns in the datagrid in code which might be a bit more tedious.