====Complex Queries==== Sometimes you want to request something a little more complex, or //several// somethings in one call. With AQL, you can use the [[fFOR]] and [[fEACH]] 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 [[fRELATED]] 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. ===Returning Multiple Lists=== 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' [[fASSET]] 'Profile' [[fRELATIONSHIP]] 'downi' [[fRELATED]]** will place a single list all of the assets in the //profile// relationship on the stack. So if we do it //twice//... **'TE101' [[fASSET]] 'Profile' [[fRELATIONSHIP]] 'downi' [[fRELATED]] 'TE101' [[fASSET]] 'Location' [[fRELATIONSHIP]] 'downi' [[fRELATED]]** will place a //two// lists on the stack - the first containing all of the 'Profile' children, the second the 'Location' children. ===Naming=== You can name your stacks so that you can understand what each is returning. **'TE101' [[fASSET]] 'Profile' [[fRELATIONSHIP]] 'downi' [[fRELATED]] 'Profile' [[fNAME]] 'TE101' [[fASSET]] 'Location' [[fRELATIONSHIP]] 'downi' [[fRELATED]] 'Location' [[fNAME]]** ===Optimising=== 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 [[fSET]] / [[fPUT]] and [[fGET]] functions. **'TE101' [[fASSET]] 'asset' [[fPUT]] 'Profile' [[fRELATIONSHIP]] 'downi' [[fRELATED]] 'asset' [[fGET]] 'Location' [[fRELATIONSHIP]] 'downi' [[fRELATED]]** - 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 \\ ===Looping=== 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 [[fEACH]]. **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 [[fRELS]] command. **'TE101' [[fASSET]] 'asset' [[fPUT]] [[fRELS]]** 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' [[fASSET]] 'asset' [[fPUT]] [[fRELS]] [] 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' [[fASSET]] 'asset' [[fPUT]] [[fRELS]] [ 'asset' [[fGET]] 'ddown' [[fRELATED]] ] EACH** //This is **close**, but it won't actually work// Why? The documentation for [[fRELATED]] 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 [[fSWAP]] to flip the stack around so that the parameters are in the correct order. **'TE101' [[fASSET]] 'asset' [[fPUT]] [[fRELS]] [ 'asset' [[fGET]] [[fSWAP]] 'ddown' [[fRELATED]] ] EACH** This version works - for every relationship the asset has, we are requesting a list of the items down-stream along that relationship. ===Naming the Columns=== 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 [[fCONCAT]], 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' [[fCONCAT]]** 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' [[fGET]]** to get the item you are iterating through with [[fEACH]]. 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' [[fGET]] 'downstream' [[fCONCAT]] [[fNAME]]**. ===Finishing Up=== So - to get every item downstream from our asset on //any// relationship, you can use the following... **'' [[fASSET]] 'asset' [[fSET]] [[fRELS]] [ 'asset' [[fGET]] [[fSWAP]] 'ddown' [[fRELATED]] 'index' [[fGET]] ' downstream' [[fCONCAT]] [[fNAME]] ] [[fEACH]]** This is the first half of the [[mCONNECTED]] macro.