Help with nested query

Hey everyone,
I have the following query and it works as expected.

const companies = await db.query.Companies.findMany({
  where: and(eq(Companies.parentCompanyId, 0), notInArray(Companies.id, existingCompanyIds)),
  with: {
    users: {
      where: eq(Users.role, 'Owner'),
      limit: 1,
      orderBy: asc(Users.createdAt),
    },
  },
});

const companies = await db.query.Companies.findMany({
  where: and(eq(Companies.parentCompanyId, 0), notInArray(Companies.id, existingCompanyIds)),
  with: {
    users: {
      where: eq(Users.role, 'Owner'),
      limit: 1,
      orderBy: asc(Users.createdAt),
    },
  },
});

I would like to improve it in order to only return the companies that have one or more users with the role 'Owner'
so, if the amount of users returned is zero, not return that company.

Is it possible? How?

Thank you

Drizzle Team•2y ago

pastilhas

Help with nested query

Hey everyone,
I have the following query and it works as expected.

const companies = await db.query.Companies.findMany({
  where: and(eq(Companies.parentCompanyId, 0), notInArray(Companies.id, existingCompanyIds)),
  with: {
    users: {
      where: eq(Users.role, 'Owner'),
      limit: 1,
      orderBy: asc(Users.createdAt),
    },
  },
});

const companies = await db.query.Companies.findMany({
  where: and(eq(Companies.parentCompanyId, 0), notInArray(Companies.id, existingCompanyIds)),
  with: {
    users: {
      where: eq(Users.role, 'Owner'),
      limit: 1,
      orderBy: asc(Users.createdAt),
    },
  },
});

Help with nested query

Similar Threads

Help with nested query

Similar Threads

Similar Threads

Similar Threads