Get document by ref

Hi Guys and Girls

Im very much a beginner and strugle with simplest things in FQL. I have the following document, where I want to get the referenced document in orderRef.

{
  "ref": Ref(Collection("refund"), "347389659425276107"),
  "ts": 1667555446020000,
  "data": {
    "bhCode": "100",
    "trxId": "221102194809023142-",
    "artikelNummer": "TEC-18-2",
    "artikelName": "Ladecase Apple AirPods (3. Generation)",
    "amount": "4900",
    "total": "200",
    "zahlungsArt": "TWI",
    "reason": "Sonstige",
    "mitarbeiter": "JU",
    "date": "04.11.2022 - 09:50:45",
    "orderRef": "Ref(Collection(\"customers\"), 347242346904027338)"
  }
}

I tried it like in the documentation: [Ref - Fauna Documentation](https://Ref - Fauna Docs) but with no success. Is the value of orderRef wrong formatted? For example the document id is a number and not a string as in documentation…

In the end, I actually want to have both documents returned in a single query, while the order has no reference to the collection refund. But both share the same trxId in data.

Hi @FuSa1337 !

The problem appears to be that the contents of orderRef is not a reference, but a string containing FQL syntax. Had it been a ref, it would have rendered like the ref field at the top of the document.

You should be able to update the document and correct the problem with this query:

Update(
  Ref(Collection("refund"), "347389659425276107"),
  {
    data: {
      orderRef: Ref(Collection("customers"), "347242346904027338")
    }
  }
)

As for returning both documents in one query, do you have an index setup on trxId? Of the three documents, refund, order, and customer, which two documents need to be retrieved?

1 Like

Thanks for the code to update the string-ref.

So how would I write the ref in there correctly?

This is my function to create a customer:

async function postCustomer(formDataCustomerObj) {

  // create new customer
  let customer = await client.query(q.Create(q.Collection('customers'), { data: formDataCustomerObj } ))
  console.log(`${cl.green} Customer created, ${ customer.ref } ${cl.reset}`)

  // return the created Ref to use in "postOrder" as FK
  return { 
    statusCode: 201,
    body: customer.ref
  }
}

which I call like this,

let postCustomerResponse = await faunadb.postCustomer(formDataCustomerObj)
let customerRef = postCustomerResponse.body
// Send the order to DB - "orders" - with customerRef as FK
await faunadb.postOrder(orderObj, customerRef)

and use the returned ref to create the order:

async function postOrder(orderObj, customerRef) {
    
    // customerRef = Foreign Key to the order
    let orderData
    if (customerRef) {
        orderData = {
            ...orderObj,
            customerRef,
            date: getCurrentDate()
        }
    } else {
        orderData = {
            ...orderObj,
            date: getCurrentDate()
        }
    }

    return client.query(
      q.Create(
        q.Collection('orders'), { 
          data: orderData
        }
      ))
      .then(function(response) {
        console.log(`${cl.green} Order created, ${ response.ref } ${cl.reset}`)

        return { 
          statusCode: 201,
          body: response.ref
        }
      })
}

As for returning both documents in one query, do you have an index setup on trxId ? Of the three documents, refund, order, and customer, which two documents need to be retrieved?

I’ve got an Index to query all orders by trxId. I could of course create another for the refunds. I want to have the document refund and order to be joined.

In this expression, how does the customerRef value get the field name orderRef? I don’t see how this expression results in the document in your original question.

Here’s a contrived example demonstrating extracting a reference from one query and using it in another query:

;(async () => {
  var letter = await client.query(
    q.Get(q.Ref(q.Collection("Letters"), "101"))
  )
  .then((res) => res)
  .catch((err) => console.log(err))

  console.log('letter', letter)
  var ref = letter.ref
  console.log('ref', ref)

  var newLetter = await client.query(
    q.Get(ref)
  )
  .then((res) => res)
  .catch((err) => console.log(err))
  console.log('newLetter', newLetter)
})()

I’ve got an Index to query all orders by trxId. I could of course create another for the refunds.

Can you share the definition of that index? Run Get(Index("<index name>")) and paste the output here.

I want to have the document refund and order to be joined.

What data shape does “joined” mean here? Do you mean that the reference in the orderRef field should be replaced by the fetched document?

In this expression, how does the customerRef value get the field name orderRef ? I don’t see how this expression results in the document in your original question.

Ah, sorry for the confusion. In my initial example I posted a document from my refunds table. But thats the way I use the Ref I receive while creating a document, to use in another document as FK.

Can you share the definition of that index? Run Get(Index("<index name>")) and paste the output here.

{
  ref: Index("refunds_by_trx"),
  ts: 1668065368090000,
  active: false,
  serialized: true,
  name: "refunds_by_trx",
  unique: false,
  source: Collection("refund"),
  terms: [
    {
      field: ["data", "trxId"]
    }
  ],
  partitions: 1
}

What data shape does “joined” mean here? Do you mean that the reference in the orderRef field should be replaced by the fetched document?

No, I wan’t to receive both documents in a single query. So I query the refund document by trxId like the index above, and then It should return the queried refund document + the document with the same trxId in the table orders. Hope that makes sense.

Probably a separate concern, but your index definition shows active: false, which means that it is not ready for querying. Do you have many refunds documents? You might want to delete the index, wait 60 seconds for the deletion to propagate throughout the service, and then recreate it.

No, I wan’t to receive both documents in a single query.

Right. There is a single response for a query, so if you need two documents, the response shape has to be constructed appropriately. Perhaps an array like [<refund doc>, <order doc>] would work?

So, to fetch a refund document and its corresponding order document, you need to capture the refund document, extract the order document reference, and fetch that order document. For this scenario, Let is your friend:

Let{
  {
    refund: Get(Match(Index("refunds_by_trx"), "221102194809023142-")),
    orderRef: Select(["data", "orderRef"], Var("refund")),
    order: Get(Var("orderRef")),
  },
  [Var("refund"), Var("order")]
)

You don’t have to assign intermediate steps to named vars, but it makes understanding and maintaining the query a bit easier.

1 Like

Thanks @ewan think I can work with that.

Last question, independently from my code above, how would I correctly use the ref of a created document as a FK in another document?

Last question, independently from my code above, how would I correctly use the ref of a created document as a FK in another document?

If you have a reference, you can store it in a field like any other value:

var authorRef = q.Ref(q.Collection("authors"), "1234")
var book = await client.query(
  q.Create(
    q.Collection("books"), 
    {
      data: {
        title: "The Player of Games",
        author: authorRef,
        published: 1988,
        ISBN: 9780061053566
      }
    }
  )
)

For this example, I defined the reference in a JavaScript variable and then used the variable in the query. But that reference could have been inline or the result of some FQL expression.