how to address error cubejs 'From members are not found ... Please make sure join fields are referencing dimensions instead of columns.'

Question

I have the Error: From members are not found in [] for join {"join":{"relationship":"belongsTo"},"from":"Conversations","to":"Advisers","originalFrom":"Conversations","originalTo":"Advisers"}. Please make sure join fields are referencing dimensions instead of columns. fallowing schemas with the fallowing schemas.

cube(`Conversations`, {
  sql: `
    select
      appUUID, roomId, sessionId, count(1) Messages,
      min(channel) channel,
      min(createdAt) FirstMessage,
      max(createdAt) LastMessage,
      max(case when type='status' and status='ended' then \`payload.selectedOption\` else '' end) EndReason,
      max(
        case
          when type='status' and status='ended' and sender='bot' then 'Robot'
          when type='status' and status='ended' and sender <> 'bot' then
            (
              \`advisorId\`
            )
          else ''
        end
      ) EndAdvisor,
      max(userGuest) userGuest,
      max(userGuestLocal) userGuestLocal
    from
      \`keybe-conversations\`.messages
    where
      ${SECURITY_CONTEXT.appUUID.filter(
        'appUUID'
      )}
    group by
      appUUID, roomId, sessionId
  `,
  joins:{
    Advisers: {
      relationship: 'belongsTo',
      sql: ` concat(${CUBE}.\`appUUID\`, '-', ${CUBE}.\`EndAdvisor\`) = concat(${Advisers}.\`appUUID\`, '-', ${Advisers}.\`userId\`)`
    }
  },
  measures: {
    count: {
      type: `count`,
      drillMembers: [userGuest, userGuestLocal, channel]
    },
    totalMessages: {
      type: `sum`,
      sql: `Messages`,
      title: 'Total Messages'
    },
    ended: {
      type: `count`,
      filters: [
        {
          sql: `${CUBE}.EndReason <> ''`
        }
      ],
      drillMembers: [userGuest, userGuestLocal, channel]
    },
    notEnded: {
      type: `count`,
      filters: [
        {
          sql: `${CUBE}.EndReason = ''`
        }
      ],
      drillMembers: [userGuest, userGuestLocal, channel]
    }
  },
  dimensions: {
    pk: {
      sql: `concat(${CUBE}.\`appUUID\`, '-', ${CUBE}.\`roomId\`, '-', ${CUBE}.\`sessionId\`)`,
      type: `string`,
      primaryKey: true
    },
    channel: {
      sql: `channel`,
      type: `string`
    },
    appUUID: {
      sql: `${CUBE}.\`appUUID\``,
      type: `string`,
      title: `AppUUID`
    },
    roomId: {
      sql: `${CUBE}.\`roomId\``,
      type: `string`,
      title: `RoomId`
    },
    sessionId: {
      sql: `${CUBE}.\`sessionId\``,
      type: `string`,
      title: `Session Id`
    },
    endReason: {
      sql: `${CUBE}.\`EndReason\``,
      type: `string`,
      title: `End Reason`
    },
    endAdvisor: {
      sql: `${CUBE}.\`EndAdvisor\``,
      type: `string`,
      title: `End Advisor`
    },
    firstMessage: {
      sql: `${CUBE}.\`FirstMessage\``,
      type: `time`,
      title: `First Message`
    },
    lastMessage: {
      sql: `DATE_FORMAT(${CUBE}.\`LastMessage\`, '%Y-%m-%d %H:%m:%s')`,
      type: `string`,
      title: `Last Message`
    },
    initialMessage: {
      sql: `DATE_FORMAT(${CUBE}.\`FirstMessage\`, '%Y-%m-%d %H:%m:%s')`,
      type: `string`,
      title: `Initial Message`
    },
    userGuest: {
      sql: `${CUBE}.\`userGuest\``,
      type: `string`,
      title: `UserGuest`
    },
    userGuestLocal: {
      sql: `${CUBE}.\`userGuestLocal\``,
      type: `string`,
      title: `UserGuestLocal`
    },
    takenTime: {
      sql: `TIMESTAMPDIFF(HOUR,DATE_FORMAT(${CUBE}.\`FirstMessage\`, '%Y-%m-%d %H:%m:%s'),DATE_FORMAT(${CUBE}.\`LastMessage\`, '%Y-%m-%d %H:%m:%s'))`,
      type: 'string',
      title: 'TakenTime'
    }
  },
  preAggregations: {
    // main: {
    //   type: `originalSql`,
    //   external: true
    // },
    ConversationsRollup: {
      type: `rollup`,
      measureReferences: [Conversations.count],
      dimensionReferences: [Conversations.appUUID, Conversations.channel, Conversations.endAdvisor, Conversations.firstMessage, Conversations.lastMessage, Conversations.userGuest, Conversations.userGuestLocal],
      external: true
    },
    joinedWithAdvisersRollup: {
      type: `rollupJoin`,
      measureReferences: [Conversations.count],
      dimensionReferences: [Advisers.name],
      rollupReferences: [Advisers.AdvisersRollup, Conversations.ConversationsRollup],
      external: true,
    }
  },
  segments: {
    humanAdvisor: {
      sql: `${CUBE}.EndAdvisor <> 'Robot' AND ${CUBE}.EndAdvisor <> ''`,
    },
    robotAdvisor: {
      sql: `${CUBE}.EndAdvisor = "Robot"`,
    },
    withoutAdvisor: {
      sql: `${CUBE}.EndAdvisor = ''`,
    }
  }
})

