Hi All,
I have a table which has over 10 million rows. I want to filter on a column which is categorical and i want the user to select multiple categories right at the start of the query.
I don’t want the DB Reader node first since that takes in a lot of time.
How do i add a list of my choices as a flow variable to my condition in the DB row filter?
For example:-
My DB Table selector has a custom query select a,b,c,d from xyz;
then DB row filter has a condition of b = ‘1234’ set
i am connecting a string input widget and i am passing '1234,‘5678’ to this condition but its not working
technically, it should be b in (‘1234’,‘5678’)
Hi r_jain, I couldn’t work out if you just had a typo in your example of what you are passing, as there appears to be a quote missing.
I have attached a sample workflow showing ways to the IN list using a flow variable. In one case, where the list is numerics, and in the other where it is strings.
For the user input, I have added a component that you may find useful. It takes a list of items and turns them into an array variable. This means the user doesn’t have to put all the quotes in, so it removes that obstacle. The array variable returned by the component is then turned into either a concatenated comma-separated list if it is numerics, or alternatively into a quoted-comma-separated list, and an additional String Manipulation node then adds on the additional quote at beginning and end.
Hi @ipazin, yet another useful node that I wasn’t aware of
Is there an in-database equivalent, so that if @r_jain is building using DB Table Selector and DB Row Filter and so on, the IN clause can be included as part of the in-database query?
The only way I can see of including the IN clause in-database rather than as the final reader is to include the IN clause in the DB Table Selector custom query (where I have the DB Query Reader in my above demo) using a flow variable.
Don’t think so. Possibly DB Row Filter but that one doesn’t feature IN clause and it’s hard to control with flow variables. Instead of DB Table Selector I would use DB Query node as it’s more intuitive and easier to document flow
btw @ipazin. it’s good to see you back as a regular on the forum again!
The only thing I have against the DB Query Node is that I don’t have the option of attaching it directly to a database connector… so I end up just attaching a Table Selector to the db connection and bunging the query in there (with a suitable annotation of course! )
Think this has now been done sufficiently “IN” depth…
This is great. Thanks! I tried the parametrized DB query reader which is sort of similar like DB looping.
In the empty table creator i was pushing the entire string in one row since i want the user to list down their preferences using a string widget.
Is there a way to get multiple options pushed onto the table creator using string widget.? The numbers can have alphabets so i would need string for the same.
Yes you can use the String Widget and generate an input to the DB Looping Node, if that’s what you mean. You don’t use a Table Creator for it as you just need to generate an input table:
