How to do dynamic tables?

[hasynjo]

Hi there,

I want to use a dynamic table component, which shows table columns depending of the role, the user has.

I am struggling to transform the columns to display into the dynamic component.

Here's my code and the output:

set userid = 1

--Static component
SELECT 'table' AS component
     , TRUE    AS sort
     , TRUE    AS search;
SELECT user_name   AS "UserID"
     , last_name   AS "Last Name"
     , first_name  AS "First Name"
     , birth_date  AS "Date of Birth"
  FROM table_.user_
 WHERE user_id <> $userid::INTEGER;

-- Getting columns to display
SELECT 'table' AS component
     , TRUE    AS sort
     , TRUE    AS search;
SELECT 'dynamic' AS component, 
       jsonb_object_agg(o.column_title, o.column_name) AS properties
  FROM view_.object_access_user AS o
 WHERE o.user_id = $userid::INTEGER
   AND o.access_id > 0;

--?? Using Column names with dynamic table component ??

Any help would be very welcome.

[lovasoa]

Hello ! Can you give a little more context about what you are building ?

Storing column names as values in the database is generally not recommended, because it can make querying and maintaining the database more complex. When designing database schemas, it is generally advised to decouple the database structure from the database contents. This makes long term maintenance and migrations easier. A common answer to your problem is to statically define in advance which views are possible. Something like

CREATE VIEW user_view_admin AS
SELECT user_name, last_name, first_name, birth_date FROM table_.user_;

CREATE VIEW user_view_employee AS
SELECT user_name, first_name FROM table_.user_;

and then in your database queries:

set role = (select role from users where id = $userid::integer);

select * from user_view_admin    where $role = 'admin';
select * from user_view_employee where $role = 'employee';

---

If you really want to store column names in your database

I wouldn't recommend it, but here is what you could do, if you needed to stick with your existing design

WITH 
-- Get the column access configuration for user $userid
user_access AS (
  SELECT 
    o.column_title, 
    o.column_name
  FROM object_access_user AS o
  WHERE o.user_id = $userid::INTEGER AND o.access_id > 0
),
-- Convert users to JSON objects (excluding user $userid)
users_as_json AS (
  SELECT
    user_id,
    row_to_json(u) AS user_json
  FROM 
    user_ u
  WHERE 
    u.user_id != $userid::INTEGER
),
-- Extract the relevant fields for each user based on access
user_fields AS (
  SELECT
    uj.user_id,
    ua.column_title,
    uj.user_json->>ua.column_name AS field_value
  FROM
    users_as_json uj
  CROSS JOIN
    user_access ua
)
-- Build the final JSON objects for each user
SELECT
  'dynamic' as component,
  jsonb_object_agg(column_title, field_value) AS properties
FROM
  user_fields
GROUP BY
  user_id;

try online

[hasynjo]

Hello lovasoa,

I am trying to migrate an MS Access-based audit management system.

Thank you for your help and recommendation. Your code works great. You're a real SQL wizard.

Best regards