Hi @Bror! First, It would be a lot easier to follow if you formatted/indented the code. you can use triple “back-ticks” (what I call them) to create a code block. Or hit the button in the editor which indents everything by 4 spaces to do the same thing. In any case!!..
Exactly! Also, the default values for any index is the Ref. What you are doing here is fine – it’s more explicit. But you don’t technically need it.
While “username” might be a bit of a misnomer, your item documents save the full account Ref. This is good. Now the output of the first index, account_by_usertype, matches the input of the second index, items_by_username, and the join works!!
FQL does not resolve any references automatically. It makes you write explicitly and exactly what you want. This is great some times and a pain some times!
As you can see, the FQL Join function is not an SQL join. FQL Join is good for getting the results at the end. In this case, the items. Whether or not this is a good approach depends on your data, the relationships (one-to-one vs one-to-many, and direction), and how you want it returned.
What you did is good for getting a list of all relevant items, and then duplicating the account data as a secondary thing.
Or, you could do the join just like you specified, and then merge the account document in the item results.