Runtime Query For Cluster Partner Selection

[pitrakou]

Hi

So I have started on my first WFA workflow, and I have it kind a working. I am now trying to limit user inputs by using a runtime queries. It's been eons since I did any SQL quering....

So the first Input box, displays a list of all clustered controllers and this works fine:

The Variable is Array1

SELECT DISTINCT(name) FROM storage.array WHERE partner_id IS NOT NULL LIMIT 1000

So the second input needs to automatically display the cluster partner, which the user can either select, or change (so not a locked input). So I have got this far, but I just can't get it to display the controller name:

SELECT DISTINCT(name) FROM storage.array

WHERE id = '${Array1}.partner_id';

So in the test window, or if you do a preview, and you actually put in the controller ID as the Array1 input it returns the name of the cluster partner, but if you select the first controller name, it displays nothing...

Has anyone managed to get this working yet?

Appreciate any help, thanks

Tony

[yaronh · Accepted solution]

Hi Tony,

Well, I think I have an answer for you, albeit with a slightly broader scope.

What I did is not present two different fields but present just one called "Pair" (Obviously your name may vary).

I created a more complex query using MySQL functions (That is something that can be done in WFA).

I used this query:

SELECT DISTINCT IF(a1.name > a2.name, CONCAT(a2.name," - ", a1.name), CONCAT(a1.name," - ",a2.name))

FROM STORAGE.array a1

JOIN STORAGE.array a2 ON a1.partner_id = a2.id

This query is a little complex but allows every pair to appear once (Due to the distinct and the lexicographical sorting).

Note the " - " - This is an artificial delimiter that allows you to split the pair string later and to assign each of the ip/name to its own variable

in a return node:

filer_a_name = splitByDelimiter($Pair," - ",0)

filer_b_name = splitByDelimiter($Pair," - ",1)

It just happens that I documented this use case and more in a preso about advanced user input queries 🙂

so I am attaching it here (See slides 8-10)

Hope that helps,

Yaron Haimsohn

WFA team

[pitrakou]

Hi Yaron

Works perfectly, thanks very much.

Tony