Adding View to database

Support
#1

Hi again, is there any way to implement view to the database? If I create it manually in db, liquidbase wants to drop it each time I start the app. I want to use view to load specified data in browser screen.
And second question is, how to import it correctly? By Entity or there is some other way?

Example of SQL Query view(shows how many hours users have had work in specific month):

SELECT SUM(dbo.CALENDAR.DURATION) AS HoursWorked,
dbo.USER_.USERNAME AS username,
dbo.USER_.FIRST_NAME AS firstname,
dbo.USER_.LAST_NAME AS lastname,
DATEPART(MONTH, dbo.CALENDAR.START_DATE) AS Month,
DATEPART(YEAR, dbo.CALENDAR.START_DATE) AS Year

FROM dbo.CALENDAR

LEFT OUTER JOIN WORK_TIME ON CALENDAR.USER_ID = WORK_TIME.USER_ID_ID
INNER JOIN dbo.USER_ ON dbo.CALENDAR.USER_ID = dbo.USER_.ID

GROUP BY dbo.USER_.USERNAME,
dbo.USER_.FIRST_NAME,
dbo.USER_.LAST_NAME,
DATEPART(MONTH, dbo.CALENDAR.START_DATE),
DATEPART(YEAR, dbo.CALENDAR.START_DATE)

##########################################################################

INSERT INTO WORK_TIME(HOURSWORKED, USERNAME , FIRST_NAME, LAST_NAME, MONTH_, YEAR_)
SELECT WorkTime.HoursWorked, WorkTime.username, WorkTime.firstname, WorkTime.lastname, WorkTime.Month, WorkTime.Year FROM

(SELECT * FROM

(SELECT SUM(dbo.CALENDAR.DURATION) AS HoursWorked,
dbo.USER_.USERNAME AS username,
dbo.USER_.FIRST_NAME AS firstname,
dbo.USER_.LAST_NAME AS lastname,
DATEPART(MONTH, dbo.CALENDAR.START_DATE) AS Month,
DATEPART(YEAR, dbo.CALENDAR.START_DATE) AS Year

FROM dbo.CALENDAR

LEFT OUTER JOIN WORK_TIME ON CALENDAR.USER_ID = WORK_TIME.USER_ID_ID
INNER JOIN dbo.USER_ ON dbo.CALENDAR.USER_ID = dbo.USER_.ID

GROUP BY dbo.USER_.USERNAME,
dbo.USER_.FIRST_NAME,
dbo.USER_.LAST_NAME,
DATEPART(MONTH, dbo.CALENDAR.START_DATE),
DATEPART(YEAR, dbo.CALENDAR.START_DATE)) AS WT) AS WorkTime

I could insert it like this to the Entity but it isn’t data I want to be stored. Generated View data should not be stored in Table, the main purpose of it is to show already stored data in specific way.

1 Like
#2

@Platform I didn’t found any resolve in documentation about it. How to do this query properly with JPQL to create view/entity witch shows this data?

SELECT SUM(dbo.CALENDAR.DURATION) AS HoursWorked, dbo.USER_.USERNAME, dbo.USER_.FIRST_NAME, dbo.USER_.LAST_NAME, DATEPART(MONTH, dbo.CALENDAR.START_DATE) AS Expr2, DATEPART(YEAR,
dbo.CALENDAR.START_DATE) AS Expr3
FROM dbo.CALENDAR INNER JOIN
dbo.USER_ ON dbo.CALENDAR.USER_ID = dbo.USER_.ID
GROUP BY dbo.USER_.USERNAME, dbo.USER_.FIRST_NAME, dbo.USER_.LAST_NAME, DATEPART(MONTH, dbo.CALENDAR.START_DATE), DATEPART(YEAR, dbo.CALENDAR.START_DATE)

#3

I did function like this and its working. But when I call out it in LoadDelegate() handler function, the Jmix search filter doesn’t work at all. Is it a bug or should I call out my function in different way?

        @Override
        public List<WorkTime> loadByFullQuery() {
            EntityManager entityManager = entityManagerFactory.createEntityManager();
            TypedQuery<WorkTime> query = entityManager
                    .createQuery("select new com.company.icms.entity.WorkTime(sum(c.duration), c.user.username, c.user.firstName, c.user.lastName, function('month', c.startDate), function('year', c.startDate), c.user)" +
                    " from Calendar c group by" +
                    " c.user.username, c.user.firstName, c.user.lastName, c.user, c.startDate", WorkTime.class);
            return query.getResultList();
        }
#4

Hi Marcin,

You can map a JPA Entity to your view.
Notice the “DB View” checkbox in the entity designer:
image

It represents the @DbView annotation on the entity class:

@DbView
@JmixEntity
@Table(name = "USER_VIEW")
@Entity
public class UserView {
// ...

You can use such an entity in UI screens without any custom load delegate, and the filter should work too.

As for the problem with Studio trying to create a Liquibase changelog to delete the view - it’s registered and will be fixed in the next release. Meanwhile you can instruct the Studio to ignore your objects by adding the following property to application.properties:

main.datasource.studio.liquibase.excludePrefixes = USER_VIEW

Regards,
Konstantin

1 Like