I am using Jmix 1.3.1.
I have a database structure (that I cannot change) that is as follows:
- There is a large table with several columns, which I'll call table T.
- One of these columns (string-valued), which I'll call column L, is the only one I am interested in. It has a few distinct values.
I want to build an entity picker where the user can select one of the values from column L for filtering a report by that column. I do not want the entire table T displayed in the entity picker, only the distinct values of column L.
My initial attempt was to create a @JmixEntity annotated class with only my column L in it with a @NamedNativeQuery annotation and a @SqlResultSetMapping annotation that contained an SQL query like SELECT DISTINCT L FROM T
, similar to what I found in this answer. I have no idea whether this is even supposed to work with Jmix, I can only find information about this approach with JPA, not with Jmix.
My current attempt is to have two classes:
- One annotated with @JmixEntity, @Table, @Entity and @Store like any other class for the entire table T, but with the only fields being the ID and the column L.
- One annotated only with @JmixEntity with only the column L which also serves as @InstanceName and @Id.
Then I adapt the standard entity browser for the first one of these classes to have a query select distinct e.L FROM T e
.
Both of these approaches are causing the entity picker to open, but no data to be displayed, and I am stuck on how to debug what is going on. I am not sure which of these approaches might even work or whether there might be another approach to achieve what I want.
I ended up solving this problem by not connecting the entity to the database at all (i.e. no @Table, no @Store, no @NamedNativeQuery annotation, no annotations other than @JmixEntity and @Entity).
Then in my UI controller, in a method like the one knstvk suggested for debugging purposes, I created a JdbcTemplate and put my query into a jdbcTemplate.queryForList call. Then I could manually dataManager.create the needed entities.
I am not claiming that this is an especially elegant solution, but it is working.