Which data container for SQL JOIN constructions?

Support
#1

Hi, in jmix 1.5,
I want to fill a table or data grid with a source described with real SQL and several LEFT JOINs
What is the best way to do this?
Maybe there’s still a solution, which stays SQL (or JPQL) driven.

Thanks in advance,
br
HP

#2

Hello, I have 2 answers.

  1. Since you have a SQL, test in on your database to see if it’s working, then make it a database view
    Then go to entity designer and check the DB view checkbox. That should enable you to create regular collection data source in the view, and your grid will be a happy grid. Disable Create and Edit actions, leave the Read action. If you need to update, you will need to make some code to handle that.

  2. Grid wants a CollectionContainer, normally it gets it from a collection datasource you define in the view’s xml. That datasource, in turn, has a data loader to retreive the data.
    When you need to do something more, you write a load delegate to do it yourself, like you need in this case.
    Data Loaders :: Jmix Documentation
    In your load delegate, you will need to use (best to write a service) Entity Manager to execute your SQL, then construct a collection from the results. Grid will load that and display, but again no create or update is possible without additional code.

Kind regards,
Mladen

1 Like
#3

If you go for a database view, check out this example: Using database view. It shows how to map a “read-only” entity to the DB view and to use another entity to update the same data.

You can also use Key-Value Containers to show in UI data not mapped to any entity. See for example DataGrid with key-value container. If you execute JPQL, you can write it directly in the loader’s XML. If you need to execute SQL, use load delegate or even populate the data container in onBeforeShow() handler.

Regards,
Konstantin

1 Like