cube(`Advisers`, {
  sql: `
    SELECT * FROM \`keybe-conversations\`.advisers where
    ${SECURITY_CONTEXT.appUUID.filter(
      'appUUID'
    )}
  `,
  joins: {
    Rooms: {
      relationship: 'hasMany',
      sql: `concat(${CUBE}.\`appUUID\`, '-', ${CUBE}.\`userId\`) = concat(${Rooms}.\`appUUID\`, '-', ${Rooms}.\`advisorId\`)`
    },
    Conversations: {
      relationship: 'hasMany',
      sql: `concat(${CUBE}.\`appUUID\`, '-', ${CUBE}.\`userId\`) = concat(${Conversations}.\`appUUID\`, '-', ${Conversations}.\`EndAdvisor\`)`
    }
  },
  measures: {
    count: {
      type: `count`,
      drillMembers: [name, email, userId]
    }
  },
  dimensions: {
    pk: {
      sql: `concat(${CUBE}.\`appUUID\`, '-', ${CUBE}.\`userId\`)`,
      type: `string`,
      primaryKey: true
    },
    appUUID: {
      sql:`${CUBE}.\`appUUID\``,
      type: `string`,
      title: `AppUUID`
    },
    status: {
      sql: `${CUBE}.\`status\``,
      type: `string`,
      title: `Status`
    },
    name: {
      sql:`${CUBE}.\`name\``,
      type: 'string',
      title: `Name`
    },
    email: {
      sql:`${CUBE}.\`email\``,
      type: 'string',
      title: `Email`
    },
    phone: {
      sql:`concat(COALESCE(${CUBE}.\`countryCode\`, 'NO NULO-') , ${CUBE}.\`phone\`)`,
      type: 'string',
      title: `Phone`
    },
    userId: {
      sql:`${CUBE}.\`userId\``,
      type: 'string',
      shown: false
    }
  },
  preAggregations: {
    AdvisersRollup: {
      type: 'rollup',
      measureReferences:[Advisers.count],
      dimensionReferences:[Advisers.status, Advisers.name, Advisers.email, Advisers.phone, Advisers.appUUID],
      external: true
    }
  }
})

Thank you in advanced.

Answer 1

Could you try updating the join in your Conversation cube to look like this:

  joins:{
    Advisers: {
      relationship: 'belongsTo',
      sql: ` concat(${CUBE.appUUID}, '-', ${CUBE.endAdvisor}) = concat(${Advisers.appUUID}, '-', ${Advisers.userId})`
    }
  },