How to add `DISTINCT`?

[slwu89]

Hi, I read the docs several times and I'm still not sure how to add distinct to my Select at end of a query (i.e. in regular SQL, SELECT DISTINCT FROM ... . Unfortunately I cannot provide the data I am using but for the following query I would like to add the distinct=true kwarg to the final Select node. I thought I could do it based on https://mechanicalrabbit.github.io/FunSQL.jl/stable/reference/#SELECT but I am not really sure what is the difference between SELECT and Select. Can someone give me advice on how to accomplish this?

equip_q = From(:Equip) |> As(:equip_tbl) |>
    Where(equip_oeb_selector[nothing]) |> Where(Get.equip_tbl.Site .== "WP") |>
    Join(From(:EquipUnitOps) |> As(:equip_unitops_tbl), on = Get.equip_tbl.ID .== Get.equip_unitops_tbl.Equip) |>
    Join(From(:UnitOps) |> As(:unitops_tbl) |> Where(Fun.in(Get.unitops_tbl.UnitOp, ["Vial Labeling", "Bottle Labeling"]...)), on = Get.equip_unitops_tbl.UnitOp .== Get.unitops_tbl.UnitOp) |>
    Select(Get.equip_tbl.ID, Get.equip_tbl.Name, Get.equip_tbl.UnitOp, Get.equip_tbl.OpArea, Get.equip_tbl.OEB, Get.equip_tbl.RemainingLife, Get.equip_tbl.RoomLocation, Get.equip_tbl.Risk, Get.equip_tbl.Portable, Get.equip_tbl.Site)

[xitology]

You can replace Select with Group:

Group(Get.equip_tbl.ID, Get.equip_tbl.Name, Get.equip_tbl.UnitOp, Get.equip_tbl.OpArea, Get.equip_tbl.OEB, Get.equip_tbl.RemainingLife, Get.equip_tbl.RoomLocation, Get.equip_tbl.Risk, Get.equip_tbl.Portable, Get.equip_tbl.Site)

This should generate a SELECT DISTINCT query.

[slwu89]

Thanks, that makes sense to me. Can you explain whats the difference between Select and SELECT in FunSQL?

[xitology]

Thanks, that makes sense to me. Can you explain whats the difference between Select and SELECT in FunSQL?

In FunSQL, there are two types of objects: those that closely mirror SQL syntax (e.g., FROM, SELECT) and those that represent data transformations (From, Select, which are of type SQLNode). You can't mix them, and, typically, you only need to use the latter. Unlike raw SQL syntax, these transformation objects aren’t constrained by SQL grammar rules, so they can be composed and applied in any order.

[slwu89]

Thanks for answering my questions. I'll close the issue.

[xitology]

BTW, we are currently experimenting with a new notation for building queries, @funsql macro, in which your query look like this:

equip_q = @funsql begin
    equip_tbl => from(Equip)
    filter($(equip_oeb_selector[nothing]))
    filter(equip_tbl.Site == "WP")
    join(
        equip_unitops_tbl => from(EquipUnitOps),
        on = equip_tbl.ID == equip_unitops_tbl.Equip)
    join(
        unitops_tbl => from(UnitOps).filter(in(UnitOp, "Vial Labeling", "Bottle Labeling")),
        on = equip_unitops_tbl.UnitOp == unitops_tbl.UnitOp)
    group(equip_tbl.ID, equip_tbl.Name, equip_tbl.UnitOp, equip_tbl.OpArea, equip_tbl.OEB, equip_tbl.RemainingLife, equip_tbl.RoomLocation, equip_tbl.Risk, equip_tbl.Portable, equip_tbl.Site)
end

[slwu89]

That's a nice syntax. Have you seen the work in Catlab which allows conjunctive queries to be expressed as undirected wiring diagrams? They also admit a very nice visualization, see https://blog.algebraicjulia.org/post/2020/12/cset-conjunctive-queries/