Hitting some issues with one to many relations and retreiving specific fields

[tsiege]

I have a model that has a one to many relationship. Franchises to Posts. Franchises can have many posts, but a post can only have on franchise. This works fine using just the schema directives, but I try to do anything to scope it down using a where clause a custom resolver I'm always returned one result and I can't retrieve any of it's fields beyond id even if I remove my custom where clause. I've tried removing pagination directives but that only works if I remove the custom resolver.

Here's my schema

  type Franchise @model(table: "franchises", pk: "id") {
    id: ID!
    name: String!
    slug: String!
    posts: [Post!]!
      @relate(
        on: [{ from: "id", to: "franchise_id" }]
        pagination: OFFSET
      )
      @many
      @paginate
  }
  type Post @model(table: "posts", pk: "id") {
    id: ID!
    slug: String!
    title: String!
    status: String!
    franchise: Franchise
      @relate(
        on: { from: "franchise_id", to: "id" }
      )
  }

Here's my resolver:

    Franchise: {
      async posts(parent, args, ctx, info) {
        const whereClause = {
          posts: {
            status: { equal: 'published' },
          }
        }
        return client.models.Franchise
          .paginate()
          .selectAll()
          .where(whereClause as any)
          .resolveInfo(info)
          .execute()
      },
    },

[danielrearden]

@tsiege Sqlmancer was developed to avoid having to write resolvers for non-root fields, particularly relationship fields. There's a couple of problems with adding this sort of resolver: A) the root field will still try to fetch everything based on your request, B) you'll hit the n+1 problem without incorporating DataLoader or some other way to do batching. Since I didn't really consider this use case before, I don't have tests that cover it either :(

I'd like to add a way to explicitly add additional where conditions like this when defining a relationship (#53). That would empower you to add extra conditions without having to write a custom resolver. The current workaround would be to use an actual view in your database or an "inline" one as shown here.

To get your current approach working, there's a few things I would do. First, take the @relate off of posts so that sqlmancer will stop trying to populate that field. Next add the @depend directive. This will let you specify any columns you might need to resolve the field yourself, namely the id.

You should also fix the order the builder methods are called. Namely, make sure you call resolveInfo first, otherwise it will override whatever else you set. We can drop the selectAll since we only want whatever columns were requested (and requestInfo will take of that). Lastly, instead of where, I would use mergeWhere to make sure that you merge the extra conditions with whatever other filters are included in the request -- using where will outright override them.

client.models.Franchise
          .paginate()
          .resolveInfo(info)
          .mergeWhere(whereClause)
          .execute()

Because you want only the posts associated with a particular franchise, I would assume your whereClause would also need to look something like this:

const whereClause = {
  posts: {
    status: { equal: 'published' },
    franchiseId: { equal: parent.id },
  }
}

To do that, you'll also need to specify franchiseId as a field on Post :/

You can give the above changes a shot and let me know if you're still seeing unexpected behavior.

[tsiege]

Hey @danielrearden this has gotten me unblocked! I figured I'd share my schema here for others so they can see what I did. It looks like I need to make sure the foreign key was cased exactly as it is in the db. So franchiseId didn't work, but franchise_id did. I guess my last question would be, am I still hitting an n+1 problem? I guess I assumed under the hood all of these calls were batched and dispatched via some sort of dataloader. As of the moment that isn't an issue for me, but I'd be happy to help brain storm some solutions.

This schema and resolvers give me these following results

  type Post @model(table: "posts", pk: "id") {
    id: ID!
    mobiledoc: String!
    slug: String!
    title: String!
    status: String!
    createdAt: String!
    publishedAt: String
    franchise_id: String @private
    franchise: Franchise @relate(on: { from: "franchise_id", to: "id" }) 
 }
  type Franchise @model(table: "franchises", pk: "id") {
    id: ID!
    name: String!
    slug: String!
    posts: [Post!]!
      @depend(on: ["id"])
      @many
      @paginate
  }

    Franchise: {
      async posts(parent, args, ctx, info) {
        const whereClause = {
          status: { equal: 'published' },
          franchise_id: { equal: parent.id },
        }
        return client.models.Post
          .paginate()
          .resolveInfo(info)
          .where(whereClause)
          .execute()
      },
    },

{
  "data": {
    "franchise": {
      "name": "test franchise",
      "posts": {
        "results": [
          {
            "status": "published",
            "title": "A Post With Every Card In It",
            "franchise": {
              "name": "test franchise"
            }
          }
        ]
      }
    }
  }