Complex Queries

Sometimes you want to request something a little more complex, or several somethings in one call.

With AQL, you can use the FOR and EACH commands to perform tasks on each item on the stack or each item in a list respectively.

One place this can come in handy is when asking about asset relationships.

The RELATED function only returns related items along a single relationship. If you were interested in seeing all of the related assets, you'd need to….

1) Send a request asking for every relationship your asset has,
2) Send a request for relationship 1,
2) Send a request for relationship 2,
2) Send a request for relationship 3…

There are faster ways of doing this.

AQL queries return everything that is in the stack at the end of execution. This means you can deliberately leave multiple items in the stack, returning different pieces of information.

For example…

'TE101' ASSET 'Profile' RELATIONSHIP 'downi' RELATED will place a single list all of the assets in the profile relationship on the stack. So if we do it twice…

'TE101' ASSET 'Profile' RELATIONSHIP 'downi' RELATED 'TE101' ASSET 'Location' RELATIONSHIP 'downi' RELATED will place a two lists on the stack - the first containing all of the 'Profile' children, the second the 'Location' children.

You can name your stacks so that you can understand what each is returning.

'TE101' ASSET 'Profile' RELATIONSHIP 'downi' RELATED 'Profile' NAME 'TE101' ASSET 'Location' RELATIONSHIP 'downi' RELATED 'Location' NAME

This can be made to execute slightly faster by storing the result of the 'ASSET' call so we don't need to call it twice. You can use SET / PUT and GET functions.

'TE101' ASSET 'asset' PUT 'Profile' RELATIONSHIP 'downi' RELATED 'asset' GET 'Location' RELATIONSHIP 'downi' RELATED - this version runs very slightly faster, as it doesn't need to look up 'TE101' twice.

We've made this much better - we can now ask for a list of relationships, then build a custom query that includes them one-by-one.

1) Send a request asking for every relationship your asset has,
2) Build a custom AQL query including every relationship,
3) Send the custom query

But this still takes two calls. It would be great if we could do this in one call, without having to do anything specifically tailored to our target database.

We can bring this down to one command using EACH.

EACH loops through every item in a list.

We want to find the downstream assets for every relationship our asset has. So we will need to start with a list of every relationship present on our asset.

This is available from the RELS command.

'TE101' ASSET 'asset' PUT RELS will…

a) Find asset TE101 and store it away in the variable named 'asset', and
b) Get a list of every relationship present on that asset.

Now, we need to go through these relationships one-by-one.

'TE101' ASSET 'asset' PUT RELS [] EACH

This will run an empty function (the square brackets containing nothing) on each of the relationships in the list.

Next, we need to fill in the content of that empty function to make it actually do something though. We are interested in seeing how our asset is related along each of these relationships, so we can take the 'RELATED' function from our earlier example and put it in the list.

'TE101' ASSET 'asset' PUT RELS [ 'asset' GET 'ddown' RELATED ] EACH

This is close, but it won't actually work

Why? The documentation for RELATED may mention that it expects to see an asset list followed by a relationship list.

Right now, we are passing it a relationship list (placed there by the EACH function) and an asset list. Our parameters are around the wrong way!

We can use SWAP to flip the stack around so that the parameters are in the correct order.

'TE101' ASSET 'asset' PUT RELS [ 'asset' GET SWAP 'ddown' RELATED ] EACH

This version works - for every relationship the asset has, we are requesting a list of the items down-stream along that relationship.

But how do we know which one is which? There are two ways we can do this - either include the relationship list at the end of the call (so you know which stack item corresponds to which relationship), or name them.

We will take the 2nd approach.

By using CONCAT, we can make a piece of text which includes the name of the relationship, and the direction we are searching. For example, 'Location' fRELATIIONSHIP 'Downstream' CONCAT will result in the test 'Location Downstream'.

The problem here is that we've now consumed the first element off the stack - the relationship ID has been lost.

Or has it?

You can use 'index' GET to get the item you are iterating through with EACH. So to set the name of your list on the stack, you need to…

1) Get the relationship ID,
2) Concatenate it with the direction of flow (ie. downstream)
3) Set it as the name.

All together, that is 'index' GET 'downstream' CONCAT NAME.

So - to get every item downstream from our asset on any relationship, you can use the following…

'<assetid>' ASSET 'asset' SET RELS [ 'asset' GET SWAP 'ddown' RELATED 'index' GET ' downstream' CONCAT NAME ] EACH

This is the first half of the CONNECTIONS macro.