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 (helpFilter for articles with at least one comment), 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.

Angelelz•12/29/23, 2:13 PM

skill issue lol

Angelelz•12/29/23, 2:15 PM

So the RQB doesn't support filtering by nested relations

Angelelz•12/29/23, 2:15 PM

You can only filter by stuff that's on your table

Angelelz•12/29/23, 2:15 PM

But you can make it work with a subquery

Angelelz•12/29/23, 2:17 PM

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•12/29/23, 2:29 PM

note: the

user

user

field is just a relation with a one-to-one relationship with the marketplaceIntegration

how im trying to make it work in drizzle:

english query:

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

Angelelz•12/29/23, 2:39 PM

jakeleventhalOP•12/29/23, 3:38 PM

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

Angelelz•12/29/23, 3:49 PM

inArray just translates to

in

in

when parsed into sql

jakeleventhalOP•12/29/23, 3:50 PM

but couldnt eq do just the same?

jakeleventhalOP•12/29/23, 3:50 PM

to compare the id to the returned id?

Angelelz•12/29/23, 3:52 PM

Yeah, but the subquery might return more than one value. You can't compare with when using a subquery

Angelelz•12/29/23, 3:52 PM

This is sql syntax, not drizzle

Angelelz•12/29/23, 3:53 PM

Did you test the query? Does it work, I just wrote from memory

jakeleventhalOP•12/29/23, 3:54 PM

i didnt yet, just trying to understand first

wouldn't the sql roughly come out to something like

how does the inArray know to map to

id

id

in the returned query

Angelelz•12/29/23, 3:55 PM

No, in sql it would be:

jakeleventhalOP•12/29/23, 4:10 PM

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

id

field of the user table? is that just determined based on the part of my schema when defining the foreign keys?

if i changed the select to or even added another field like would i expect this to break?

Angelelz•12/29/23, 4:20 PM

The syntax in drizzle is used for mapping after the db has responded with the columns so doing this wouldn't matter: . asdf is the key you'll get in the response, drizzle does that mapping for you

Angelelz•12/29/23, 4:21 PM

This on the other hand, will throw an error in the db

Angelelz•12/29/23, 4:21 PM

In this context

Angelelz•12/29/23, 4:22 PM

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.

AAngelelz This on the other hand, will throw an error in the db `.select({ id: users.id, n...

jakeleventhalOP•12/29/23, 4:23 PM

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

Angelelz•12/29/23, 4:23 PM

I don't understand this question

Angelelz•12/29/23, 4:24 PM

I guess yes?

jakeleventhalOP•12/29/23, 4:24 PM

marketplaceIntegrations.userId is a single value and the subquery reutrns 1 column of data. if i change to

then sql will throw an error because it cant compare

userId

userId

to a 2d response since it wont know which to map to (

id

id

name

name

)

Angelelz•12/29/23, 4:25 PM

right

jakeleventhalOP•12/29/23, 4:25 PM

ok, copy all. thanks

Angelelz•12/29/23, 7:04 PM

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

jakeleventhalOP•12/29/23, 7:05 PM

i just click close post. i figured that doesnt delete

Angelelz•12/29/23, 7:09 PM

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

jakeleventhalOP•12/29/23, 7:12 PM

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