FunSQL.jl
FunSQL.jl copied to clipboard
do not collapse a `Group` node when an aggregate is used in `Bind`
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")))
=#