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•11mo 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

jakeleventhalOP•11mo 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•11mo 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)))
  )
})

jakeleventhalOP•11mo ago

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

Angelelz•11mo ago

inArray just translates to in when parsed into sql

jakeleventhalOP•11mo ago

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

Angelelz•11mo 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

jakeleventhalOP•11mo 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•11mo ago

No, in sql it would be:

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

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

jakeleventhalOP•11mo 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•11mo 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.

jakeleventhalOP•11mo ago

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

Angelelz•11mo ago

I don't understand this question I guess yes?

jakeleventhalOP•11mo 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•11mo ago

right

jakeleventhalOP•11mo ago

ok, copy all. thanks

Angelelz•11mo ago

Do you delete the thread? I would say you should let em be, this can help somebody in the future

jakeleventhalOP•11mo ago

i just click close post. i figured that doesnt delete

Angelelz•11mo ago

Got you, I honestly don't know how discord word too much

jakeleventhalOP•11mo ago

me neither. but i just closed the post again and searched. verified it shows up

Gaming

Programming

Not understanding how to do nested filters with query API