Not understanding how to do nested filters with query API
I am looking to do migrate this query from prisma to drizzle
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
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
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:
english query:
find the firstmarketplaceIntegration
that has the same user id and whose user has theshop
field that is not null
why would you do inarray if it is a one-to-one relation
inArray just translates to
in
when parsed into sqlbut couldnt eq do just the same?
to compare the id to the returned id?
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 memoryi 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 queryNo, in sql it would be:
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?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.
so sql will throw an error because youre trying to compare something 1d to a 2d subquery response
I don't understand this question
I guess yes?
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
)right