FunSQL.jl icon indicating copy to clipboard operation
FunSQL.jl copied to clipboard

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

Open xitology opened this issue 4 years ago • 0 comments
trafficstars

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 avatar Sep 09 '21 13:09 xitology