Hi i tried to connect two collections with common field CustomerID using Join but it is returning a data empty array.How to resolve it?

Help
, , , ,
1

Hi i have created two collections Orders and Customers.For time being i’m pasting document’s of both collections with same CustomerID which i need to join.

Orders Collection:

{
  "ref": Ref(Collection("Orders"), "366856354920398928"),
  "ts": 1686123836000000,
  "data": {
    "OrderId": 10308,
    "CustomerID": 2,
    "OrderDate": Date("1996-09-18")
  }
}

Customers Collection:

{
  "ref": Ref(Collection("Customers"), "366856746958848080"),
  "ts": 1686123551520000,
  "data": {
    "CustomerID": 2,
    "CustomerName": "Ana Trujillo Emparedados y helados",
    "ContactName": "Ana Trujillo",
    "Country": "Mexico"
  }
}

I have created the indexes for both collections by CustomerID term using below queries.

Index Creation by CustomerID for Customers Collection:

CreateIndex({
  name: "customers_by_customerId",
  source: Collection("Customers"),
  terms: [{ field: ["data", "CustomerID"] }]
})

Index Creation by CustomerID for Orders Collection:

CreateIndex({
  name: "orders_by_customerId",
  source: Collection("Orders"),
  terms: [{ field: ["data", "CustomerId"] }]
})

I have used the below query for joining above two collections.

Map(
  Paginate(
    Join(
      Match(Index("customers_by_customerId"), 2),
      Index("orders_by_customerId")
    )
  ),
  Lambda(["customerRef", "orderRef"], {
    customer: Get(Var("customerRef")),
    order: Get(Var("orderRef"))
  })
)

Output for the above query is data key pointing to empty array.


Map(
  Paginate(
    Join(
      Match(Index("customers_by_customerId"), 2),
      Index("orders_by_customerId")
    )
  ),
  Lambda(["customerRef", "orderRef"], {
    customer: Get(Var("customerRef")),
    order: Get(Var("orderRef"))
  })
)

{
  data: []
}

What was the reason for getting a data key with empty array and how to resolve it?

2

Hi @Srinivasa_Reddy_Chal welcome! :wave:

Since you have not provided values to your customers_by_customerId Index, it returns the document Ref.

Paginate(
  Match(Index("customers_by_customerId"), 2)
)

{
  data: [Ref(Collection("Customers"), "366904489736667216")]
}

You need to make sure that the output (values) of the first index is compatible with the input (terms) of the second index.

In order to return an object with both customer and order info, you do not want to use the Join function. FQL Join is not SQL JOIN. You can simply use the Map operation to achieve what you want.

Additionally, you are indexing one the field CustomerId but storing the field CustomerID

Update your index

CreateIndex({
  name: "orders_by_customerId",
  source: Collection("Orders"),
  terms: [{ field: ["data", "CustomerID"] }] // `ID` not `Id`
})

Query without Join

Let(
  {
    CustomerID: 2,
    Customer: Get(Match(Index("customers_by_customerId"), Var("CustomerID"))),
  },
  Map(
    Paginate(
      Match(Index("orders_by_customerId"), Var("CustomerID"))
    ),
    Lambda(["OrderRef"], {
      customer: Var("Customer"),
      order: Get(Var("OrderRef"))
    })
  )
)

{
  data: [
    {
      customer: {
        ref: Ref(Collection("Customers"), "366904489736667216"),
        ts: 1686166238430000,
        data: {
          CustomerID: 2,
          CustomerName: "Ana Trujillo Emparedados y helados",
          ContactName: "Ana Trujillo",
          Country: "Mexico"
        }
      },
      order: {
        ref: Ref(Collection("Orders"), "366904474323648593"),
        ts: 1686166223745000,
        data: {
          OrderId: 10308,
          CustomerID: 2,
          OrderDate: Date("1996-09-18")
        }
      }
    }
  ]
}
3

Also, FQL v10 is now in public beta! Try the new dashboard or view our blog and docs to learn more.

With FQL v10 the query is a little easier to understand, in my opinion.

let customerID = 2

let customer = Customers.byCustomerID(customerID)

Orders.byCustomerID(customerID)
  .map(order => {
    customer: customer,
    order: order
  })

You can add v10 indexes onto your collections like this

Customers.definition.update({
  indexes: {
    "byCustomerID": {
      "terms": [{"field":"CustomerID"}]
    }},
})

Orders.definition.update({
  indexes: {
    "byCustomerID": {
      "terms": [{"field":"CustomerID"}]
    }},
})
4

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.