Database Multiple Optional Filters with Null values in AnyLogic

Question

I am trying to find the most optimal way to create conditional filtering of a Database in AnyLogic. The database, looks as follows:

What I am trying to do is adding in AnyLogic Main window 12 check boxes: P1, P2, P3, ..., T5, T6

If the user checks P1, with reference to the above table, A and B need to be displayed.
If the user checks P2, C needs to be displayed
If the user checks P1 and T2, A needs to be discplayed

In summary, it is like filtering in Excel any set of columns by "x" noting that the other cells have a null value.

To start, I used the following code to filter for P1 only (adding the entries to a collection of type String):

collectionDescription.addAll(  
selectFrom( data_base ).  
where( data_base.P1.eq("x")).  
list( data_base.Col1));

Now to filter for P2 as well, the following can be done:

collectionDescription.addAll(  
selectFrom( data_base ).  
where( data_base.P1.eq("x")).
where( data_base.P2.eq("x")).  
list( data_base.Col1));

Following the above logic, as many "where" conditions as needed can be added. However, there are so many possible combinations (e.g. P1/P2, P1/P2/P3, P1/P3, etc. you can imagine the amount of possible combinations).

So, I thought of a potential solution where I would add as many "where" conditions as there are columns, but instead of adding ".eq("x")", I would add ".eq(varP1)". If a checkbox (e.g. P1) is ticked, varP1 would be equal to "x" (varP1 being a variable of type String). But if the box is unticked, ideally varP1 should take a value that means "where anything" so that the condition would not make any impact. I did not manage to find a way to mimic this behavior.

Sorry for the long post, but I wanted to make sure the situation is as clear as possible. So am I on the right track? Any suggestions on how to fix this? Your suggestions would be highly appreciated.

Felipe Felipe · Accepted Answer · 2020-10-21T14:40:18

let's say you have a checkbox associated to a variable varP1 to define what you want... then you can do

selectFrom(db_table)
    .where(varP1 ? db_table.db_column.eq("x") : db_table.db_column.isNotNull().or(db_table.db_column.isNull()))
    .list();

where anything is what i here did as any value which is either null or not null... so if the box is checked then you will find x, otherwise you will find anything

Database Multiple Optional Filters with Null values in AnyLogic

1 Answers