adding/dispalying column from another entity

sanchit.bhavsar · July 17, 2017, 2:05pm

Hello,

I would like to display a column in an entity which can be extracted from another entity.
for example, I am attaching a sample db model together with the tables and columns.
If you follow the image, you will see two tables “Account” and “Site” what I want is to display “company” column in “Site” table from “Account” table.
Both the tables are related with the same keyId.
Note: external database tables is added to the project.

Can anyone please tell me the approach how can I achieve this.

Thanking in advance.

Regards
Sanchit

shiryaeva · July 18, 2017, 8:30am

Hi Sanchit,

If Account and Site entities are not mapped directly, you can display them in one table using ValueCollectionDatasource:

        <valueCollectionDatasource id="comboDs">
            <query>
                <![CDATA[select distinct s.date, s.status, s.keyId, a.company from ds$Site s join ds$Account a on s.keyId = a.keyId]]>
            </query>
            <properties>
                <property datatype="date"
                          name="s.date"/>
                <property datatype="boolean"
                          name="s.status"/>
                <property datatype="int"
                          name="s.keyId"/>
                <property datatype="string"
                          name="a.company"/>
            </properties>
        </valueCollectionDatasource>

See an example attached.

ds.zip (30.8K)

sanchit.bhavsar · July 18, 2017, 12:22pm

Hi Olga,

I was trying the same approach but I am getting an error while compiling the query… what I see in the example is that you have a separate screen for valueDatasource and separate screen for “site-browse”.

I made a valueDatasource in the same “site-browse” screen and followed the same steps but it doesn’t work for me.

do I have to create a separate screen as well?

Thanks.

shiryaeva · July 18, 2017, 12:36pm

Entity browser and editor screens are designed for CRUD operations with this entity. Value datasource works with a special type named KeyValueEntity. Using it in the Site entity’s browser screen will definitely cause an error.

So, for your purpose, you need to create a separate screen.

sanchit.bhavsar · July 18, 2017, 12:45pm

okay, so once I create a separate screen for valueDatasource including all the columns then how can I relate this to the browser screen because all I want is in the separate screen.

In the example above how come the Site browser screen can see the company column while the datasource are different?

sanchit.bhavsar · July 18, 2017, 1:13pm

I created a separate screen but I’m getting the same error.

error trace:

com.haulmont.cuba.core.global.RemoteException:
---
java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: 
Exception Description: Problem compiling [
                select distinct s.relationshipStatus, s.siteName, s.registrationDate, s.publisherSegment, s.trafficChannel, s.apiKey, a.company from zeusv2$PublisherSite s join zeusv2$Account a on s.apiKey = a.apiKey
            ]. 
[16, 36] The state field path 's.relationshipStatus' cannot be resolved to a valid type.
[38, 48] The state field path 's.siteName' cannot be resolved to a valid type.
[50, 68] The state field path 's.registrationDate' cannot be resolved to a valid type.
[70, 88] The state field path 's.publisherSegment' cannot be resolved to a valid type.
[90, 106] The state field path 's.trafficChannel' cannot be resolved to a valid type.
[108, 116] The state field path 's.apiKey' cannot be resolved to a valid type.
[118, 127] The state field path 'a.company' cannot be resolved to a valid type.
[133, 153] The abstract schema type 'zeusv2$PublisherSite' is unknown.
[161, 175] The abstract schema type 'zeusv2$Account' is unknown.
---
org.eclipse.persistence.exceptions.JPQLException: 
Exception Description: Problem compiling [
                select distinct s.relationshipStatus, s.siteName, s.registrationDate, s.publisherSegment, s.trafficChannel, s.apiKey, a.company from zeusv2$PublisherSite s join zeusv2$Account a on s.apiKey = a.apiKey
            ]. 
