Suggestion: Expose join API on Builder Class

[tsiege]

I've mentioned in https://github.com/danielrearden/sqlmancer/issues/132, I've now come across multiple instances where I need to alter plain one to many or many to many statements. A scenario will arise where I'd like to have a connection of one type to another, but can only return data from my API if those have a certain status. This is easy to alleviate with a simple where clause to a sql statement, which I can do by manually creating the resolver using sqlmancer models for a one to many relationship. However, for many to many there is no such easy solution in the API. Since under the hood sqlmancer is making these sql statements, I'm wondering how easy would it be to expose this API as public methods on models?

[danielrearden]

Thanks for opening this issue :)

This is something I've also been wrestling with. The core of the problem is that the library currently has no concept of where a foreign key resides. It simply captures the necessary information needed for the join condition (i.e. match this column on table A with this column on table B). The fix would be to identify individual relationships between models like how traditional ORM libraries do (belongsTo, hasMany, etc.). But that sort of thing adds even more complexity to the @relate directive and it's already a bit hard to grok. That said, I'm down to adding this if we can figure out a palpable way of handling it through directives... maybe by breaking up @relate into individual directives like @hasMany, @hasOne, @belongsTo and @belongsToMany like Lighthouse does 🤔

As a workaround, for many-to-many relationships, you can just add a model to the schema that represents the join table and then hide it with the @private directive. For all other relationships, joining two models is just a question of updating the correct column on one of the models.

[tsiege]

That makes sense to me. I've been digging through the source code myself, and I wonder if it'd easier to just change the @relate API to add a field like relation or something along those lines to describe the relationship. So if you have the following in your schema define say a many to many.

  type Post @model(table: "posts", pk: "id", include: ["id"]) {
    id: ID!
    authors: [Author!]!
      @relate(
        relation: "hasMany"
        through: "posts_authors"
        on: [{ from: "id", to: "post_id" }, { from: "author_id", to: "id" }]
        pagination: OFFSET
      )
      @many
      @paginate
    franchise: Franchise @relate(
      relation: "belongsTo"
      on: { from: "franchise_id", to: "id"}
     )
  }

As for the work around. Do you have an example of how the schema and resolver would look for a join table and the model it's trying to join through? Having a bit of difficulty figuring out how to make it work

[danielrearden]

type Post @model(table: "posts", pk: "id", include: ["id"]) {
  id: ID!
  authors: [Author!]!
    @relate(
      through: "posts_authors"
      on: [{ from: "id", to: "post_id" }, { from: "author_id", to: "id" }]
      pagination: OFFSET
      )
    @many
    @paginate
}

type Author @model(table: "authors", pk: "id", include: ["id"]) {
  id: ID!
  posts: [Post!]!
    @relate(
      through: "posts_authors"
      on: [{ from: "id", to: "author_id" }, { from: "post_id", to: "id" }]
      pagination: OFFSET
      )
    @many
    @paginate
  body: String
}

type PostAuthor @model(table: "posts_authors", pk: "id") {
  postId: ID
  authorId: ID
}

type Mutation {
  writePost(input: WritePostInput!): Post
}

input WritePostInput {
  body: String!
}

const resolvers = {
  Mutation: {
    writePost: (root, args, ctx, info) => {
      const postId = await Post.createOne({ body: args.input.body }).execute()
      const authorId = ctx.userId

      await PostAuthor.createOne({ postId, authorId })

      return Post.findById(postId).resolveInfo(info).execute()
    }
  }
}