Attempting to get a Joined Subquery query into drizzle crud api
I was trying to get the following native sql query into a drizzle query
according to the docs for subqueries and aggregations I did the following
but it seems I can't join a subquery in drizzle sql. Is there another way to do it (besides a native sql query where I'll have to somehow type the results).
Thnx
P.S.: Can I call
CONCAT (firstname, ' ', surname)
or firstname || ' ' || surname
from within the drizzle crud query ?23 Replies
What specific issue are you running into when joining the subquery?
For the final bit, you’d need to use the
sql
template string, interpolating firstname and surname (from your schema objects) in the correct place in the query stringI think the issue is with converting the Select into a Subquery. Append an
.as
at the end of sq
, this mirrors exactly what you have in the native SQL
Wow .. that was it ... I totally overlooked the
.as('name')
. Thank you so much. Now typescript stopped yelling at me ... need to try if the query works as expected - but I guess I'll figure it out from here if something is not working right. Thank you again 👍🏽
Drizzle is simply awesome ❣️ Powerful query builder, that is easy to get used to + fine grained schema definition / migration without extra build steps.
The only thing I couldn't figure out yet is how to do a ORDER BY xyz DESC NULLS LAST
The common ORDER BY xyz DESC
is trivial.orderBy()
method accepts sql magic operator
You could do works perfectly, thank you 🙂
As stated at https://orm.drizzle.team/docs/sql#sqlt the T in sql<T> has not effect on the resulting type
only difference is that drizzle can introspect into e.g.
(property) numberOfLogins: SQL.Aliased<number>
.
I wished drizzle would cast/map the types for primitive values like number etc.
What is the recomended way to coerce the results to the right types? zod ?Magical sql operator 🪄 - DrizzleORM
Drizzle ORM | %s
for example: As of now we can only get a string representing the count - if we want to do something like:
Select count (*) from X
in drizzleYou probably want mapWith https://orm.drizzle.team/docs/sql#sqlmapwith
Magical sql operator 🪄 - DrizzleORM
Drizzle ORM | %s
Yes that works ... thank you 👍🙂
for count and
which seems a little verbose - but ultimately works. Even though somehow it seems to still stringify the Date - at least it looks like it when logging to the console.
.mapWith(loginlog.createdAt)
doesn't make any difference here ... contrary to the count.
Why not use the T - type in sql<T> to map the result type too? > Would make it less verbose.
btw. I just double checked in an old prisma project. Prisma infers the correct types from the database even on a prisma.$queryRaw native sql query. For sure that is no reason to go back to it - but I guess that there might be potential to infer types from the query results. So far drizzle seem to somehow stringify all results.If you use mapWith you don't need to give the type <number>
Giving it the type like this <number> is just a type casting, only at compile time
mapWith will actually run at runtime the function you pass to it
Ahh nice ... this also works with the max(Date) query I used 👍
I see ... I'll try to play with it
My bet is that this is actually the DB driver, not Drizzle
IIRC, if you're using
sql
, it just passes back whatever the driver gives you
Using the mysql2 driver for example, I get a number back from a count
And if you provide loginlog.createdAt
directly (without the max, etc), without the max, Drizzle should map that itself as long as you've configured the column type correctlyI'm using postgres right now ... I can/will also try with pg. Using .mapWith(Number) does the right thing for now.
Yeah different postgres drivers may have different behaviors
That's just one thing to consider when weighing the pros and cons of a driver
Yes I think it does ... need to double check ... but in that case I want the latest date only so needs to be max(date)