[16, 36] The state field path 's.relationshipStatus' cannot be resolved to a valid type.
[38, 48] The state field path 's.siteName' cannot be resolved to a valid type.
[50, 68] The state field path 's.registrationDate' cannot be resolved to a valid type.
[70, 88] The state field path 's.publisherSegment' cannot be resolved to a valid type.
[90, 106] The state field path 's.trafficChannel' cannot be resolved to a valid type.
[108, 116] The state field path 's.apiKey' cannot be resolved to a valid type.
[118, 127] The state field path 'a.company' cannot be resolved to a valid type.
[133, 153] The abstract schema type 'zeusv2$PublisherSite' is unknown.
[161, 175] The abstract schema type 'zeusv2$Account' is unknown.
	at com.haulmont.cuba.core.sys.ServiceInterceptor.aroundInvoke(ServiceInterceptor.java:127)
	at sun.reflect.GeneratedMethodAccessor118.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:629)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:618)
	at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:168)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
	at com.sun.proxy.$Proxy187.loadValues(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.haulmont.cuba.core.sys.remoting.LocalServiceInvokerImpl.invoke(LocalServiceInvokerImpl.java:94)
	at com.haulmont.cuba.web.sys.remoting.LocalServiceProxy$LocalServiceInvocationHandler.invoke(LocalServiceProxy.java:148)
	at com.sun.proxy.$Proxy25.loadValues(Unknown Source)
	at com.haulmont.cuba.client.sys.DataManagerClientImpl.loadValues(DataManagerClientImpl.java:137)
	at com.haulmont.cuba.gui.data.impl.GenericDataSupplier.loadValues(GenericDataSupplier.java:80)
	at com.haulmont.cuba.gui.data.impl.ValueDatasourceDelegate.loadData(ValueDatasourceDelegate.java:78)
	at com.haulmont.cuba.gui.data.impl.ValueCollectionDatasourceImpl.loadData(ValueCollectionDatasourceImpl.java:83)
	at com.haulmont.cuba.gui.data.impl.CollectionDatasourceImpl.refresh(CollectionDatasourceImpl.java:146)
	at com.haulmont.cuba.gui.data.impl.CollectionDatasourceImpl.refresh(CollectionDatasourceImpl.java:104)
	at com.haulmont.cuba.gui.data.impl.CollectionDatasourceImpl.refreshIfNotSuspended(CollectionDatasourceImpl.java:97)
	at com.haulmont.cuba.gui.data.impl.CollectionDsHelper.autoRefreshInvalid(CollectionDsHelper.java:106)
	at com.haulmont.cuba.web.gui.data.DataGridIndexedCollectionDsWrapper.size(DataGridIndexedCollectionDsWrapper.java:228)
	at com.vaadin.data.util.GeneratedPropertyContainer.size(GeneratedPropertyContainer.java:724)
	at com.vaadin.server.communication.data.RpcDataProviderExtension.beforeClientResponse(RpcDataProviderExtension.java:330)
	at com.vaadin.server.communication.UidlWriter.write(UidlWriter.java:112)
	at com.vaadin.server.communication.UidlRequestHandler.writeUidl(UidlRequestHandler.java:124)
	at com.vaadin.server.communication.UidlRequestHandler.synchronizedHandleRequest(UidlRequestHandler.java:92)
	at com.vaadin.server.SynchronizedRequestHandler.handleRequest(SynchronizedRequestHandler.java:41)
	at com.vaadin.server.VaadinService.handleRequest(VaadinService.java:1422)
	at com.vaadin.server.VaadinServlet.service(VaadinServlet.java:385)
	at com.haulmont.cuba.web.sys.CubaApplicationServlet.serviceAppRequest(CubaApplicationServlet.java:290)
	at com.haulmont.cuba.web.sys.CubaApplicationServlet.service(CubaApplicationServlet.java:197)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:230)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
	at com.haulmont.cuba.web.sys.CubaHttpFilter.handleNotFiltered(CubaHttpFilter.java:108)
	at com.haulmont.cuba.web.sys.CubaHttpFilter.doFilter(CubaHttpFilter.java:95)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:474)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:624)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:349)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:783)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:789)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1437)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:745)

