Help with this relational query?

I am trying to get all the organizations that a member is associated with. This code is working:

await db.query.membership.findMany({
  columns: {},
  where: eq(membership.userId, sessionUser.userId),
  with: {
    organization: true
  }
});

await db.query.membership.findMany({
  columns: {},
  where: eq(membership.userId, sessionUser.userId),
  with: {
    organization: true
  }
});

But it is outputting this:

[
  { organization: { id: 19, name: "Testing123" } },
  { organization: { id: 20, name: 'test' } },
  { organization: { id: 21, name: 'Place 4012 } },
  { organization: { id: 22, name: 'Foo' } }
]

[
  { organization: { id: 19, name: "Testing123" } },
  { organization: { id: 20, name: 'test' } },
  { organization: { id: 21, name: 'Place 4012 } },
  { organization: { id: 22, name: 'Foo' } }
]

I know I could just transform this easily with JS, but I wonder if I writing the initial code wrong. I just want an array of organizations.

9 Replies

Noahh•2y ago

That looks right to me...it's a list of memberships, without any of the membership columns (because columns is {}) , and you add the organization relation on each one, so organization is set for each membership if you just want an array of organizations, why not just do

await db.query.organizations.findMany();

await db.query.organizations.findMany();

and from there you could filter or sort on relations to membership or sessionUser if you need to

CoryOP•2y ago

Well that would be selecting a lot of data

Andrii Sherman•2y ago

you can use limit or limit + offset for pagination or where for cursor pagination

CoryOP•2y ago

yea but I only want the orgs associated with the membership which is associated with the user

Noahh•2y ago

@Andrew Sherman am I right that Drizzle doesn't yet support doing where clauses on sub-query fields? For example, in this case, something like

await db.query.organization.findMany({
  where: eq(membership.userId, sessionUser.userId),
  with: {
    membership: {
      columns: {},
      with: {
        sessionUser: true
      }
    }
  }
});

await db.query.organization.findMany({
  where: eq(membership.userId, sessionUser.userId),
  with: {
    membership: {
      columns: {},
      with: {
        sessionUser: true
      }
    }
  }
});

that would be the best way within SQL I could see to do that with findMany , but if that is not currently possible, I'd just suggest mapping it in JS

Andrii Sherman•2y ago

It does https://orm.drizzle.team/docs/rqb#select-filters

Andrii Sherman•2y ago

Andrii Sherman•2y ago

example from docs you need to use callback to have access to relation fields in the table but it won't be so simple I guess it's something we need to explain more but for that we will need to explain how query is built, so you can use sql in where(not just an example from docs) we have only 1 so far

CoryOP•2y ago

ah yea an example of what the actual output is in mySQL (selectable via a little tab) would be cool! Could you help me out with writing this with the callback? Trying to figure it out but having trouble

Gaming

Programming

Help with this relational query?