https://www.prisma.io/ logo
Join Slack
Powered by
Hi, i have this simple SQL query ```SELECT s.id, s...
# orm-help
a
Hi, i have this simple SQL query
Copy code
SELECT s.id, s.name, s.logo_url
  FROM supplier AS s
  JOIN supplier_user su ON s.id = su."supplierId"
 WHERE su."userId" = ${id}
   AND su.active = TRUE
I tried to build this with a prisma query but i don’t get this to work. May last try was:
Copy code
return this.prisma.supplier.findMany({
  where: {
    supplierUser: {
      some: {
        active: true,
        userId: { equals: id }
      }
    }
  }, select: {
    id: true,
    name: true,
    logoUrl: true,
  }
});
can someone pleas help me to understand why and how this joined selects work?
h
Looks like you should share 
schema.prisma
. If the prisma models are correctly written, I guess your code should look like belo.
Copy code
prisma.supplier.findMany({
  where: {
    supplierUser: {
      active: true,
      userId: { equals: id }
    }
  }, select: {
    id: true,
    name: true,
    logoUrl: true,
  }
});
a
Copy code
model Supplier {
  id            String   @id @default(uuid())
  name          String
  logoUrl       String   @map("logo_url")
  updatedAt     DateTime @updatedAt @map("updated_at")
  createdAt     DateTime @default(now()) @map("created_at")
  supplierUser  SupplierUser[]

  @@map("supplier")
}

model SupplierUser {
  id              String    @id @default(uuid())
  customerNumber  String    @map("customer_number")
  supplier        Supplier  @relation(fields: [supplierId], references: [id])
  user            User      @relation(fields: [userId], references: [id])
  active          Boolean   @map("active")
  supplierId      String
  userId          String

  @@map("supplier_user")
  @@unique([supplierId, userId], name: "unique_supplier_user")
}
@Hyo this is an error i got from your code.
h
Oh I'm sorry! How about trying the other way around
Copy code
prisma.supplierUser.findMany({
  where: {
    active: true,
    userId,
    supplierId, 
  }, select: {
    supplier: {
      id: true,
      name: true,
      logoUrl: true,
    }
  }
});
I am not sure if I am getting it since I don't have good 
env
of yours 😿 But hope it helps
Just note that you can even do things like below.
Copy code
const result = await prisma.user.findUnique({
  where: { id: Number(id) },
}).posts({
  where: {
    published: false
  }
})
a
I tried this. but didn’t work. so i tried my attemt again 🙈 I added the debugging query and found out, that the query looks like this:
Copy code
this.prisma.supplier.findMany({
  where: {
    supplierUser: {
      some: {
        AND: [
          { active: true },
          { userId: { equals: id } }
        ]
      }
    }
  },
  select: {
    id: true,
    name: true,
    logoUrl: true
  }
});
Copy code
SELECT "public"."supplier"."id", "public"."supplier"."name", "public"."supplier"."logo_url"
FROM "public"."supplier"
WHERE ("public"."supplier"."id") IN (SELECT "t0"."id"
                                     FROM "public"."supplier" AS "t0"
                                              INNER JOIN "public"."supplier_user" AS "j0" ON ("j0"."supplierId") = ("t0"."id")
                                     WHERE ("j0"."active" = true AND "t0"."id" IS NOT NULL));
What i noticed it, that the engine interprets the 
userId:{equals:id}
is iterpreted as 
is not null
🤔
the id is an uuid string in this case
I found the solution for this. I need to wrap the parameter.
Copy code
{ userId:  `${id}` }
What i don’t understand is, why. the id has the type of string already.
h
Great~! I think you want to write something on the issue board
a
r
@Adrian replied to the discussion that you posted 🙂
3 Views
Open in Slack
PreviousNext
Powered by