Multiple .where() calls with $dynamic not working
When adding multiple
.where() calls to a query created with .$dynamic() only the last .where() call gets applied to the resulting query.
I put together the following example that adds two .where() calls but only the last one is applied. You can see the resulting query in the console.
https://codesandbox.io/p/sandbox/charming-herschel-8c9rk6?file=%2Findex.js%3A7%2C119 Replies
That is expected behavior. The query builder was designed to match the sql syntax.
Although you can use the
.where() method (or any other) repeatedly, it's the last one that will get applied.Isn't the motivating example in the release notes basically suggesting you can call where multiple times when using dynamic queries though?
Also the quote: "However, it becomes a problem when you want to build a query dynamically, i.e. if you have a shared function that takes a query builder and enhances it. To solve this problem, Drizzle provides a special 'dynamic' mode for query builders, which removes the restriction of invoking methods only once"
I expected it to allow for what is in the code sandbox (without having tried it yet)
Yes, the idea is to allow users to build queries dynamically. The withPagination example in the docs builds the query dynamically, and doesn't require calling methods more than once or rely on untested/undefined behavior.
But the notes for the dynamic feature state: "which removes the restriction of invoking methods only once."
This is after showing the example where there is an error when the code has a where followed by another where
Am I misunderstanding what the dynamic feature is able to do? From what I understand from the notes it should allow for you to call where multiple times and have it work like the original question here has code for in that code sandbox link
You are not misunderstanding it. You can call methods more than once but it is not going to combine them together with an and or an or
The correct way of doing what you want, it to pass an
and() or an or() function, and build your logic that way
Instead of trying to construct an and query by chaining together wheres, you can push expressions to an array, and then passing that array to an and function.
Again, drizzle is trying to be as close as possible to sql.
This is how you do it: https://codesandbox.io/p/sandbox/nifty-river-kjfdxx?file=%2Findex.js%3A38%2C60
You might want to use or, I don't know. But this way is a clearerYeah I've been doing something similar for a while now. I think I was confused by the release notes and the documentation for the dynamic function. It might just be me or it may be something people also assume is different due to the motivating example in both of these having a chained where and people expecting it to work when you use a dynamic query (as it often does in other ORMs and query builders or with something similar like andWhere being a function)
I think in reality it just lets you overwrite (or add if they don't exist) parts to the query after the first definition of it.
Thanks for your time though, I'd have been confused about this next week otherwise 😄
I thought it was a little strange to have the motivating example given and then the example being withPagination and not something that builds on the double where function call. It makes more sense now 😁
I think the new types they implemented to avoid the double calling of methods is genius
The runtime stayed the same, simple and fast
But they were so restrictive, that those type of builder functions would not have been possible, that's why the included the
$dynamic() methodI just had this issue, and found this post. Is there any way we can do this sample with associated typescript types for safety?
Thank you so much. This should be somewhere in the documentation