search

MechanicalRabbit / OHDSICohortExpressions.jl

reimplementation of OHDSI's Circe JSON->SQL compiler
Other
7 stars 1 forks source link

[Feature Request] Have `translate` Return a SQLNode #2

Closed TheCedarPrince closed 2 weeks ago

TheCedarPrince commented 2 years ago

A need that I have would be for translate to return a FunSQL SQLNode rather than a rendered SQL query. The reason why I want this functionality is so that I can do something like this:

translate(cohort, [...]) |> Group(Get.column_of_interest) |> Select(Get.column_of_interest, Agg.count())

And so on. Basically, I want to compose the cohort expression with downstream further sub-queries formed by FunSQL. Thanks!

xitology commented 2 years ago

With the current implementation, It would be difficult to fix this issue. This is because translate(), in general, returns not just one, but a sequence of SQL statements, which may include CREATE TEMP TABLE AS .... Such SQL statements currently cannot be expressed as a FunSQL SQLNode.

However you can still apply FunSQL to the generated cohort dataset. Note that translate() query does not return the dataset directly. Instead it stored the data in the cohort table. This means that after you execute the translate() query, you could fetch the cohort dataset from the table and transform it further with FunSQL:

From(:cohort) |> Where(Get.cohort_definition_id .== 1) |> Group(...) |> Select(...)
TheCedarPrince commented 2 years ago

Oh I did not realize it was that complicated! My apologies @xitology !

I wanted to avoid the creation of an intermediary table with translate. Or do you find it reasonable for a cohort table, for example, to be created and then additional queries run against this cohort table? This may be some of my naiveté coming through regarding how to efficiently handle data in a pipeline like this. Thanks Kirill!

clarkevans commented 2 months ago

@TheCedarPrince With v0.2 you should now be able to do further processing as this library no longer creates temporary tables.