I have two tables called Writers and Publications where there is a foreign key publications.writer_id = writers.id

Writers

Publications

I want to do a proof of concept in cube.js Developer Playground to show various charts. Is it possible to filter dynamically based on the user_id so that they can only access content that is equal to or in their subtree i.e.

• If an ADMIN/EDITOR is using, they can see all the publications
• If the WEB EDITOR (writers.id=4) is using the application, they can only see their own articles (publications.id in (4,5))
• If the WEB EDITOR (writers.id=3) is using the application, they can see their publication and the WEB PROOFREADER's one (publications.id in (3,6))
• The WEB PROOFREADER should only see their publication (publications.id=6)

These are the models I have set up so far

cube(`Writers`, {
  sql: `SELECT * FROM public.writers`,
  
  preAggregations: {},
  
  joins: {

    Publications:{
      sql: `${CUBE}.id = ${Publication}.writer_id`,
      relationship: `hasMany`
    }
    
  },
  
  measures: {
    count: {
      type: `count`,
      drillMembers: [id, name, created]
    }
  },
  
  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primaryKey: true
    },

 role: {
          sql: `role`,
          type: `string`,
        },
};





cube(`Publications`, {
  sql: `SELECT * FROM public.publications`,
  
  preAggregations: {},
  
  joins: {

    Writer:{
      sql: `${CUBE}.writer_id = ${Writers}.id`,
      relationship: `hasOne`
    }
    
  },
  
  measures: {
    count: {
      type: `count`,
      drillMembers: [id, name, created]
    }
  },

 dimensions: {
     id: {
          sql: `id`,
          type: `number`,
          primaryKey: true
        },

     wordLength: {
              sql: `word_length`,
              type: `number`,
            },
};

I know there are filters and segments but these appear to be static. Is there any way to pass a writer_id to filter the relevant data dynamically? (I have no previous knowledge of JS)

CodePudding user response：

I think you can query the cube from your front-end application, queries are dynamic you can filter with the id.

CodePudding user response：

I think these recipes can help you:

• https://cube.dev/docs/recipes/role-based-access
• https://cube.dev/docs/recipes/column-based-access
• https://cube.dev/docs/recipes/passing-dynamic-parameters-in-a-query