Prisma•3w ago

Making findMany act like an inner join?

I'm just starting out with Prisma, so apologies in advance for any stupid questions! 😉 In SQL, the query below would return one row for each row in [prefs]. Users who had no rows in prefs would not show up. Prefs with no living users would not show up.


SELECT users.name, prefs.name, prefs.value
FROM users
INNER JOIN prefs
   ON prefs.userId = users.userId
WHERE users.isDead = 0

In Prisma,


users.findMany {
   where: { equals: {isDead:0}},
   include: { prefs:true}
}

seems to return all the living users, whether they have prefs or not. If I reverse it, and do prefs.findMany, then I get all the prefs, whether they have living users or not. How do I replicate the behavior of the SQL query?

2 Replies

Prisma AI Help•3w ago

You selected the carefully hand-crafted route. A dev artisan will respond soon. Meanwhile, the #ask-ai channel awaits if you're curious!

Nurul•3w ago

Does this query help?

const prefs = await prisma.prefs.findMany({
  where: {
    user: {
      isDead: 0
    }
  },
  include: {
    user: {
      select: { name: true }
    }
  }
});

const prefs = await prisma.prefs.findMany({
  where: {
    user: {
      isDead: 0
    }
  },
  include: {
    user: {
      select: { name: true }
    }
  }
});

Gaming

Programming

Making findMany act like an inner join?

Did you find this page helpful?