Here, screen.xml:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<window xmlns="http://schemas.haulmont.com/cuba/window.xsd"
        caption="msg://caption"
        class="com.company.zeusv2.web.publishersite.Screen"
        messagesPack="com.company.zeusv2.web.publishersite">

    <dsContext>
        <valueCollectionDatasource id="comboDs">
            <query>
                <![CDATA[select distinct s.relationshipStatus, s.siteName, s.registrationDate, s.publisherSegment, s.trafficChannel, s.apiKey, a.company from zeusv2$PublisherSite s join zeusv2$Account a on s.apiKey = a.apiKey]]>
            </query>
            <properties>
                <property datatype="string"
                          name="s.relationshipStatus"/>
                <property datatype="string"
                          name="s.siteName"/>
                <property datatype="dateTime"
                          name="s.registrationDate"/>
                <property datatype="string"
                          name="s.publisherSegment"/>
                <property datatype="string"
                          name="s.trafficChannel"/>
                <property datatype="string"
                          name="s.apiKey"/>
                <property datatype="string"
                          name="a.company"/>
            </properties>
        </valueCollectionDatasource>
    </dsContext>
    <dialogMode height="600"
                width="800"/>
    <layout  expand="sitesGrid"
             spacing="true">
        <filter id="filter"
                applyTo="sitesGrid"
                datasource="comboDs">
            <properties include=".*"/>
        </filter>
        <dataGrid id="sitesGrid"
                  datasource="comboDs"
                  width="100%">
            <columns>
                <column id="relationshipStatus"
                        editable="false"
                        property="s.relationshipStatus"/>
                <column id="siteName"
                        editable="false"
                        property="s.siteName"/>
                <column id="registrationDate"
                        editable="false"
                        property="s.registrationDate"/>
                <column id="publisherSegment"
                        editable="false"
                        property="s.publisherSegment"/>
                <column id="trafficChannel"
                        editable="false"
                        property="s.trafficChannel"/>
                <column id="company"
                        editable="false"
                        property="a.company"/>
                <column id="apiKey"
                        editable="false"
                        property="s.apiKey"/>
            </columns>
            <rowsCount/>
        </dataGrid>
    </layout>
</window>

Am I missing something?

sanchit.bhavsar · July 19, 2017, 9:43am

Hi Olga,

Any thoughts about this one?

shiryaeva · July 19, 2017, 1:30pm

As far as I understand, you want to keep all browser screen functionality (i.e. CRUD buttons that invoke the editor screen) and, at the same time, you want to display value datasource instead of group datasource of a single entity.

So, there is no reason why you can’t use a separate screen. You can add the Create, Edit and Remove buttons and invoke the Site entity editor from this separate screen.

Below is an example of the logic, of course, you can implement your own:

screen.xml:

<button id="createBtn"
        caption="Create"
        invoke="createSite"/>

Screen.java:

public void editSite() {
    if (sitesGrid.getSingleSelected() != null) {
        Integer keyId = sitesGrid.getSingleSelected().getValue("s.keyId");
        LoadContext<Site> loadContext = LoadContext.create(Site.class)
                .setQuery(LoadContext.createQuery("select s from ds$Site s where s.keyId = :keyId")
                .setParameter("keyId", keyId))
                .setView(View.LOCAL);
        List<Site> sites = dataManager.loadList(loadContext);

        if (sites.size() == 0) {
            createSite();
        } else if (sites.size() == 1) {
            Site site = sites.get(0);
            AbstractEditor editor = openEditor(site, WindowManager.OpenType.NEW_WINDOW);
            editor.addCloseWithCommitListener(() -> comboDs.refresh());
        } else {
            throw new IllegalStateException("More than one site with keyId " + keyId);
        }
    }
}

I’ve added this functionality to the sample project, see attached.

ds+editor.zip (32.7K)

sanchit.bhavsar · July 19, 2017, 2:07pm

Hi Olga,
I think you misunderstood it. I am not using editor screens for this project.
I have a browser screen without CRUD buttons in-which, I want to add an additional column from another table from different database i.e. “company” from table “account” (something like

 <column id="account.company"/>

)
Is there a lenient way to add columns from another tables.

sanchit.bhavsar · July 19, 2017, 3:13pm

I don’t know for some reason the query used in valueCollectionDatasource is not compiling in the Studio but I tried the same query in MySQLWorkbench and it worked!!

shiryaeva · July 20, 2017, 5:40am

