Filter custom condition with unrelated entity

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 = 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"
               i.usedBy = ?

This cause exception:

Exception Description: Problem compiling [select e from forum_Device e where = i.usedBy]. 
[35, 41] The state field path '' 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.

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

        join=", forum_Device i"

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

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"
               i.memo = ?

Observed in app.log:

2019-09-17 09:50:00.719 DEBUG [http-nio-13000-exec-3/app-core/test] - loadList: metaClass=forum_Customer,, 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 (93.9 KB)

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 ... >

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

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 = ?