Views V2

[mjashanks]

Views have been with us for a long time. However, since we added the "Define Filters" option to Data Providers, they have lost some of their purpose.

However, views are still the only way that we can achieve “aggregation” functions on internal tables

Please give your opinions below on views.

How we use views today

Aggregation functions

• sum of a column.
• count of records
• average of a column

Filters
We can also apply a filter to a dataset (without applying an aggregation). This has largely been superseded by “Define Filters”. However, this is still needed for performing aggregates on the filtered dataset.

Group by
Allows to apply aggregates per column.

Charts
Whilst "Charts" is not a feature of views per say, this is one of the primary uses of a view - especially with the combination of a group by.

For example, you might want to display a bar chart, where each bar is a Group.

We should consider how views and charts interact.

Limitations

Applying a dynamic filter
For example, we cannot apply a filter like
Creation Date > {{ fromDate }} - where fromDate is a parameter passed into the view.
#2986

Group by relationship
If a Project has many Tasks, we cannot apply a Count of Tasks per Project
#2763
#3171

Group by, then by
This would allow us to perform 2 or more groupings in one query.
#3426

Group by Formula
#3678

"Having"
"Having" (SQL terminology) is the ability to apply a filter on an aggregate function. For example:

Tasks, Grouped By Project, Having Count > 5 = show me projects that have more than 5 tasks.

Formulas
Views do not support formula fields.
#1033
#2271
#3170
Group by formula is particularly useful, for creating on-the fly groups. e.g. "Group by Month" (where date is a timestamp). This was raised by Sypher and here: #3678

Filters do not handle null
#2042

Sorting and pagination
This is irrelavant if views are only to be used for calculations. However, we are unable to sort data by any given column and then paginate.
#2235

Column projection
Again, irrelevant if using calculations only. But, we cannot choose which columns a view returns: #1299.

This was raised in #3268, with a really good point - restricting access to certain columns.

[daawaan4x]

Hello, are there any updates regarding this?

[xnohat]

Hi, currently at version 1.0.98 still cannot filter by formula column in View ?

[marco-santulli]

hi mike,

I think it would also be very useful to allow views for the external databases, like postgres or mysql. what do you think?

M

[daawaan4x]

@marco-santulli

hi mike,

I think it would also be very useful to allow views for the external databases, like postgres or mysql. what do you think?

M

BudibaseDB Views are just the same as custom queries for MySQL and PostgreSQL

[hmmhmmhm]

i need this

[marco-santulli]

@daawaan sorry replying here because github is playing up with replies...

I thought views on the internal DB supported paginations?
Also, a view would be picked up by budibase automatically, without the need to write and maintain a query no?

M

[Zero2XIII]

Any progress on this point, or is there any known workaround?:

Group by, then by
This would allow us to perform 2 or more groupings in one query.
#3426

[Brrrradley]

Any progress on this point as well? Can't proceed without a workaround.

Formulas
Views do not support formula fields.
#1033
#2271
#3170
Group by formula is particularly useful, for creating on-the fly groups. e.g. "Group by Month" (where date is a timestamp). This was raised by Sypher and here: #3678

[hurrifan1]

As far as #1299 and #3268 and the need to limit columns in a view, what is the recommended way of handling that in the absence of that feature? Just make two linked tables where one has the "public" columns and the other has the "restricted" columns?