JPQL and SQL are different query languages. The datasources load data by sending JPQL queries to the middleware. If your query is not compiling, perhaps there is an error in the query.

If you don’t even need the CRUD functionality, and all you need is to display columns, so use ValueCollectionDatasource with the query that joins two entities on the condition of equal apiKey, as suggested above.

sanchit.bhavsar · July 21, 2017, 11:47am

Hi Olga,

I’m doing the exact same thing as you mentioned but I cannot compile the query.

Please check the error mentioned above.

shiryaeva · July 21, 2017, 11:55am

Pay attention to the following error messages:

[133, 153] The abstract schema type 'zeusv2$PublisherSite' is unknown.
[161, 175] The abstract schema type 'zeusv2$Account' is unknown.

Make sure the zeusv2$PublisherSite and ‘zeusv2$Account’ entities exist in your project.

sanchit.bhavsar · July 21, 2017, 12:34pm

It does exist in my project…
Have a look here…
They are both different database schema from an additional MySQL datastore.

DataModel

shiryaeva · July 21, 2017, 12:56pm

JPA seems to fail to locate the entities when compiling the query.

Check if these entity classes are explicitly listed in the persistence.xml file:

    <persistence-unit name="zeusv2"
                      transaction-type="RESOURCE_LOCAL">
        <class>com.company.zeusv2.entity.Account</class>
        <class>com.company.zeusv2.entity.PublisherSite</class>
    </persistence-unit>

Also, check the entity annotations. The @Table annotation value should correspond to your database table name, and the value of the @Entity annotation - to the name used in the query, for example:

@Table(name = "ZEUSV2_ACCOUNT")
@Entity(name = "zeusv2$Account")

select a from zeusv2$Account a

sanchit.bhavsar · July 21, 2017, 2:37pm

Hi Olga,
You are right, the entity classes were missing in the persistence.xml file but when I explicitly add entity classes to the file then I get copies of the table in the data model (see the attached picture) and also an error when I try to open browser screen.
error:

&#91;16:31:32.849&#93; Unexpected error
java.lang.IllegalStateException: Ambiguous entity FQNs:&#91;Account &#91;zeusv2$Account&#93;, Account &#91;zeusv2$Account&#93;&#93;
&#91;16:31:32.849&#93; Exception has been thrown by com.haulmont.studio.ui.app.a@4d2834cf:
&#91;16:31:32.850&#93; java.lang.IllegalStateException: Ambiguous entity FQNs:&#91;Account &#91;zeusv2$Account&#93;, Account&#91;zeusv2$Account&#93;&#93;
 at com.haulmont.studio.common.model.aI.e(SourceFile:708)
 at com.haulmont.studio.common.model.aV.a(SourceFile:426)
 at com.haulmont.studio.backend.sd.d.a(SourceFile:69)
 at com.haulmont.studio.backend.sd.d.a(SourceFile:43)
 at com.haulmont.studio.backend.sd.p.d(SourceFile:179)
 at com.haulmont.studio.backend.sd.n.a(SourceFile:271)
 at com.haulmont.studio.ui.sd.a.b(SourceFile:365)
 at com.haulmont.studio.ui.sd.a.f(SourceFile:359)
 at com.haulmont.studio.ui.sd.a.a(SourceFile:374)
 at com.haulmont.studio.ui.a.a(SourceFile:204)
 at sun.reflect.GeneratedMethodAccessor205.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:498)
 at com.vaadin.event.ListenerMethod.receiveEvent(ListenerMethod.java:510)
 at com.vaadin.event.EventRouter.fireEvent(EventRouter.java:200)
 at com.vaadin.event.EventRouter.fireEvent(EventRouter.java:163)
 at com.vaadin.server.AbstractClientConnector.fireEvent(AbstractClientConnector.java:1037)
 at com.vaadin.ui.Tree.changeVariables(Tree.java:517)
 at com.vaadin.server.communication.ServerRpcHandler.changeVariables(ServerRpcHandler.java:615)

Note: both the entities are extracted from additional datastore.
After adding entity into persistence file it considered the second table in the main datastore…

DataModel_New

shiryaeva · July 24, 2017, 5:47am

Please create another issue for this problem.