How do I set Properties in DataLoader for KeyValueEntities

Hi,

I want to load a KeyValueEntity where emails have a many to one relationship to the users but not every user needs to have a email.
So I have the following in the data part of the descriptor.

<keyValueCollection id="usersDc">
        <properties>
            <property name="user" class="com.acme.test.user.User"/>
            <property name="email" class="com.acme.test.user.Email"/>
        </properties>
        <loader id="usersDl">
            <query>
                <![CDATA[select u from myPrj_User u LEFT JOIN u.emails e ON e.from = :from]]>
                <condition>
                    <c:jpql>
                        <c:where>u.role = :role</c:where>
                    </c:jpql>
                </condition>
            </query>
        </loader>
    </keyValueCollection>

In the layout part I have a group table that works so far.

<groupTable id="usersTable"
                                dataContainer="usersDc">
    <columns>
        <column id="user.name"/>
        <column id="email.adress"/>
    </columns>
</groupTable>

If I load the data with the dataManager it works fine. But I’d like to define the query in the descriptor. That’s how I do it with dataManager with given query, role and from.

final List<KeyValueEntity> users;
users = dataManager.loadValues(query).parameter("role", role).parameter("from", from).properties("user","email").list()
usersDc.setItems(users);

Really nice would be just to use the dataLoader like this. But I don’t know how I declare the properties and without declaring the properties I only get the users but no emails.

usersDl.setParameter("role", role);
usersDl.setParameter("from", from);
usersDl.load();

I can load the query from the dataLoader but that one doesn’t contain the conditions declared with <c:jpql> in the descriptor.

usersDl.getQuery()

What’s the best practice to do that?

Regards

Hi,
It’s not about properties. You have written your JPQL query in such a way that it returns only one entity.
The query should look like “select u, e from myPrj_User u LEFT JOIN u.emails e …”

Example of keyValueCollection query can be found here: https://docs.jmix.io/jmix/1.0/backoffice-ui/data/key-value-containers.html

1 Like

Hi and thanks for your support.

It works if I have it in the CDATA part of the query. But if I split the join to c:jqpl part I get a NullPointerException even if the from variable is valid.

For the moment it’ll work inside the CDATA part but I’m curious how to get the following working

<![CDATA[select u, e from myPrj_User u]]>
    <condition>
        <and>
            <c:jpql>
                <c:where>u.role = :role</c:where>
            </c:jpql>
            <c:jpql>
                <c:join>LEFT JOIN u.emails e ON e.from = :from</c:join>
                <c:where>u IS NOT NULL</c:where>
            </c:jpql>
        </and>
    </condition>

Can you show full stack trace of the NullPointerException?

I guess, you should unify two c:where conditions into one tag. Because one of them depends on the join in the second c:jpql section.

Hi Alexander,

the stack trace is

java.lang.NullPointerException
at io.jmix.data.impl.jpql.QueryParserAstBased.getQueryPaths(QueryParserAstBased.java:196)
at io.jmix.data.accesscontext.LoadValuesAccessContext.getEntityClasses(LoadValuesAccessContext.java:44)
at io.jmix.securitydata.constraint.LoadValuesConstraint.applyTo(LoadValuesConstraint.java:55)
at io.jmix.securitydata.constraint.LoadValuesConstraint.applyTo(LoadValuesConstraint.java:31)
at io.jmix.core.AccessManager.lambda$applyConstraints$2(AccessManager.java:72)
at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:183)
at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195)
at java.base/java.util.stream.ReferencePipeline$2$1.accept(ReferencePipeline.java:177)
at java.base/java.util.Spliterators$ArraySpliterator.forEachRemaining(Spliterators.java:948)
at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484)
at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474)
at java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:150)
at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:173)
at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
at java.base/java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:497)
at io.jmix.core.AccessManager.applyConstraints(AccessManager.java:72)
at io.jmix.data.impl.DataStoreCrudValuesListener.beforeValueLoad(DataStoreCrudValuesListener.java:43)
at io.jmix.core.datastore.DataStoreBeforeValueLoadEvent.sendTo(DataStoreBeforeValueLoadEvent.java:66)
at io.jmix.core.datastore.AbstractDataStore.fireEvent(AbstractDataStore.java:343)
at io.jmix.core.datastore.AbstractDataStore.loadValues(AbstractDataStore.java:251)
at io.jmix.core.impl.UnconstrainedDataManagerImpl.loadValues(UnconstrainedDataManagerImpl.java:264)
at io.jmix.ui.model.impl.KeyValueCollectionLoaderImpl.load(KeyValueCollectionLoaderImpl.java:87)

until the load call inside my controller.

I unified the c:where statements but the result is the same.

<![CDATA[select u, e from myPrj_User u]]>
    <condition>
        <c:jpql>
            <c:join>LEFT JOIN u.emails e ON e.from = :from</c:join>
            <c:where>u.role = :role</c:where>
        </c:jpql>
    </condition>

I thought the problem is, that e in select u, e is defined in c:join part and not in CDATApart.

Hi!

Could you send us a small sample project that demonstrates the issue? It would be helpful to avoid any misunderstanding.

Regards,
Nadezhda.