Cube.js - Unable to join 2 or more tables but keep getting a "Can't find join path to join" error

Question

I am new to Cube.js and I am trying to get data from 2 or more tables using the join.

The original SQL query would look like this which would join multiple tables together to display data from all 3.

SELECT wo.EndDate AS DueDate, p.PurchaseNumber AS Details, [proc].Name
FROM WorkOrder wo
INNER JOIN Purchase p
ON wo.PurchaseId = p.Id
INNER JOIN Product pr
ON wo.ProductId = pr.Id
INNER JOIN [Procedure] [proc]
ON pr.ProcedureId = [proc].Id

Here is what I have for the Cube.js schema

cube(`WorkOrder`, {
    sql: `SELECT * FROM dbo."WorkOrder"`,

    joins: {
        Purchase: {
            relationship: `hasMany`,
            sql: `${WorkOrder}.PurchaseId = ${Purchase}.Id`
        },
        Product: {
            relationship:`hasMany`,
            sql: `${WorkOrder}.ProductId = ${Product.Id}`
        },
        Procedure: {
            relationship: `hasMany`,
            sql: `${Product}.ProcedureId = ${Procedure}.Id`
        }
    },

    dimensions: {
            id: {
                sql: `${CUBE}."Id"`,
                type: `number`,
                primaryKey: true,
                shown: true
            },

            scheduledstartdate: {
                sql: `${CUBE}."ScheduledStartDate"`,
                type: `time`
            },

            scheduledenddate: {
                sql: `${CUBE}."ScheduledEndDate"`,
                type: `time`
            }
    }
});

cube(`Purchase`, {
    sql: `SELECT * FROM dbo."Purchase"`,

    dimensions: {
        id: {
            sql: `${CUBE}."Id"`,
            type: `number`,
            primaryKey: true
        },

        customerpurchasenumber: {
            sql: `${CUBE}."CustomerPurchaseNumber"`,
            type: `string`
        }
    }
});

cube(`Product`, {
    sql: `SELECT * FROM dbo."Product"`,

    dimensions: {
        id: {
            sql: `${CUBE}."Id"`,
            type: `number`,
            primaryKey: true
        },

        name: {
            sql: `${CUBE}."Name"`,
            type: `string`
        }
    }
});

cube(`Procedure`, {
    sql: `SELECT * FROM dbo."Procedure"`,

    dimensions: {
        id: {
            sql: `${CUBE}."Id"`,
            type: `number`,
            primaryKey: true
        },

        name: {
            sql: `${CUBE}."Name"`,
            type: `string`
        },
    }
});

And then I would try to query in the Cube.js API with this

'{"dimensions":["WorkOrder.scheduledenddate", "Purchase.customerpurchasenumber", "Procedure.Name"]}'

But I would keep getting the "Can't find join path to join 'WorkOrder', 'Purchase'" error. I tried to make it easier and query only 2 tables, but still the same error. Can someone point out what I am missing to get this to work?

Answer 1

NOTE: There is now a more complete and correct answer in the official documentation.

The "Can't find join path to join ..." error occurs when the cube.js cannot find a way to build a join for the cubes passed in the query.

To fix the error, you need to describe the joins parameter

Based on the relationships you described, cube.js builds a directed graph in which the nodes are cubes and the edges are the relationships you described.

Since cube.js uses left join when joining cubes, the order of the cubes in the query is important.

In your case, the graph looks like this:

So, your query is correct

{"dimensions":["WorkOrder.scheduledenddate", "Purchase.customerpurchasenumber", "Procedure.Name"]}

But, for example, this query is wrong

{"dimensions":["Procedure.name","Purchase.customerpurchasenumber"]}

Since the graph does not have an edge outgoing from "Procedure" and leading to "Purchase"

To fix the error, add the joins parameter to the description of the "Procedure" cube, which describes the relationship between "Procedure" and "Purchase"