Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
Clear All
new posts

    Enhancement to SetCriterion (Subselect)

    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

    #2
    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
    Last edited by Blama; 18 Feb 2016, 04:02. Reason: Fixed Oracle Language Reference link

    Comment


      #3
      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).

      Comment


        #4
        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

        Comment


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

          Comment


            #6
            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

            Comment


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

              Comment


                #8
                Hi Isomorphic,

                of course, an operationBinding on the user DataSource with a <whereClause> along the line of
                Code:
                <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.

                Comment


                  #9
                  Hi Isomorphic,

                  skimming and scanning the 13.1d API after the estimatedTotalRows confusion here I noticed AdvancedCriterionSubquery in the docs.

                  Really really great, yet again! I think the use case from #8 could be solved with a Simple valueQuery from your docs.

                  Thank you & Best regards
                  Blama

                  Comment


                    #10
                    Yes, it's a huge new feature and very, very powerful. There are two samples of it:

                    https://smartclient.com/smartclient-...rRelatedFields
                    https://smartclient.com/smartclient-...gregatedFilter

                    If you play around with it and come up with some additional interesting & novel use cases, we are still looking for additional cases to add to the samples!

                    Note that it's a 14.0 feature - docs for it should not be appearing in 13.1 and we'll correct that.

                    Comment


                      #11
                      Hi Isomorphic,

                      could you add these and other new samples also to the newSamples-section?

                      Thank you & Best regards
                      Blama

                      Comment

                      Working...
                      X