more sidebar info - Githubissues

when u click on a cluster it should show more info than just associations

e.g.

number of shulkers, number of chests, most recent online time, first hit timestamp, has legacy, etc

example query that outputs number of shulkers (obv just replace %shulker% with %chest% to get chests):

WITH RECURSIVE
    tmp AS (
        SELECT id, disjoint_rank
        FROM dbscan
        WHERE id = 38916757
    ),
    clusters AS (
        SELECT *
        FROM tmp
        UNION
        SELECT dbscan.id, dbscan.disjoint_rank
        FROM dbscan
                 INNER JOIN clusters ON dbscan.cluster_parent = clusters.id
        WHERE clusters.disjoint_rank > 0
    ),
    shulkers AS MATERIALIZED (
        SELECT block_state
        FROM block_states
        WHERE name LIKE '%shulker%'
    )
SELECT SUM(num_shulkers)
FROM (
         SELECT (
                    SELECT COUNT(*)
                    FROM (
                             SELECT block_state,
                                    ROW_NUMBER() OVER (PARTITION BY x, y, z ORDER BY created_at DESC) AS age
                             FROM blocks
                             WHERE blocks.x >> 4 = dbscan.x
                               AND blocks.z >> 4 = dbscan.z
                         ) tmp
                    WHERE block_state IN (SELECT * FROM shulkers)
                      AND age = 1
                ) AS num_shulkers,
                dbscan.x,
                dbscan.z
         FROM clusters
                  INNER JOIN dbscan ON dbscan.id = clusters.id
     ) tmp;

also do it for signs. in this case, we still want to use the blocks table not signs since signs doesnt keep track of overwrites, so just replace the block scan with sign

nerdsinspace / nocom-frontend

more sidebar info #15