How do you write a query to get the relations data together with a JOIN or a SUBQUERY?

I need to run something like:

SELECT * FROM POST p
WHERE p.ID IN (SELECT post_ID FROM ANOTHER_TABLE WHERE post_category = 'whatever')

SELECT * FROM POST p
WHERE p.ID IN (SELECT post_ID FROM ANOTHER_TABLE WHERE post_category = 'whatever')

We could also achieve the same result using a JOIN. However, I want to take advantage of the relations I have created in my schema to retrieve the actual data related to my top table. As far as I can read on the docs, to get the relations data, you have to use 'findMany' or 'findFirst'

const posts = await db.query.posts.findMany({
  with: {
    comments: true,
  },
});

const posts = await db.query.posts.findMany({
  with: {
    comments: true,
  },
});

I don't know how to make a JOIN with this syntax neither how to specify a subquery Can someone help please?

1 Reply

Angelelz•13mo ago

You can use Joins as well with the crud api: https://orm.drizzle.team/docs/joins

Joins [SQL] - Drizzle ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Gaming

Programming

How do you write a query to get the relations data together with a JOIN or a SUBQUERY?