malloydata / malloy

Malloy is an experimental language for describing data relationships and transformations.
http://www.malloydata.dev
MIT License
1.99k stars 76 forks source link

Improve expand/modify experience for nested queries, including expansion, add limit, filter, or ordering #889

Open christopherswenson opened 2 years ago

christopherswenson commented 2 years ago

Today, we cannot add limits, filters, or orderings to nested queries without first duplicating them. This is due to a limitation of the Malloy structdef format in which a refinement of a query field is indistinguishable from a redefinition of the field with the added attributes. In other words, during parsing, the difference between refining something and redefining it is lost. E.g. nest: my_query { limit: 10 } is indistinguishable from nest: my_query is { <...my_query> limit: 10 } (where <...my_query> represents the attributes of my_query).

That said, it would not be hard for us to add options to the nest menu to limit, filter, or order, then automatically expand the nested query before adding the limit/filter/ordering.

anikaks commented 2 years ago

Yes! Todd and I have been talking some about how to make this whole flow with named queries feel better. https://github.com/looker-open-source/malloy/issues/822 is probably part of the same conversation.

The "duplicate" option doesn't immediately look like what I want to click on to be able to add/modify/expand a nest so I like your recommendation to show those options before even if we do actually have to duplicate it to do these actions (though having the option to use refinement and retain the link to the parent named query would be preferable if it were possible in the longer term--I don't fully grasp why it's not possible today).

vitiral commented 1 year ago

Does this include filtering by nested query results? I was surprised that the having: clause below didn't compile in the quickstart:

query: airports -> {
  where: state = 'CA'
  group_by: county
  aggregate: airport_count
  nest: major_facilities is {
    where: major = 'Y'
    group_by: name is concat(code, ' (', full_name, ')')
  }
  nest: by_facility is {
    group_by: fac_type
    aggregate: airport_count
  }
  having: by_facility.airport_count < 0
  top: 5
}

Results in:

... other errors
line 13: 'by_facility' is not defined
  |   having: by_facility.airport_count < 0
  |           ^
line 13: Aggregate or analytic expression expected in HAVING filter
  |   having: by_facility.airport_count < 0