MechanicalRabbit / FunSQL.jl

Julia library for compositional construction of SQL queries
https://mechanicalrabbit.github.io/FunSQL.jl
Other
146 stars 6 forks source link

do not collapse a `Group` node when an aggregate is used in `Bind` #12

Closed xitology closed 1 year ago

xitology commented 2 years ago
using FunSQL: SQLTable, From, Select, Join, Where, Group, Define, Bind, Fun, Get, Agg, Var, render
visit_occurrence = SQLTable(:visit_occurrence, :visit_occurrence_id, :person_id, :visit_start_date, :visit_end_date)
condition_occurrence = SQLTable(:condition_occurrence, :condition_occurrence_id, :person_id, :condition_start_date, :condition_end_date)
ConditionAfter(person_id, date) =
    From(condition_occurrence) |>
    Where(Fun.and(Get.person_id .== Var.person_id, Get.condition_start_date .> Var.date)) |>
    Bind(:person_id => person_id, :date => date)
q = From(visit_occurrence) |>
    Group(Get.person_id) |>
    Where(Fun.exists(ConditionAfter(Get.person_id, Agg.max(Get.visit_start_date))))
print(render(q))
#=>
SELECT "visit_occurrence_1"."person_id"
FROM "visit_occurrence" AS "visit_occurrence_1"
GROUP BY "visit_occurrence_1"."person_id"
HAVING (EXISTS (
  SELECT NULL
  FROM "condition_occurrence" AS "condition_occurrence_1"
  WHERE (("condition_occurrence_1"."person_id" = "visit_occurrence_1"."person_id") AND ("condition_occurrence_1"."condition_start_date" > MAX("visit_occurrence_1"."visit_start_date")))
=#
xitology commented 1 year ago

Fixed in https://github.com/MechanicalRabbit/FunSQL.jl/pull/37

print(render(q))
#=>
SELECT "visit_occurrence_2"."person_id"
FROM (
  SELECT
    "visit_occurrence_1"."person_id",
    MAX("visit_occurrence_1"."visit_start_date") AS "max"
  FROM "visit_occurrence" AS "visit_occurrence_1"
  GROUP BY "visit_occurrence_1"."person_id"
) AS "visit_occurrence_2"
WHERE (EXISTS (
  SELECT NULL
  FROM "condition_occurrence" AS "condition_occurrence_1"
  WHERE
    ("condition_occurrence_1"."person_id" = "visit_occurrence_2"."person_id") AND
    ("condition_occurrence_1"."condition_start_date" > "visit_occurrence_2"."max")
))
=#