Multiple Joins with same Cube

What will be a good solution if you need to join the same cube multiple times?
For e.g. For the ticketing system, we have two cubes, Issues, and Users. The issue will have an assignee, reporter, and creator of the issue, and all of them will be foreign keys to the Users table.

cube("Users", {
    sql: `SELECT *
          FROM public.users`,
    dimensions: {
        id: {
            sql: `id`,
            type: `number`,
            primaryKey: true,
            shown: false,

        },
        email: {
            sql: `email`,
            type: `string`
        },
        firstName: {
            sql: `first_name`,
            type: `string`
        },
        lastName: {
            sql: `last_name`,
            type: `string`
        }
    },
})

cube("Issues", {
    sql: `SELECT *
          FROM public.issues`,
    dimensions: {
        id: {
            sql: `id`,
            type: `number`,
            primaryKey: true,
            shown: false,

        },
        name: {
            sql: `last_name`,
            type: `string`
        }
    },
})

We need to build the below queries -

  • Issues assigned per User ( user id, email, issue count)
  • Issues grouped by Reportes ( user id, email, issues)
  • Average issue count per assigne
    Is it possible to have joined for all these user relations like shown below?
Creator: {
    relationship: `belongsTo`,
    sql: `${CUBE}.created_by_id = ${Users}.id`
},
Reporter: {
    relationship: `belongsTo`,
    sql: `${CUBE}.reporter_id = ${Users}.cid`
},
Assignee: {
    relationship: `belongsTo`,
    sql: `${CUBE}.assignee_id = ${Users}.cid`
}

If not, can you suggest some alternative solutions to achieve this?

Yeah, that’s how I’ve been doing it. You’ll need to declare the Creator, Reporter, and Assignee cubes like this:

cube(`Creator `, {
  extends: Users,
});

Then use those cubes in the joins instead of User

1 Like