Enhancement to SetCriterion (Subselect)

[Blama]

Hi Isomorphic,

I looked at the serverside com.isomorphic.criteria.criterion.SetCriterion API and saw that it only takes a collection or array as values.

In my (serverside) use-case I get a short (from 1 to ~100) list of IDs with a simple inexpensive query that feed into a SetCriterion and execute my real fetch-query with this.

My suggestion is to support a com.isomorphic.datasource.DSRequest as 3rd parameter in the SetCriterion-constructor or with SetCriterion.setValues().
The DSRequest would never be executed, but the generated SQL for the request would be put as String into the "WHERE field IN (<list>)"-part of the main SQL, resulting in a uncorrelated subquery.
The user would be responsible for using a operation binding that only outputs a single column in the SELECT-list.

Do you think that this is a good enhancement for SmartGWT (serverside). It would save a DB-roundtrip and a DB-call and possibly allow the optimizer to generate a better execution plan.

Best regards,
Blama

[Blama]

As further enhancement, you might also consider supporting this API:

SetCriterion(java.lang.String[] fieldNames, java.lang.String operatorId, DSRequest dsRequest)

After implementing SelectClause stability (see this suggestion) one could use the API to generate SQLs like
WHERE (fieldA, fieldB) IN (SELECT a, b FROM ...) (Oracle IN-syntax).

I don't have use for it right now, but in order to make the gap to the competitors even bigger, you could also add this feature.

Best regards,
Blama

[Isomorphic]

We have plans to add this in an even more general way - any Criterion will allow a (partial) DSRequest to be specified as the way that criterion.value is determined.

We're not sure when this will happen. It would be valid to sponsor if someone needs it by a specific date (including the enhancements to FilterBuilder to allow end users to build sub-selects this way).

[Blama]

Hi Isomorphic,

still no (real) need for it, but now it could save me a DB hit in order to collect an (fairly short) IN value list. Do you have an estimate (this year / next year)?
As I don't use FilterBuilder, I'm only talking about the serverside implementation.

Thank you & Best regards
Blama

[Isomorphic]

At the moment, still no concrete plans, and still eligible for Sponsorship.

[Blama]

Hi Isomorphic,

just found this thread by chance again. Did anything w.r.t your prioritization of this feature change?
I still think it's a great idea, especially if the list you put in IN (..., ..., ..., ...) otherwise is very long.

Best regards
Blama

[Isomorphic]

In the current product, this use case is best achieved with SQL customization. Right now this thread represents the only request for a different way to do the job. Still eligible for sponsorship.

[Blama]

Hi Isomorphic,

of course, an operationBinding on the user DataSource with a <whereClause> along the line of

<whereClause>(id NOT IN (SELECT user_id FROM t_chat_user WHERE chat_id = $criteria.chat_id)) AND ($defaultWhereClause)</whereClause>

In general there would be no reason for the user DataSource to have a chat_id field, but perhaps one could add one with customSelectExpression="null".

This would get rid of the need of server code and also mean only one DB contact.
Also in other use cases the NOT IN-list might be too big and the subquery offer the only way to do it and also offer the DB optimization possibilities.

Thank you & Best regards
Blama

PS: To everyone finding this thread via search, this thread is about a similar topic, where this feature would help.