Drizzle Team•5mo ago

Where clause dependent on with relation

const news = await ctx.db.query.newsTable.findMany({
        limit: 20,
        offset: 20 * input.page,
        orderBy: (newsTable, { desc }) => [desc(newsTable.createdAt)],
        with: {
          tweets: {
            with: { pundit: true },
          },
        },
      });

const news = await ctx.db.query.newsTable.findMany({
        limit: 20,
        offset: 20 * input.page,
        orderBy: (newsTable, { desc }) => [desc(newsTable.createdAt)],
        with: {
          tweets: {
            with: { pundit: true },
          },
        },
      });

So I want to add to this query where tweets.length > 0. But I don't know how to anchor on to this tweets relation

2 Replies

noahsolomon•5mo ago

const news = await ctx.db.query.newsTable.findMany({
        limit: 20,
        offset: 20 * input.page,
        orderBy: (newsTable, { desc }) => [desc(newsTable.createdAt)],
        with: {
          tweets: {
            with: { pundit: true },
          },
        },
        where: tweets.length > 0
      });

const news = await ctx.db.query.newsTable.findMany({
        limit: 20,
        offset: 20 * input.page,
        orderBy: (newsTable, { desc }) => [desc(newsTable.createdAt)],
        with: {
          tweets: {
            with: { pundit: true },
          },
        },
        where: tweets.length > 0
      });

kinda what im thinking but this isn't the right syntax ^

Sillvva•5mo ago

You can use the exists filter and a subquery: https://orm.drizzle.team/docs/operators#exists Something like this:

const news = await ctx.db.query.newsTable.findMany({
  limit: 20,
  offset: 20 * input.page,
  orderBy: (newsTable, { desc }) => [desc(newsTable.createdAt)],
  with: {
    tweets: {
      with: { pundit: true },
    },
  },
  where: (newsTable, { exists, eq }) => exists(
    db.select().from(tweetsTable).where(eq(tweetsTable.newsId, newsTable.id))
  )
});

const news = await ctx.db.query.newsTable.findMany({
  limit: 20,
  offset: 20 * input.page,
  orderBy: (newsTable, { desc }) => [desc(newsTable.createdAt)],
  with: {
    tweets: {
      with: { pundit: true },
    },
  },
  where: (newsTable, { exists, eq }) => exists(
    db.select().from(tweetsTable).where(eq(tweetsTable.newsId, newsTable.id))
  )
});

Drizzle ORM - Filters

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

Gaming

Programming

Where clause dependent on with relation