Feature: view auto update from other tables

[agufagit]

Is your feature request related to a problem?

a way to auto update view from other tables

DEFINE TABLE aggregated_view AS
	SELECT id,
        (SELECT in, created_at FROM <-like ORDER BY created_at) as likes
        FROM posts
;

this view doesn't auot update when like relation table changes

Describe the solution

a clause to identity related tables to trigger auto update on row level WITH AUTOUPDATE ON like(out, post_id), comment(out, post_id), out is the field that matches with post_id field of view, whenever like or comment table is updated/inserted/deleted, corresponding row in materialized view is also updated, so it's something like update aggregated_view as select ... from like.out where post_id=like.out

DEFINE TABLE aggregated_view AS
	SELECT id as post_id,
        (SELECT in, created_at FROM <-like ORDER BY created_at) as likes
        FROM posts
        WITH AUTOUPDATE ON like(out, post_id), comment(out, post_id)
;

Alternative methods

manually update the view using ON UPDATE trigger on like relation table

SurrealDB version

surreal 1.4.0

Contact Details

No response

Is there an existing issue for this?

• I have searched the existing issues

Code of Conduct

• I agree to follow this project's Code of Conduct