Cannot implement many-to-many relations with explicit custom relation service/table

[mhaidarhanif]

Bug report

Describe the bug

Apparently, Kontenbase might not able to implement proper many-to-many relations with explicit custom relation service/table yet. Please check if the intended use is correct or not, and how to resolve it with Kontenbase's current behavior.

For a specific example, implementing a proper Shopping Cart (/carts) where each of the Product (/products) or Cart Product (/cart-products) is added in it that can have a quantity field.

This is different from the implicit many-to-many relations in a case of a Blog Post (/posts) and Category (/categories) because there's no need for explicit custom relation service.

To reproduce and expected behavior

Here's the expected model of the data, inspired by this Many-to-many relations on Prisma Docs

Try to implement this data model. Because Kontenbase doesn't currently support a way to view the data model at a glance, we're using a Prisma schema syntax.

As a reference, it's currently implemented in the Kontenbase project with id 634d7d2040f5380221733a1e or API key 468ad784-020f-4e5b-8c8a-1fd44c4d10a5.

# /products
model Product {
  _id    String
  name   String
  price  Decimal
  carts  CartsProducts[]
}

# /carts
model Cart {
  _id           String
  totalPrice    Decimal
  cartProducts  CartsProducts[]
}

# /carts-products
model CartsProducts {
  _id       String
  quantity  Int     # this is the most important part, so this model/service has to be explicit
  product   Product
  cart      Cart
}

Examples

Example data of the Product, Cart, and CartProduct.

Product

{
  "_id": "634d8055dadc42808a40f384",
  "carts": [
    {
      "_id": "634d852cdadc42808a40f38b",
      "quantity": 1
    }
  ],
  "name": "Book of Wisdom",
  "price": 120000
}

Cart

{
  "_id": "634d80f7dadc42808a40f385",
  "products": [
    {
      "_id": "634d852cdadc42808a40f38b",
      "quantity": 1
    }
  ],
  "totalPrice": 0
}

CartsProducts

{
  "_id": "634d852cdadc42808a40f38b",
  "cart": [
    {
      "_id": "634d80f7dadc42808a40f385",
      "totalPrice": 0
    }
  ],
  "product": [
    {
      "_id": "634d8055dadc42808a40f384",
      "name": "Book of Wisdom",
      "price": 120000
    }
  ],
+  "quantity": 2  // This is why we need an explicit relation service
}

Unfortunately, when getting the Cart data, it cannot resolve or lookup the relation fields inside it, other than the quantity field:

{
  "_id": "634d80f7dadc42808a40f385",
  "products": [
    {
      "_id": "634d852cdadc42808a40f38b",
      "quantity": 2
    }
  ],
  "totalPrice": 240000
}

What's expected will be similar to the data modeling, we can retrieve the product details within the cart.

{
  "_id": "634d80f7dadc42808a40f385",
  "cartProducts": [
    {
      "_id": "634d852cdadc42808a40f38b",
      "quantity": 2,
+      "product": {
+	    "_id": "634d8055dadc42808a40f384",
+	    "name": "Book of Wisdom",
+	    "price": 120000
+	  }
    }
  ],
  "totalPrice": 240000
}

Additional context

To workaround the issue, we can also do the fetching more than once:

1. To retrieve the cart object first.
2. To loop over the cartProducts then fetch each of the _id to retrieve the product details.

But this will result in too much inefficient data fetching. Imagine if there are more than 2-5 products in the cart, it will result in fetching 3-6 times just to get the right data.

I think the Kontenbase team can also try to implement a proper shopping cart feature with a quantity for each of the products in the cart, to test if the assumption in this issue is correct or wrong.