Joining two collections by ref returns nothing?

[Bror]

My goal is to do a query by joining two collections so I get a list of items and also which user created the item.

So, I have followed the e-com tutorial:

E-commerce

and:

Fauna Query Language for SQL users

In SQL it would look like:
SELECT e.* FROM emp e, dept d
WHERE e.deptno = d.deptno
AND d.dname = “SALES”;

In my collections I have assumed that the PK in accounts is ref then the ref to the PK in items is called username.

Built two indexes:

Index #1 (Collection: accounts, Term: usertype, Value: ref)

CreateIndex({
name: “account_by_usertype”,
source: Collection(“accounts”),
terms: [{ field: [“data”, “usertype”] }],
values: [{ field: [“data”,“ref”] }]
})

Username in items is a ref to accounts…

Index #2 (Collection: items, Term: username)

CreateIndex({
name: “items_by_username”,
source: Collection(“items”),
terms: [{ field: [“data”,“username”] }]
})

Then I run:

Map(
Paginate(
Join(
Match(Index(“account_by_usertype”), “vendor”),
Index(“items_by_username”)
)
),
Lambda(“X”, Get(Var(“X”)))
)

Result:

{ data: }

Nada! Why is it empty?

[Bror]

Ok, I changed:

CreateIndex({
name: “account_by_usertype”,
source: Collection(“accounts”),
terms: [{ field: [“data”, “usertype”] }],
values: [{ field: [“ref”] }] <<---- here: just ref
})

Now I get the items, but no names from the account collection, only refs:

{
data: [
{
ref: Ref(Collection(“items”), “296574686213440000”),
ts: 1619096368370000,
data: {
name: ‘Poke Bowl’,
username: Ref(Collection(“accounts”), “296572848587145728”),
category: ‘fisk’,
foodType: ‘lax’,
price: ‘130’,
description: ‘En extremt matig rätt med lax och en massa tort ris.’
}
},
{
ref: Ref(Collection(“items”), “296575155996459520”),
ts: 1619097123550000,
data: {
name: ‘Hamburger med jalapeno’,
username: Ref(Collection(“accounts”), “296575027816432128”),
category: ‘kött’,
foodType: ‘hamburger’,
price: ‘190’,
description: ‘Klassikern, The hamburger, 1 kg, drypande malet kött.’
}
}
]
}

Solved by adding:

Map(
Paginate(
Join(
Match(Index(“account_by_usertype4”), “vendor”),
Index(“items_by_username”)
)
),
Lambda(“X”, Let({item: Get(Var(“X”)), account: Get(Select([‘data’, ‘username’], Var(“item”)))}, {item: Var(“item”), account: Var(“account”)}))
)

I have no idea if this is the most economic or efficient query…

[ptpaterson]

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!!..

@Bror

CreateIndex({
  name: "account_by_usertype",
  source: Collection("accounts"),
  terms: [{ field: ["data", "usertype"] }],
  values: [{ field: ["ref"] }] <<---- here: just ref
})

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.

@Bror

CreateIndex({
  name: "items_by_username",
  source: Collection("items"),
  terms: [{ field: ["data", "username"] }],
})

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.

Map(
  Paginate(Join(
    Match(Index('account_by_usertype4'), 'vendor'), 
    Index('items_by_username')
  )),
  Lambda(
    'itemRef',
    Merge(
      Get(Var('itemRef')),
      {
        username: Get(Select(['data', 'username'], Var('item')))
      }
    )
  ),
)

Or, you could skip the join and just merge all of the items into the account documents

Map(
  Paginate(
    Match(Index('account_by_usertype4'), 'vendor')
  ),
  Lambda(
    'accountRef',
    Merge(
      Get(Var('accountRef')),
      {
        items: Paginate(Match(Index('items_by_username')), Var('accountRef'))
      }
    )
  ),
)

I expect all three of these queries would cost similarly in Read Ops, though maybe the last could save the size of data coming over the wire.

[Bror]

Thanks for the clarification! I suspected that there was more to it then I first anticipated.