missing FROM-clause entry for table when building a measure from joined data

[dada-engineer]

Describe the bug Hi there, I have two cubes Orders and Items and I want to add a measure on Orders to calculate totalOrderPrice. But I get missing FROM-clause entry for table "orders_measure_join__orders" Can anyone help me out here?

cube(`Orders`, {
  sql: `SELECT * FROM purchase_order`,

  joins: {
    Items: {
      sql: `${CUBE}.id = ${Items}.fk_purchase_order`,
      relationship: `hasMany`,
    },
  },

  measures: {
    count: {
      type: `count`,
      drillMembers: [id, createdAt],
    },
    totalOrderPrice: {
      sql: `${Items.price} * ${Items.quantity}`,
      type: `sum`,
    },
  },

  dimensions: {
    id: {
      sql: `id`,
      type: `string`,
      primaryKey: true,
    },
    createdAt: {
      sql: `created_at`,
      type: `time`,
    },
  },
});

cube(`Items`, {
  sql: `SELECT * FROM purchase_order_item`,

  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primaryKey: true,
    },
    orderId: {
      sql: `fk_purchase_order`,
      type: `number`,
    },
    price: {
      sql: `price_per_unit`,
      type: `number`,
    },
    quantity: {
      sql: `quantity`,
      type: `number`,
    },
  },
});

To Reproduce Steps to reproduce the behavior: @igorlukanin on the slack channel also provided a minimal example and generated sql

cube(`Orders`, {
  sql: `
    SELECT 1 AS id, '2024-01-01'::TIMESTAMP AS created_at UNION ALL
    SELECT 2 AS id, '2024-01-02'::TIMESTAMP AS created_at
  `,

  joins: {
    Items: {
      sql: `${CUBE}.id = ${Items}.fk_purchase_order`,
      relationship: `hasMany`,
    },
  },

  measures: {
    count: {
      type: `count`,
      drillMembers: [id, createdAt],
    },
    totalOrderPrice: {
      sql: `${Items.price} * ${Items.quantity}`,
      type: `sum`,
    },
  },

  dimensions: {
    id: {
      sql: `id`,
      type: `string`,
      primaryKey: true,
    },
    createdAt: {
      sql: `created_at`,
      type: `time`,
    },
  },
});

cube(`Items`, {
  sql: `
    SELECT 11 AS id, 1 AS fk_purchase_order, 10 AS price_per_unit, 5 AS quantity UNION ALL
    SELECT 12 AS id, 1 AS fk_purchase_order, 10 AS price_per_unit, 6 AS quantity UNION ALL
    SELECT 13 AS id, 2 AS fk_purchase_order, 10 AS price_per_unit, 7 AS quantity UNION ALL
    SELECT 14 AS id, 2 AS fk_purchase_order, 10 AS price_per_unit, 8 AS quantity
  `,

  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primaryKey: true,
    },
    orderId: {
      sql: `fk_purchase_order`,
      type: `number`,
    },
    price: {
      sql: `price_per_unit`,
      type: `number`,
    },
    quantity: {
      sql: `quantity`,
      type: `number`,
    },
  },
});

Expected behavior The join of the measures should be referencing the correct alias

Version: 0.35.56+ (didn't test lower versions)

Additional context Here is the slack convo

[igorlukanin]

Hi @dada-engineer 👋

Thanks for filing this! Taking a second look at this, I see that it works (no error) with many_to_one for the join relationship, but would not work for one_to_many. However, I feel like one_to_many is the correct one here,