Filter custom condition with unrelated entity

Support
#1

Hi Team,

I have a request to create a custom filter condition on entity, for example, Order, filter by another unrelated entity, say Device, where Order.customer.name = Device.usedBy. So, here Order and Device are 2 unrelated entities. I tried to create customer filter like this:

            <custom caption="device contains"
                    join="join forum_Device i"
                    name="deviceItem"
                    paramClass="com.company.forum.entity.Device"
                    paramWhere="{E}.customer.name=i.usedBy">
               i.usedBy = ?
            </custom>

This cause exception:

JPQLException: 
Exception Description: Problem compiling [select e from forum_Device e where e.customer.name = i.usedBy]. 
[35, 41] The state field path 'e.customer.name' cannot be resolved to a valid type.
[44, 50] The state field path 'i.usedBy' cannot be resolved to a valid type.
[44, 45] The identification variable 'i' is not defined in the FROM clause.

Looks like the join attribute of xml is not well parsed, please suggest.

#2

Hi,
In your case you should just use ", " instead of “join”:

        join=", forum_Device i"
        paramWhere="{E}.customer.name=i.usedBy">

So {E} and i are going to be connected through where clause.

#3

Hi Alex,
Thanks for your reply! Yes, with using comma, the join statement is correctly parsed. But seems paramWhere is missing now. I created a test project and using:

            <custom caption="device memo ="
                    join=", forum_Device i"
                    name="deviceItem"
                    paramClass="java.lang.String"
                    paramWhere="{E}.name=i.memo">
               i.memo = ?
            </custom>

Observed in app.log:

2019-09-17 09:50:00.719 DEBUG [http-nio-13000-exec-3/app-core/test] com.haulmont.cuba.core.app.RdbmsStore - loadList: metaClass=forum_Customer, view=com.company.forum.entity.Customer/_base, query=select e from forum_Customer, max=50
2019-09-17 09:50:00.728 DEBUG [http-nio-13000-exec-3/app-core/test] eclipselink.sql - <t 525378069, conn 680428765> SELECT t1.ID AS a1, t1.DELETE_TS AS a2, t1.DELETED_BY AS a3, t1.EMAIL AS a4, t1.NAME AS a5, t1.VERSION AS a6 FROM FORUM_DEVICE t0, FORUM_CUSTOMER t1 WHERE (((t0.MEMO = ?) AND (t1.DELETE_TS IS NULL)) AND (t0.DELETE_TS IS NULL)) LIMIT ? OFFSET ?
    bind => [128G, 50, 0]

Attach the test project, test page is in Application->datagrid test
xml descriptor: com/company/forum/web/screens/datagrid-test.xml
forum.zip (93.9 KB)

#4

Eh, I messed up. “paramWhere” is not for query, it’s to load parameter options.
You can “connect” {E} and “i” variables in tag text:

<custom ... >
{E}.name=i.memo
</custom>

And you can’t use “i” from “join” attribute in paramWhere statement, because join is not added to that options loading query.

#5

Thank you! I think i also messed up by the name paramWhere…
Based on the hint you provide, I just tested with below and it works.

        <custom ...>
            {E}.name=i.memo and i.memo = ?
        </custom>