https://www.prisma.io/ logo
Join Slack
Powered by
Hey, given I have two models defined in my schema ...
# orm-help
t
Hey, given I have two models defined in my schema with a many-to-many relationship (e.g. Property and Tag). How would I go about retrieving all Property’s that have at least all Tags with tag ids from an array?
I tried this but the results are not at all what I expect.
So in this case I would like to retrieve all Property’s that at least have all of the tags with id 1, 2 and 3.
n
Hey 👋 Can you share your property and tag schema? What response are you getting now?
t
Hey, for sure!
Copy code
model Property {
    id             Int     @id @default(autoincrement())
    imageUrl       String?
    imageAlt       String?
    beds           Int
    baths          Int
    title          String
    description    String
    formattedPrice String
    reviewCount    Int
    rating         Int
    tags           Tag[]
}

model Tag {
    id         Int        @id @default(autoincrement())
    name       String     @unique
    Properties Property[]
}
Right now the query seems to return all Property's without any tags (lol?), and all Property's that EXACTLY have the tags supplied in the tagIds array. So if there is a Property with only the tags with id 1,2 and 3.. it will return it. But if the Property has 1,2,3,4.. it wont return it.
n
Based on your use case it seems you need to use some. Here's the query that I tried:
Copy code
const result = await prisma.property.findMany({
    include: {
      tags: true,
    },
    where: {
      tags: {
        some: {
          id: {
            in: [1, 2, 3],
          },
        },
      },
    },
  });

  console.log("result", JSON.stringify(result, null, 2));
And Here's the output, both records having tags - 1,2,3 and tags-1,2,3,4 are fetched.
Copy code
result [
  {
    "id": 1,
    "imageUrl": null,
    "imageAlt": "Testing",
    "beds": 2,
    "baths": 2,
    "title": "Testing",
    "description": "Testing",
    "formattedPrice": "$100",
    "reviewCount": 10,
    "rating": 4,
    "tags": [
      {
        "id": 1,
        "name": "1"
      },
      {
        "id": 2,
        "name": "2"
      },
      {
        "id": 3,
        "name": "3"
      }
    ]
  },
  {
    "id": 4,
    "imageUrl": null,
    "imageAlt": "Testing 2",
    "beds": 2,
    "baths": 2,
    "title": "Testing 2",
    "description": "Testing 2",
    "formattedPrice": "$100",
    "reviewCount": 10,
    "rating": 4,
    "tags": [
      {
        "id": 1,
        "name": "1"
      },
      {
        "id": 2,
        "name": "2"
      },
      {
        "id": 3,
        "name": "3"
      },
      {
        "id": 5,
        "name": "4"
      }
    ]
  }
]
t
Thank you! I will check it out once I get home
👍 1
I tested it out. Unfortunately it doesn't exactly work as I want it to. This query also returns Property's that have only a subset of the tagids. So for example if a Property only contains a tag with tag id 1, it will return that record regardless of what other tag ids are in the [1, 2,3 ] array. @Nurul
2 Views
Open in Slack
PreviousNext
Powered by