4 ms·
As i said, it's about maintainability, not about length ;) All that declaration can be reused and implicitly imported. SO main query is just simple as main:
by revskill 4y ago
As i said, it's about maintainability, not about length ;)
All that declaration can be reused and implicitly imported. SO main query is just simple as
main:
from: payments
alias: p
distinct: true
select:
- apply: ["sum", "i.views"]
- apply: ["sum", "i.clicks"]
- apply: ["sum", "p.amount"]
where:
- apply: ["eql", "user_id", "12"]
group:
- apply: ["group_by", "date"]
- simonw 4y agoI'm not seeing the readability benefit over this (which I reformatted and converted to lower-case, personal taste): select distinct sum(i.views), sum(i.clicks), sum(p.amount) from "payments" "p" where user_id = 12 group by date
- revskill 4y agoThen try this query instead ? https://yaml2sql.netlify.app/play/crosstab/ https://yaml2sql.netlify.app/play/crosstab/ How do you think ?
- simonw 4y agoWow, I'd never seen that crosstab() function before! TIL. https://www.postgresql.org/docs/current/tablefunc.html#id-1.11.7.52.5.5 https://www.postgresql.org/docs/current/tablefunc.html#id-1.... This raises a problem with the YAML DSL though. Consider the following (truncated) example: main: select: - field: ct.* from: alias: ct fields: ... crosstab: - select: - extra_infos.problem_id - extra_infos.info_type - extra_infos.info_value from: extra_infos - select: - extra_infos.info_type distinct: true from: extra_infos Inside the "from:" area, those alias and fields and crosstab keys are all at the same level. But... "alias:" and "fields:" correspond to features of the from clause itself, whereas "crosstab:" is a special custom thing (a macro?) which ends up being compiled into a call to the identically-named crosstab() feature. So to understand the YAML, I need to understand both how the crosstab() feature in PostgreSQL works AND how that YAML DSL represents "crosstab:" calls using a different syntax. I looked up crosstab in the PostgreSQL manual but that information won't help me figure out the DSL. So I think I'd rather stick with regular SQL.
- revskill 4y agoMy goal is to build a composable and consistent DSL. It should have few keywords and structures as possible. Of course there's some "fixed keyword" like above crosstab (due to inconsistent SQL syntax i think). "alias" + "fields" helps you build "as (field1, field2,...)" kind of thing. "select" + "from" must form a normal "sql query".