Announcement

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

    AdvacncedCriteria is adding not null constraint in generated query.

    Hi
    I am facing an issue where query is getting generated with not null constraint for supplied advancedCriteria.

    below is the log

    2023-11-16 12:48:01,218 INFO [com.isomorphic.servlet.IDACall] (default task-41) Performing 1 operation(s) [UA: Java/1.6.0_07]
    2023-11-16 12:48:01,226 INFO [com.isomorphic.datasource.DSRequest] (default task-41) Executing isc_ListGrid_3->code.fetch[fetch-by-designator-and-value] rows: 0->-1 with
    criteria: {
    operator:"or",
    _constructor:"AdvancedCriteria",
    criteria:[
    {
    operator:"and",
    criteria:[
    {fieldName:"code_value", operator:"equals", value:"0016070"},
    {fieldName:"code_designator", operator:"equals", value:"I10P"}
    ]
    },
    {
    operator:"and",
    criteria:[
    {fieldName:"code_value", operator:"equals", value:"0016071"},
    {fieldName:"code_designator", operator:"equals", value:"I10P"}
    ]
    }
    ]
    }
    sortBy: [pk]
    2023-11-16 12:48:01,259 INFO [com.isomorphic.sql.SQLDataSource] (default task-41) derived query: SELECT code.pk, code.code_value, code.code_designator, code.code_version, code.code_meaning, code.clinic_pk FROM code WHERE ($defaultWhereClause) and (code.clinic_pk is null or code.clinic_pk in (select c.pk from sono_user su join clinic c on (su.clinic_fk = c.pk) where su.user_id=$servletRequest.remoteUser )) ORDER BY $defaultOrderClause
    2023-11-16 12:48:01,266 DEBUG [com.isomorphic.sql.SQLDataSource] (default task-41) 156: Executing SQL query on 'PostgreSQL': SELECT code.pk, code.code_value, code.code_designator, code.code_version, code.code_meaning, code.clinic_pk FROM code WHERE ((((code.code_value = '0016070' AND code.code_value IS NOT NULL) AND (code.code_designator = 'I10P' AND code.code_designator IS NOT NULL)) OR ((code.code_value = '0016071' AND code.code_value IS NOT NULL) AND (code.code_designator = 'I10P' AND code.code_designator IS NOT NULL)))) and (code.clinic_pk is null or code.clinic_pk in (select c.pk from sono_user su join clinic c on (su.clinic_fk = c.pk) where su.user_id='admin2' )) ORDER BY code.pk
    2023-11-16 12:48:01,268 DEBUG [com.isomorphic.sql.PoolableSQLConnectionFactory] (default task-41) makeObject() created an unpooled Connection '426260436'
    2023-11-16 12:48:01,268 DEBUG [com.isomorphic.sql.SQLConnectionManager] (default task-41) Borrowed connection '426260436'
    2023-11-16 12:48:01,269 DEBUG [com.isomorphic.sql.SQLTransaction] (default task-41) Started new PostgreSQL transaction "426260436"
    2023-11-16 12:48:01,269 DEBUG [com.isomorphic.sql.SQLDataSource] (default task-41) Setting DSRequest as being part of a transaction on connection 426260436


    I dont want IS NOT NULL constraint to be included in query
    Last edited by zahid2948; 20 Nov 2023, 03:51.

    #2
    You can remove this via setting strictSQLFiltering:true:

    https://smartclient.com/smartclient-...ctSQLFiltering

    However note that this will cause filtering behavior that most people consider counter-intuitive.

    If what you actually want is to include records that have a null value, you can also do that, for a specific query, by just adding an "or" with an isNull Criterion.

    Comment

    Working...
    X