Many to many with pivot data to dgraph using graphql schema

Question

I have the bellow many to many relation using a relational DB and I want to transition this to the dgraph DB.

This relation has also extra columns in the pivot table: products_stores like price, disc_price.

I have the bellow dgraph schema using graphql:

type Product {
    id: ID!
    name: String! @id
    slug: String! @id
    image: String
    created_at: DateTime!
    updated_at: DateTime!
    stores: [Store] @hasInverse(field: products)
}

type Store {
    id: ID!
    name: String! @id
    logo: String
    products: [Product] @hasInverse(field: stores)
    created_at: DateTime!
    updated_at: DateTime!
}

I am newbie to graph databases and I don't know how to define these extra pivot columns.

Any help would be greatly appreciated.

Answer 1

To model a pivot table that is only a linking pivot table holding no additional information, then you model it as you did above. However, if your pivot table contains additional information regarding the relationship, then you will need to model it with an intermediate linking type. Almost the same idea as above. I prefer these linking types to have a name describing the link. For instance I named it in this case Stock but that name could be anything you want it to be. I also prefer camelCase for field names so my example reflects this preference as well. (I added some search directives too)

type Product {
  id: ID!
  name: String! @id
  slug: String! @id
  image: String
  createdAt: DateTime! @search
  updatedAt: DateTime! @search
  stock: [Stock] @hasInverse(field: product)
}
type Store {
  id: ID!
  name: String! @id
  logo: String
  stock: [Stock] @hasInverse(field: store)
  createdAt: DateTime! @search
  updatedAt: DateTime! @search
}
type Stock {
  id: ID!
  store: Store!
  product: Product!
  name: String! @id
  price: Float! @search
  originLink: String
  discPrice: Float @search
}

The hasInverse directive is only required on one edge of the inverse relationship, if you want to for readability you can define it on both ends without any side effects

This model allows you to query many common use cases very simply without needing to do additional join statements like you are probably use to in sql. And the best part about Dgraph is that all of these queries and mutations are generated for you so you don't have to write any resolvers! Here is one example of finding all the items in a store between a certain price range:

query ($storeName: String, $minPrice: Float!, $maxPrice: Float!) {
  getStore(name: $storeName) {
    id
    name
    stock(filter: { price: { between: { min: $minPrice, max: $maxPrice } } }) {
      id
      name
      price
      product {
        id
        name
        slug
        image
      }
    }
  }
}

For a query to find only specific product names in a specific store, then use the cascade directive to remove the undesired Stock nodes (until Dgraph finished nested filters RFC in progress)

query ($storeName: String, $productIDs: [ID!]!) {
  getStore(name: $storeName) {
    id
    name
    stock @cascade(fields:["product"]) {
      id
      name
      price
      product(filter: { id: $productIDs }) @cascade(fields:["id"]) {
        id
        name
        slug
        image
      }
    }
  }
}