Sorry I'm late to the game! If this reply is better-suited to its own post, please let me know and I'm happy to post elsewhere. It's a different approach to what seems like a similar problem.
About @rtmill's points, and a 'vocabulary' of queries as mentioned in @Rijnbeek's update: In addition to the proposed interfaces to query a CDM, the OMOP CDM might be a good candidate for a domain-specific language. There's a data model around which to work, with the focus of the model being a particular unit ('person'), and several queries could form a domain-specific-vocabulary that users could then compose into more complex queries.
As an example, I put together some code (happy to share, premature as it may be) to achieve the following (using dbplyr and an OMOP CDM on PostgreSQL, but it could be suited to the parameterized SQL used in SqlRender):
everyone %who_took% aspirin_concept_ids
anyone %first_took% aspirin_concept_ids
selected_pop <- everyone %who_had% diabetes_concept_ids
selected_pop %first_took% aspirin_concept_ids
To explain, the 'anyone'/'everyone' arguments on the left-hand side (lhs) are not evaluated and instead interpreted as a 'WHERE TRUE' clause. If the user provides a left-hand side dataframe containing a 'person_id' column, then the statement is instead interpreted as a 'WHERE person_id IN {lhs person_ids}' clause. Each of %who_took% and %who_had% return the DRUG_EXPOSURE and CONDITION_OCCURRENCE tables, respectively, but the person_id-based table filtering means that these statements may be composed.
Additionally, I could imagine a '+' operator that would do something like bind_rows if both the lhs and rhs are of the class cdm_person, but performs a left-join by person_id if the lhs is of class cdm_person and the right-hand side is a different cdm class (e.g. cdm_drug_exposure). Each table would have its own class.
Unfortunately, increasing user-friendliness by abstracting away SQL may not be so helpful as an educational tool, which is definitely a drawback as far as @MaximMoinat's point about helping people learn the CDM. At the same time, maybe it could be a good gateway (especially if users inspect the underlying SQL)?
Again, a DSL is perhaps too high-level, and is not a Shiny App, so forgive if this is off-topic. Going back to @rtmill's post, such wide scope could very well be overkill. However, it strikes me as a great chance to leverage R and metaprogramming because the 'standard' queries are in a great position to be functioned away (with well-defined inputs/outputs and SqlRender--thanks @schuemie!).
Have a great weekend!
P.S.
Each of who_took and who_had are implemented as infix functions with the % on either side, but also as functions to be called using parenthetical notation (e.g. who_had(...)). The who_had function has an argument first_only that is used to create first_had, this is also used for the drug exposure function who_took to implement first_took. Alternatively, the first_took function can be recapitulated as first_time(who_took(...), 'drug_exposure'), which is possible because the first_time function works on a table regardless of where it is implemented. 'drug_exposure' or 'condition' is then pasted into a string that is used for the query to extract the earliest occurrence of e.g. drug_exposure_start_datetime, grouped by person_id and whatever extra arguments are provided as part of ... (e.g. drug_concept_id).
The concept_id objects are made more user-friendly by sanitizing the input, allowing functions to accept a e.g. a vector of concept ids, data.frame of concept ids, and associated user-defined concept id names (if provided as a named list, dataframe, etc).
Under the hood, there is a set of evaluators. Right now it uses S3, ideally, these would use multiple dispatch (with the S4 system). Or maybe it would use the more standard-OO-style R6 system, still working that out. I've been working mainly from T. Mailund's recent work on DSLs in R
