Question 246998 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to filter a prisma association to have only 1 association on a 1 to many

I have a DB with posts and tags. I want to find posts that only contain 1 tag with a specific tagId and no other tags at all. The code below works, but it requires the server to filter the results manually which will make pagination difficult. Does anyone have a direct query that can do this?

I tried a groupBy on postId in PostTag, but the issue is when I search for the tagId in a where clause then all entries on the PostTag only have 1 entry which makes counting impossible.

I am using Prisma as a wrapper for a PostgresQL DB.

        const query = await prisma.post.findMany({
            where: {
                tags: {
                    some: {
                        tagId: Number(tagId),
                    }
                },
            },
            select: {
                id: true,
                tags: {
                    select: {
                        tagId: true,
                    }
                }
            },
        })
        const tagIds = query
            .filter(item => item.tags.length === 1)
            .map(item => ({ id: item.id }));

Models

model Post {
    id                     Int                         @id @default(autoincrement())
    tags                   PostTag[]
}

//Pivot Table
model PostTag {
    id           Int               @id @default(autoincrement())
    post         Post              @relation(fields: [postId], references: [id], onDelete: Cascade)
    postId       Int
    tag          Tag               @relation(fields: [tagId], references: [id])
    tag    Id    Int
}

model Factiii {
    id              Int                  @id @default(autoincrement())
    tags            PostTag[]
}
Tags:

2

Answers


  1. 0

    I think the keyword here is having which unfortunately Prisma doesn’t yet support in where clauses.

    They support having in the groupBy function, but that doesn’t allow you to check based on the count.

    You can try this, which feels like a workaround but could work in your case:

    const query = await prisma.post.findMany({
            where: {
                tags: {
                    every: { //this is the change
                        tagId: Number(tagId),
                    }
                },
            },
            select: {
                id: true,
                tags: {
                    select: {
                        tagId: true,
                    }
                }
            },
        })

    The idea here is that since a post can contain a tag only once, if you provide only 1 value for comparison (Number(tagId) in this case) it can only match posts with one tag, which is the tag you care about. if my logic is wrong, please kindly let me know

    Alternatively, you can consider doing a raw query with Prisma where you supply your own SQL code directly.

    If you’re doing that, BE CAREFUL! Read more about raw queries with Prisma to be safe against SQL injections.

    Here is the SQL code you can use for this purpose:

    SELECT p.*
FROM Post p
JOIN (
  SELECT pt.post_id
  FROM PostTag pt
  JOIN Tag t ON pt.tag_id = t.id
  WHERE t.id = 1 -- Replace with your specific tagId
  GROUP BY pt.post_id
  HAVING COUNT(*) = 1
) subquery ON p.id = subquery.post_id;
    Login or Signup to reply.
  2. 0

    you could try this from Rayan answer on this question

    await prisma.post.findMany({

    where: { tags: { some: { tag: { id: 1 } } } }
    })

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search