Query engine generates wrong SQL queries for Hierarchical Data

[braska]

Here is datamodel:

model Category {
  id String @default(cuid()) @id
  name String
  parent Category? @relation(name: "Subcategories", references: id)
  subcategories Category[] @relation(name: "Subcategories", onDelete: CASCADE, references: parent)
}

Here is example of the query:

photon
  .categories
  .findOne({
    where: { id: 'cjs86oooa007w0c94qym4xqqf' },
    select: { id: true, name: true, parent: { select: { id: true } } }
  });

This query generates following SQL queries to DB (new Photon({ debug: true, log: true });):

1. "SELECT "public"."Category"."id", "public"."Category"."name" FROM "public"."Category" WHERE "public"."Category"."id" = $1 LIMIT $2 OFFSET $3", params: ["cjs86oooa007w0c94qym4xqqf",1,0]
2. "SELECT "public"."Category"."id", "public"."Category"."id" AS "__RelatedModel__", "public"."Category"."parent" AS "__ParentModel__" FROM "public"."Category" WHERE (1=1 AND "public"."Category"."parent" IN ($1)) OFFSET $2", params: ["cjs86oooa007w0c94qym4xqqf",0]

So Query Engine tries to query all categories where parent='cjs86oooa007w0c94qym4xqqf', which is completely unexpected behavior.

---

With following query:

photon
  .categories
  .findMany({
    select: { id: true, name: true, parent: { select: { id: true } } }
  });

... engine returns error: PhotonQueryError: Reason: Expected at most 1 item for 'parent', got 13

[pantharshit00]

I can reproduce this bug.

GraphQL trees that photon send to the engine so that we can easily debug in engine:

query {
  findOneCategory(where: { id: "ck5bbt33h000001mj6s6c7c0k" }) {
    id
    name
    parent {
      id
    }
  }
}

query {
  findManyCategory {
    id
    name
    parent {
      id
    }
  }
}

[braska]

I just tried to adjust schema a little bit and regenerate photon client. I renamed subcategories to smth:

model Category {
  id String @default(cuid()) @id
  name String
  parent Category? @relation(name: "Subcategories", references: id)
  smth Category[] @relation(name: "Subcategories", onDelete: CASCADE, references: parent)
}

After that I tried to run following query:

photon
  .categories
  .findOne({
    where: { id: 'cjs86oooa007w0c94qym4xqqf' },
    select: { id: true, name: true, parent: { select: { id: true }, smth: { select: { id: true } } }
  });

And all works fine. It looks like the root of problem is that relation name and field name are same.

I hope this can help you.

[braska]

It looks like the root of problem is that relation name and field name are same.

Just tried to adjust schema in following way:

model Category {
  id String @default(cuid()) @id
  name String
  parent Category? @relation(name: "ParentChildRelation", references: id)
  subcategories Category[] @relation(name: "ParentChildRelation", onDelete: CASCADE, references: parent)
}

In this case "relation name" (ParentChildRelation) and "field name" (subcategories) are different, but prisma engine generates wrong queries (like I described in first message in this issue). So the problem is not that relation name and field name are same.

After that I tried to adjust schema in following way:

model Category {
  id String @default(cuid()) @id
  name String
  parent Category? @relation(name: "Subcategories", references: id)
  children Category[] @relation(name: "Subcategories", onDelete: CASCADE, references: parent)
}

And all works fine. Looks like the problem appears only in one case: when field name === subcategories.

[mavilein]

Internal Note: I suspect that the root cause might be use of references: on both sides of the relation. Verify this.

[braska]

@mavilein already tried without references:. Unfortunately, no difference.

[pantharshit00]

I am unable to reproduce this after removing the references modifier.
First Query after the change:

Second query:

Model after modification:

datasource db {
  provider = "sqlite"
  url      = "file:dev.db"
}

generator client {
  provider = "prisma-client-js"
}

model Category {
  id String @default(cuid()) @id
  name String
  parent Category? @relation(name: "Subcategories")
  subcategories Category[] @relation(name: "Subcategories")
}

Version used: prisma2@2.0.0-preview020.2, binary version: 0150d2c09b7ddee6b866a924dea38ba157ad4980

Can you please try with the latest version just to confirm that you can still reproduce this?

[janpio]

Ping @braska

[braska]

I tried it with latest available version. Looks like issue resolved. Thanks!

[janpio]

Awesome, thanks for the update @braska!