Computed/RawSQL field type

[ganigeorgiev]

This was discussed in #289 (comment) and #265.

To allow simple computed response values and/or aggregations, we could define a new custom read-only field type that will accept a raw sqlite expression as a field option (SUM(), COUNT(), subquery or anything else that can be used as a SELECT column).

Furthermore, the computed fields could be combined with the "singleton style" type collections feature, so that users will be able to create a simple reporting pages and apis.

[tonyhart7]

can its change other table field ?? like for example I want to reduce amount in product.qty if someone ordering that item ?

[ganigeorgiev]

@tonyhart7 No, this field type is intented only as read-only as part of a SELECT statement. You will be able to use it to create an aggregation field, for example to dynamically calculate your product qty based on some sql query.

"Collection triggers" are intented to perform basic webhook-like operations (see #317).

[tonyhart7]

@tonyhart7 No, this field type is intented only as read-only as part of a SELECT statement. You will be able to use it to create an aggregation field, for example to dynamically calculate your product qty based on some sql query.

"Collection triggers" are intented to perform basic webhook-like operations (see #317).

so I was whandering in sup**ase and they have the same feature like webhook that you mention
while UI is pretty intuitive I think you can just make a like rule based using sql or same function like the one you use in rule base
so you dnt need make a new custom field and let the developer custom it for you

[ganigeorgiev]

@tonyhart7 I'm not sure that I understand your proposal.

This feature is expected to allow developers to enter a plain SQL string (eg. sqlite concat firstName || ' ' || lastName) in a read-only field type. The field value will be dynamic and computed everytime when you call for example client.records.getOne().

The other feature, collection triggers #317, is for post processing based on some event. This is already supported by PocketBase if you use it as framework with the event hooks, but #317 will try to simplify things a little by providing a UI for common hook actions, like making HTTP request(s) or sending a custom email.

[tonyhart7]

@tonyhart7 I'm not sure that I understand your proposal.

This feature is expected to allow developers to enter a plain SQL string (eg. sqlite concat firstName || ' ' || lastName) in a read-only field type. The field value will be dynamic and computed everytime when you call for example client.records.getOne().

The other feature, collection triggers #317, is for post processing based on some event. This is already supported by PocketBase if you use it as framework with the event hooks, but #317 will try to simplify things a little by providing a UI for common hook actions, like making HTTP request(s) or sending a custom email.

Yeah that will do, what I mean is we can get some interactivity like Rule using same technology no ??
and I notice that postgress have plpgsql so complex query can can be split into smaller and yet effective set similiar to sql,
so I wonder where sqlite have same feature

[Frioo]

Definitely a great feature to have! Awaiting updates.

[oysteinsigholt]

@ganigeorgiev I have been playing with this use case, and have made a quick computed-on-read implementation backed by sqlite generated columns, over on this branch.

The other alternative I'm exploring is computing the value on write using something like govaluate and writing the result to the database. My use case would benefit from having a materialized value for read performance, and a parsed expression to know which values depend on which. Are you firm on the value being computed on read, and the expression being a sql expression?

Compute-on-read demo

[ganigeorgiev]

@oysteinsigholt

Are you firm on the value being computed on read, and the expression being a sql expression?

Yes, at least that was the initial idea.

Couple weeks ago @iskaa02 created a PR for adding support for Views in #759 and I liked the idea but it needs to be implemented differently. I have it in my todo to research it in more details and eventually we may scrape this issue in favor of a new "View" collection type.

But for now this remain after the planned migrations flow improvements and filters enhancements.

[gevera]

Not sure if this is related or should I open an issue/proposal, would be great for generated columns based on preexisting ones For reference https://antonz.org/generated-columns

[tonyhart7]

Not sure if this is related or should I open an issue/proposal, would be great for generated columns based on preexisting ones For reference https://antonz.org/generated-columns

or just use JSON data type

[ganigeorgiev]

Experimental "View" collection type was added in the develop branch and will be available soon with the next v0.13.0 release.

The "View" collection type extends the scope of this task, allowing users to define any custom SELECT query and convert that to a read-only collection.

If anyone wants to test it, feel free to checkout the develop branch.

You can also read some notes on the implementaiton in #759 (comment).