Extract type from SubQuery
If I have a query like this I intend to use as a subquery:
Is there any way I can extract a type from this, such like:
18 Replies
Can you show an example of how you would use such a type?
Sure! This is incomplete and non-functional (and this specific example could be done with better queries), missing
where
and joins, but something like this:
Essentially, using the subquery in an aggregate function like json_agg
to return multiple points of data per single point. I want to have a good type to pass into sql
without manually defining it, being able to infer from the articles
query, especially since in practice my queries are much larger than this. That way, articles
doesn't come back as unknown
, but as an actual typed result from the query prior.Got you, thanks for the clarification. This is a legit use case, let me see if I can help you find a workatound. We'll inevitably have to use drizzle internal types cause I don't think we have a helper for this
Amazing, thank you! I poked around a little bit but I'm definitely not familiar enough with Drizzle's internal types for this 😅
You could also build
sql
helpers with generic types for the various sql functions you use to avoid you the trouble of manually typing things every time you want to do things like json_agg
, row_to_json
, etc. I'm using something like this in a project of mine, maybe this could help?
Small warning if you go this route: generic types can get a bit messy when you want helpers to handle not only table arguments but also subqueries (Subquery | SubqueryWithSelection | WithSubqueryWithSelection
and etc.)Cool, it feels like this solution should work
@Angelelz do you know if there's a plan for drizzle to provide such helpers of their own?
Perfect coincidence as that's just what we're looking for as well. Trying to define different subqueries and merge them into a final output using json_agg. Native support would be very nice though I do have to say 😅
Yeah, there is a gist running around with this type of helpers that will eventually end up in drizzle codebase
Quick question, like I said we're looking to do a similar pattern. However I'm curious how you would pass data to that subselect. Like if you wanted to filter for a specific author in your example then adding the where clause to authors is easy. However for articles by that author that seems impossible to do?
We've been banging our heads against the wall for days now and can't find a nice solution unfortunately
@iolyd Have you by chance ever run into this usecase as well? The pattern looks so close to what we're looking for and where doing without an ORM but the final piece of the puzzle seems to be missing
Hard to test without a db I can hit, but I feel something like this should work:
In this case, the generic type of
jsonAgg
would need to be expanded a bit to account for Subquery
With modified types for the jsonAgg
helper I can confirm passing subqueries should work as expected:
Note that I haven't tested queries with aggregated non-aliased subqueries (Any[dialect]Select
) through drizzle, so I don't know if it's valid that I also handle these. But db.select().from().as()
subqueries work without problem!hi, i was casually trying to steal this code and noticed some unknown types
![No description](https://cdn.answeroverflow.com/1199452422586781716/image.png)
which version of drizzle are you using?
@Gary, el Pingüino Artefacto Those are some types I made, sorry I forgot to include them!