Not understanding how to do nested filters with query API

I am looking to do migrate this query from prisma to drizzle

await prisma.marketplaceIntegration.findFirst({
  where: {
    user: { shop: { not: null } }, // this field is a nullable string type (not referencing any table)
    userId: userId
  }
});

await prisma.marketplaceIntegration.findFirst({
  where: {
    user: { shop: { not: null } }, // this field is a nullable string type (not referencing any table)
    userId: userId
  }
});

I found this related thread (https://discord.com/channels/1043890932593987624/1166190702984708126), but it wasn't quite clear how to translate this to my query

Discord

Discord - A New Way to Chat with Friends & Communities

Discord is the easiest way to communicate over voice, video, and text. Chat, hang out, and stay close with your friends and communities.

20 Replies

Angelelz•9mo ago

skill issue lol So the RQB doesn't support filtering by nested relations You can only filter by stuff that's on your table But you can make it work with a subquery I'm not super familiar with the prisma api to help you translate, if you can show me the raw query or your attempt to make it work in drizzle, I can help you out

jakeleventhal•9mo ago

note: the user field is just a relation with a one-to-one relationship with the marketplaceIntegration how im trying to make it work in drizzle:

await db.query.marketplaceIntegrations.findFirst({
  where: and(
    isNotNull(marketplaceIntegrations.user.shop),
    eq(marketplaceIntegrations.userId, userId),
  )
})

await db.query.marketplaceIntegrations.findFirst({
  where: and(
    isNotNull(marketplaceIntegrations.user.shop),
    eq(marketplaceIntegrations.userId, userId),
  )
})

english query:

find the first marketplaceIntegration that has the same user id and whose user has the shop field that is not null

Angelelz•9mo ago

await db.query.marketplaceIntegrations.findFirst({
  where: inArray(
    marketplaceIntegrations.userId,
    db
      .select({ id: users.id })
      .from(users)
      .where(and(eq(users.id, userId), isNotNull(users.shop)))
  )
})

await db.query.marketplaceIntegrations.findFirst({
  where: inArray(
    marketplaceIntegrations.userId,
    db
      .select({ id: users.id })
      .from(users)
      .where(and(eq(users.id, userId), isNotNull(users.shop)))
  )
})

jakeleventhal•9mo ago

why would you do inarray if it is a one-to-one relation

Angelelz•9mo ago

inArray just translates to in when parsed into sql

jakeleventhal•9mo ago

but couldnt eq do just the same? to compare the id to the returned id?

Angelelz•9mo ago

Yeah, but the subquery might return more than one value. You can't compare with = when using a subquery This is sql syntax, not drizzle Did you test the query? Does it work, I just wrote from memory

jakeleventhal•9mo ago

i didnt yet, just trying to understand first wouldn't the sql roughly come out to something like where marketplaceIntegrations.userId in [{id: 'asdf'}] how does the inArray know to map marketplaceIntegrations.userId to id in the returned query

Angelelz•9mo ago

No, in sql it would be:

where marketplaceIntegrations.userId in (select id from users ....)

where marketplaceIntegrations.userId in (select id from users ....)

jakeleventhal•9mo ago

i guess what im getting as is wouldnt that sub query return a table with a column name "id" and rows for each user? how does it know userId in the one table is associated with the id field of the user table? is that just determined based on the references part of my schema when defining the foreign keys? if i changed the select to .select( {asdf: users.id} ) or even added another field like .select({ id: users.id, name: users.name }) would i expect this to break?

Angelelz•9mo ago

The syntax in drizzle is used for mapping after the db has responded with the columns so doing this wouldn't matter: .select( {asdf: users.id} ). asdf is the key you'll get in the response, drizzle does that mapping for you This on the other hand, will throw an error in the db .select({ id: users.id, name: users.name }) In this context

i guess what im getting as is wouldnt that sub query return a table with a column name "id" and rows for each user? how does it know userId in the one table is associated with the id field of the user table? is that just determined based on the references part of my schema when defining the foreign keys?

This is how sql works, I don't know how to respond to this.

jakeleventhal•9mo ago

so sql will throw an error because youre trying to compare something 1d to a 2d subquery response

Angelelz•9mo ago

I don't understand this question I guess yes?

jakeleventhal•9mo ago

where marketplaceIntegrations.userId in (select id from users ....) marketplaceIntegrations.userId is a single value and the subquery reutrns 1 column of data. if i change to where marketplaceIntegrations.userId in (select id, name from users ....) then sql will throw an error because it cant compare userId to a 2d response since it wont know which to map to (id or name)

Angelelz•9mo ago

right

Gaming

Programming

Not understanding how to do nested filters with query API