Solved

pre-filters that are off (hidden) are still part of query?

  • 17 September 2022
  • 9 replies
  • 79 views
Freddy
Userlevel 5
Badge +16
  • Freddy
  • Thinkwise Local Partner Brasil
  • 389 replies

I found this a bit odd. I have a group of pre-filters that are off hidden and they showed up in the query. Because I missed a pre-filter, I missed some data records. I don't think this can be correct. 

 

WHERE (t1.tenant_id = convert(int,SESSION_CONTEXT(N'tenant_id'))
)
 AND t1.[tenant_id] = @p0 AND t1.[process_id] = @p1 AND ((exists ( select 1 
           from dbo.get_caller_process_tabs() cpt
          where cpt.process_id      = t1.process_id
            and cpt.is_cancel       = 1 )
)
 OR (exists ( select 1 
           from dbo.get_caller_process_tabs() cpt
          where cpt.process_id      = t1.process_id
            and cpt.is_close        = 1 )
)
 OR (exists ( select 1 
           from dbo.get_caller_process_tabs() cpt
          where cpt.process_id      = t1.process_id
            and cpt.is_late         = 1 )
)
 OR (exists ( select 1 
           from dbo.get_caller_process_tabs() cpt
          where cpt.process_id      = t1.process_id
            and cpt.is_open         = 1 )
)
 OR (exists ( select 1 
           from dbo.get_caller_process_tabs() cpt
          where cpt.process_id      = t1.process_id
            and cpt.is_wait         = 1 )
)
)

 

icon

Best answer by Anne Buit 5 October 2022, 11:56

View original
LEF - The future favors the brave. [lef.tec.br]
This topic has been closed for comments

9 replies

E
Rank
Userlevel 6
Badge +16

Is the prefilter part of a mandatory group?

Freddy
Userlevel 5
Badge +16

Is the prefilter part of a mandatory group?

Yes, and I think I understand why it does it like this then. However when everything is off and hidden it should not be part of your query at all.  In all other scenario's I can follow the logic that the filter(s) should be applied. I can perfectly have mandatory groups that do not cover the whole dataset, that I only want to use in certain variants. 

So I would expect:

  • Or in case of all off hidden, the shown where clause based on the grip is not added. .
  • Or a group should get an on/off toggle to explicitly indicate that you want this group active or not for a certain variant or by default. 

 

LEF - The future favors the brave. [lef.tec.br]
E
Rank
Userlevel 6
Badge +16

There is a validation to warn you of the problem when deactivating a mandatory prefilter. If you want different behavior feel free to create an idea for this in the ideas section. 

 

 

Freddy
Userlevel 5
Badge +16

There is a validation to warn you of the problem when deactivating a mandatory prefilter. If you want different behavior feel free to create an idea for this in the ideas section. 

 

 

This I understand but it is not what I am talking about. 

This validation is totally legit when the group is used. When you put it off and hidden, it means you are not going to use this group. However it will only show the results that meet the OR OR OR OR requirement. And there lies the problem. You don't see all results when the group doesn't cover the whole dataset. 

And besides that I'm pretty sure it will improve performance when you just remove the pre-filter group from the WHERE clause completely when it's OFF and HIDDEN. 

I don't think this is an idea but an unintended side-effect which should be resolved as a bug. 

LEF - The future favors the brave. [lef.tec.br]
Anne Buit
Rank
Userlevel 7
Badge +5

Hi Freddy,

Even though the individual prefilters in the mandatory prefilter group are turned off, the prefilter group iin itself is still considered mandatory.

Indicium will subsequently only return information of records that would fall in one or more of the prefilters in the mandatory prefilter group, using this orred-clause.

Say that you have a mandatory exclusive prefilter group that has prefilters for Red, Green and Orange. Even when all of these prefilters are hidden, you can still not get access to Blue.

Core principle here is that visually hiding prefilters (via roles or otherwise) should not ‘increase’ the amount of data you have access to. Having an extra role granted with prefilters visible would then reduce the data you have access to, which goes against the current RBAC set-up.

Freddy
Userlevel 5
Badge +16

Hi Freddy,

Even though the individual prefilters in the mandatory prefilter group are turned off, the prefilter group iin itself is still considered mandatory.

Indicium will subsequently only return information of records that would fall in one or more of the prefilters in the mandatory prefilter group, using this orred-clause.

Say that you have a mandatory exclusive prefilter group that has prefilters for Red, Green and Orange. Even when all of these prefilters are hidden, you can still not get access to Blue.

Core principle here is that visually hiding prefilters (via roles or otherwise) should not ‘increase’ the amount of data you have access to. Having an extra role granted with prefilters visible would then reduce the data you have access to, which goes against the current RBAC set-up.

Hi Anne, 

I do understand the principle, and it is correct. However the point is that there is no way to turn it off. So in case of variants you cannot ever use mandatory groups (if they don't cover the whole result-base) because in all scenario's you'll be limited to the result set of the mandatory group. 

I do think the ‘bug’ here is then that you cannot turn off a mandatory group, and I think it should be pointed out very clearly in the documentation this principle.. because to me, although I follow the principle, it's not logical to have a pre-filter group off and hidden limiting the result-set in the background. 

 

LEF - The future favors the brave. [lef.tec.br]
Anne Buit
Rank
Userlevel 7
Badge +5

Hi Freddy,

Within role rights, I don’t think there is a fitting solution where we deactivate the mandatory state when all prefilters are visually hidden.

However, I wouldn’t see a problem with marking the prefilter group as non-mandatory for a variant. This should be seen as a new feature though, we cannot shoe-horn this into the runtime components without a change in the model.

Now I generally wouldn’t endorse workarounds but in the meanwhile, if you really want the API to not limit the data, you can add a hidden 1=1 prefilter to the group so the query ends with an  or 1=1 clause.

Freddy
Userlevel 5
Badge +16

Hi Freddy,

Within role rights, I don’t think there is a fitting solution where we deactivate the mandatory state when all prefilters are visually hidden.

However, I wouldn’t see a problem with marking the prefilter group as non-mandatory for a variant. This should be seen as a new feature though, we cannot shoe-horn this into the runtime components without a change in the model.

Now I generally wouldn’t endorse workarounds but in the meanwhile, if you really want the API to not limit the data, you can add a hidden 1=1 prefilter to the group so the query ends with an  or 1=1 clause.

 

Thanks for the reply, can I read it as a point on your backlog to have a mandatory group be switched on/off so it would work with a setup that contains multiple variants? 

In my case I solved it to make the mandatory group fully inclusive. But I rather switch it off, because a multiple OR statement doesn't perform as well as completely leaving it out. 

LEF - The future favors the brave. [lef.tec.br]
Anne Buit
Rank
Userlevel 7
Badge +5

Hi Freddy,

Good to hear you managed to get it working ona fucntional level. And yes, not having the orred prefilter in this case is indeed more preformant.

Can you create a topic regarding the configurable mandatory state of prefilter groups for variants at the Ideation section? This way we can keep track of desired features from the community.

Terms & ConditionsCookie settings