$Order and $Query

Hi

I'm sure that the documentation will answer your question but if you want a short answer that explains the difference here you go.

Globals are defined as persisted sparse multidimensional arrays.

A Multidimensional array basically means that you can create an array with multiple subscripts and visually this is represented by a tree structure where you have a trunk that branches and each branch then branches and each of those branches can have many branches and so on and so on and eventually at the tips of the very outer branches you have a leaf. This tree however can also have leaves at the point where the branch branches again. 

The leaves in this analogy are your data pages i.e. a database block that contains some data.  In your application you want to go and fetch the data in those leaves.

But as in nature, some of those branches don't have leaves, it might be a new branch and a leaf is beginning to develop. Likewise some of the leaves at the join between a branch and the branches that sprout from that branch have fallen off or for whatever reason a leaf never grew at that particular intersection between a branch and the branches that branch off that branch.

So what is the most effective way of finding all of the leaves on the tree? Worse still, depending on how old that tree is we don't necessarily know how many branches on branches on branches... on branches there are.

So, if you only had $order and you were a hungry grasshopper 🦗 you would have to walk up the trunk, choose the furthest branch to the left and walk up it. Ah! success, you find a leaf. You eat the leaf. Your still hungry so you take the branch that is the furthest branch on the left of the next level of branches and eventually you reach the very top of the tree. The thinest little twig and you eat the little leaf that has just budded on that twig then you walk back down the twig and you move one twig to the right and up you climb and eat the leaf at the end of that twig and you repeat this process until you have processed each twig on that outer most branch -1. Now you move one branch to the right and up you climb and  you climb each twig on that outer most branch -1 and so on and once you have traversed every single branch on every single branch you will eventually get back to the trunk where a very hungy sparrow has been watching your progress with interest and as soon as you stumble back down the trunk, tired, dusty, full to the brim with leaf 🍃 the sparrow makes his move and eats you.

Bummer. So much effort, so little reward, the amount of leaf you ate barely replenished the energy you used to traverse the tree 🌴.

Now, if you are a smart grasshopper and you have good eyesight you remember you can hop, in fact you can hop like a grasshopper on steroids. So you bound up the trunk and you scan the branches and you spot a leaf and it is within hopping distance and so you hop to the leaf and eat it and on you go, hopping from one leafy branch to the next, ignoring all of the branches that have no leaves.

Well, that's how $query works, $query will return the next set of subscripts in your sparse array that has some data at the nth subscript level.

Of course, the grasshopper was so pleased with himself and how well he hopped that he forgot to keep his eyes open for sparrows and that sparrow that had sat patiently watching the first grasshopper traverse the tree using $order has been quietly sitting (do sparrows sit or clutch?) watching you bound effortlessly from ;leafy branch to leafy branch and when the grasshopper eventually gets back to the trunk the sparrow eats him too.

Moral of the story: I'd rather be a sparrow than a grasshopper and if I am a developer and I have a large sparse array I will use $query rather than $order.

Nigel