how does futures work with sub queries? i wanted to put a column that updates when another table updates

[Cariaga]

currently i have this schema

BEGIN TRANSACTION;
	DEFINE TABLE player SCHEMALESS;
	DEFINE FIELD nickname ON TABLE player TYPE string;


	DEFINE FIELD accessed ON player DEFAULT <future> { time::now() } VALUE <future> { time::now() } PERMISSIONS FULL;

	DEFINE INDEX player_name_index ON TABLE player COLUMNS name UNIQUE;

	DEFINE TABLE room SCHEMALESS;
	DEFINE FIELD name ON TABLE room TYPE string;

        //some future here that returns count about total player in room

	DEFINE FIELD accessed ON room DEFAULT <future> { time::now() } VALUE <future> { time::now() } PERMISSIONS FULL;

	DEFINE INDEX room_name_index ON TABLE room COLUMNS name UNIQUE;


	DEFINE TABLE joined SCHEMALESS PERMISSIONS FULL;
	
	DEFINE FIELD in ON joined TYPE record<player> PERMISSIONS FULL;
	DEFINE FIELD out ON joined TYPE record<room> PERMISSIONS FULL;
	
	DEFINE INDEX room_player_index ON joined FIELDS in, out UNIQUE;

COMMIT TRANSACTION;

my goal is to have Futures on room table and count the number of joined

looking for a way how to define it

i had this originally but as view but can it be done on the column inside room and only return the value
SELECT * FROM (SELECT out, count(out) AS total_players FROM joined GROUP BY out)

[Cariaga]

figured it out
DEFINE FIELD total_players ON room VALUE <future> { RETURN count((SELECT * FROM joined WHERE out = $parent.id)); } DEFAULT <future> { RETURN count((SELECT * FROM joined WHERE out = $parent.id)); } PERMISSIONS FULL;
unless there is a better way