Join Table Data

[MPiland]

Before opening, please confirm:

• [X] I have searched for duplicate or closed issues and discussions.
• [X] I have read the guide for submitting bug reports.
• [X] I have done my best to include a minimal, self-contained set of instructions for consistently reproducing the issue.

JavaScript Framework

React

Amplify APIs

Not applicable

Amplify Version

v6

Amplify Categories

api

Backend

Amplify Gen 2 (Preview)

Environment information

``` # Put output below this line System: OS: Windows 11 10.0.22621 CPU: (16) x64 AMD Ryzen 7 7840HS w/ Radeon 780M Graphics Memory: 619.91 MB / 15.19 GB Binaries: Node: 20.12.2 - C:\Program Files\nodejs\node.EXE npm: 10.8.2 - C:\Program Files\nodejs\npm.CMD Browsers: Edge: Chromium (127.0.2651.74) Internet Explorer: 11.0.22621.3527 npmPackages: %name%: 0.1.0 @aws-amplify/backend: ^1.2.1 => 1.2.1 @aws-amplify/backend-cli: ^1.2.5 => 1.2.6 @aws-amplify/ui-react: ^6.2.2 => 6.3.1 @aws-amplify/ui-react-internal: undefined () @aws-amplify/ui-react-server: undefined () @aws-sdk/client-cognito-identity-provider: ^3.649.0 => 3.651.1 @aws-sdk/client-location: ^3.651.1 => 3.651.1 @dnd-kit/core: ^6.1.0 => 6.1.0 @dnd-kit/sortable: ^8.0.0 => 8.0.0 @dnd-kit/utilities: ^3.2.2 => 3.2.2 @emotion/react: ^11.13.3 => 11.13.3 @emotion/styled: ^11.13.0 => 11.13.0 @floating-ui/react: ^0.26.8 => 0.26.24 @fvilers/disable-react-devtools: ^1.3.0 => 1.3.0 @gsap/react: ^2.1.0 => 2.1.1 @mantine/vanilla-extract: ^7.12.1 => 7.12.2 @mui/material: ^6.0.2 => 6.1.0 @radix-ui/colors: ^3.0.0 => 3.0.0 @re-dev/react-truncate: ^0.4.2 => 0.4.2 @react-spring/web: ^9.7.4 => 9.7.4 @react-three/drei: ^9.105.6 => 9.112.0 @react-three/eslint-plugin: ^0.1.1 => 0.1.1 @react-three/fiber: ^8.16.6 => 8.17.7 @react-three/rapier: ^1.3.1 => 1.4.0 @reduxjs/toolkit: ^2.1.0 => 2.2.7 @reduxjs/toolkit-query: 1.0.0 @reduxjs/toolkit-query-react: 1.0.0 @reduxjs/toolkit-react: 1.0.0 @supabase/supabase-js: ^2.45.3 => 2.45.4 @tanstack/eslint-plugin-query: ^5.52.0 => 5.56.1 @tanstack/query-codemods: undefined () @tanstack/react-query: ^5.52.1 => 5.56.2 @tanstack/react-query-devtools: ^5.52.1 => 5.56.2 @tanstack/react-router: ^1.49.7 => 1.57.15 @tanstack/router-plugin: ^1.49.3 => 1.57.15 @testing-library/jest-dom: ^6.4.0 => 6.5.0 @testing-library/react: ^14.1.2 => 14.3.1 @testing-library/user-event: ^14.5.2 => 14.5.2 @tiptap/extension-color: ^2.2.0 => 2.6.6 @tiptap/extension-highlight: ^2.2.0 => 2.6.6 @tiptap/extension-link: ^2.2.0 => 2.6.6 @tiptap/extension-list-item: ^2.2.0 => 2.6.6 @tiptap/extension-placeholder: ^2.2.0 => 2.6.6 @tiptap/extension-subscript: ^2.2.0 => 2.6.6 @tiptap/extension-superscript: ^2.2.0 => 2.6.6 @tiptap/extension-table: ^2.2.0 => 2.6.6 @tiptap/extension-table-cell: ^2.2.0 => 2.6.6 @tiptap/extension-table-header: ^2.2.0 => 2.6.6 @tiptap/extension-table-row: ^2.2.0 => 2.6.6 @tiptap/extension-text-align: ^2.2.0 => 2.6.6 @tiptap/extension-text-style: ^2.2.0 => 2.6.6 @tiptap/extension-underline: ^2.2.0 => 2.6.6 @tiptap/pm: ^2.2.0 => 2.6.6 @tiptap/react: ^2.2.0 => 2.6.6 @tiptap/starter-kit: ^2.2.0 => 2.6.6 @types/aws-lambda: ^8.10.145 => 8.10.145 @types/draft-js: ^0.11.17 => 0.11.18 @types/lodash: ^4.14.202 => 4.17.7 @types/node: ^20.11.11 => 20.16.5 @types/react: ^18.2.48 => 18.3.6 @types/react-dom: ^18.2.18 => 18.3.0 @types/three: ^0.164.0 => 0.164.1 (0.163.0) @typescript-eslint/eslint-plugin: ^6.20.0 => 6.21.0 @typescript-eslint/parser: ^6.20.0 => 6.21.0 @vanilla-extract/css: ^1.14.1 => 1.15.5 @vanilla-extract/dynamic: ^2.1.0 => 2.1.2 @vanilla-extract/recipes: ^0.5.3 => 0.5.5 @vanilla-extract/sprinkles: ^1.6.1 => 1.6.3 @vanilla-extract/vite-plugin: ^4.0.1 => 4.0.15 @vitejs/plugin-react: ^4.2.1 => 4.3.1 aws-amplify: ^6.6.0 => 6.6.1 aws-amplify/adapter-core: undefined () aws-amplify/analytics: undefined () aws-amplify/analytics/kinesis: undefined () aws-amplify/analytics/kinesis-firehose: undefined () aws-amplify/analytics/personalize: undefined () aws-amplify/analytics/pinpoint: undefined () aws-amplify/api: undefined () aws-amplify/api/server: undefined () aws-amplify/auth: undefined () aws-amplify/auth/cognito: undefined () aws-amplify/auth/cognito/server: undefined () aws-amplify/auth/enable-oauth-listener: undefined () aws-amplify/auth/server: undefined () aws-amplify/data: undefined () aws-amplify/data/server: undefined () aws-amplify/datastore: undefined () aws-amplify/in-app-messaging: undefined () aws-amplify/in-app-messaging/pinpoint: undefined () aws-amplify/push-notifications: undefined () aws-amplify/push-notifications/pinpoint: undefined () aws-amplify/storage: undefined () aws-amplify/storage/s3: undefined () aws-amplify/storage/s3/server: undefined () aws-amplify/storage/server: undefined () aws-amplify/utils: undefined () aws-cdk: ^2.156.0 => 2.158.0 aws-cdk-lib: ^2.156.0 => 2.158.0 aws-sdk: ^2.1691.0 => 2.1691.0 clsx: ^2.1.0 => 2.1.1 constructs: ^10.3.0 => 10.3.0 dotenv: ^16.4.1 => 16.4.5 esbuild: ^0.23.1 => 0.23.1 (0.21.5) eslint: ^8.56.0 => 8.57.1 eslint-plugin-import: ^2.29.1 => 2.30.0 eslint-plugin-jsx-a11y: ^6.8.0 => 6.10.0 eslint-plugin-react: ^7.33.2 => 7.36.1 eslint-plugin-react-hooks: ^4.6.0 => 4.6.2 eslint-plugin-react-refresh: ^0.4.5 => 0.4.12 git: ^0.1.5 => 0.1.5 html-react-parser: ^5.1.1 => 5.1.16 jwt-decode: ^4.0.0 => 4.0.0 lodash: ^4.17.21 => 4.17.21 nice-color-palettes: ^3.0.0 => 3.0.0 node-html-parser: ^6.1.13 => 6.1.13 pluralize: ^8.0.0 => 8.0.0 react: ^18.3.1 => 18.3.1 react-dom: ^18.2.0 => 18.3.1 react-icons: ^5.0.1 => 5.3.0 react-intersection-observer: ^9.13.0 => 9.13.1 react-redux: ^9.1.0 => 9.1.2 react-refresh: ^0.14.0 => 0.14.2 react-router-dom: ^6.22.1 => 6.26.2 react-use-measure: ^2.1.1 => 2.1.1 sass: ^1.70.0 => 1.78.0 test: 0.1.0 three: ^0.164.1 => 0.164.1 ts-node: ^10.9.2 => 10.9.2 tsx: ^4.19.0 => 4.19.1 typescript: ^5.5.4 => 5.6.2 (4.4.4, 4.9.5) usehooks-ts: ^2.14.0 => 2.16.0 vite: ^5.0.12 => 5.4.6 vite-plugin-eslint: ^1.8.1 => 1.8.1 npmGlobalPackages: @aws-amplify/cli: 12.12.6 @types/node: 20.10.0 classnames: 2.3.2 depcheck: 1.4.7 git-install: 0.5.2 git: 0.1.5 nodemon: 3.0.1 npm-check-updates: 16.14.14 npm: 10.8.2 react-scripts: 5.0.1 sass: 1.69.5 typescript: 5.3.2 ```

Describe the bug

When using a join table to create a many-to-many relationship, when I get the data from one side of the table or the other, the objects returned in the array are the join table objects, not the objects on the other side of the table. Is this normal or expected? It seems to be an odd behavior especially if I'm wanting to nest the data properly. Here's my example

import { a } from '@aws-amplify/backend'

export const ModifierSystem = a
  .model({
    id: a.id(),
    systemId: a.id().required(),
    system: a.belongsTo('System', 'systemId'),
    categories: a.hasMany('ModifierCategory', 'modifierSystemId'),
    types: a.hasMany('ModifierType', 'modifierSystemId'),
    methods: a.hasMany('ModifierMethod', 'modifierSystemId'),
  })
  .authorization((allow) => [allow.owner(), allow.authenticated().to(['read'])])

export const ModifierCategoryTypeBridge = a
  .model({
    id: a.id(),
    categoryId: a.id().required(),
    category: a.belongsTo('ModifierCategory', 'categoryId'),
    typeId: a.id().required(),
    type: a.belongsTo('ModifierType', 'typeId'),
    methods: a.hasMany('ModifierCategoryMethodBridge', 'categoryTypeId'),
  })
  .authorization((allow) => [allow.owner(), allow.authenticated().to(['read'])])

export const ModifierCategoryMethodBridge = a
  .model({
    id: a.id(),
    categoryTypeId: a.id().required(),
    categoryType: a.belongsTo('ModifierCategoryTypeBridge', 'categoryTypeId'),
    methodId: a.id().required(),
    method: a.belongsTo('ModifierMethod', 'methodId'),
  })
  .authorization((allow) => [allow.owner(), allow.authenticated().to(['read'])])

export const ModifierTypeMethodBridge = a
  .model({
    id: a.id(),
    typeId: a.id().required(),
    type: a.belongsTo('ModifierType', 'typeId'),
    methodId: a.id().required(),
    method: a.belongsTo('ModifierMethod', 'methodId'),
  })
  .authorization((allow) => [allow.owner(), allow.authenticated().to(['read'])])

export const ModifierCategory = a
  .model({
    id: a.id(),
    value: a.string().required(),
    label: a.string().required(),
    tooltip: a.string(),
    modifierSystemId: a.id().required(),
    modifierSystem: a.belongsTo('ModifierSystem', 'modifierSystemId'),
    types: a.hasMany('ModifierCategoryTypeBridge', 'typeId'),
  })
  .authorization((allow) => [allow.owner(), allow.authenticated().to(['read'])])

export const ModifierType = a
  .model({
    id: a.id(),
    value: a.string().required(),
    label: a.string().required(),
    tooltip: a.string(),
    modifierSystemId: a.id().required(),
    modifierSystem: a.belongsTo('ModifierSystem', 'modifierSystemId'),
    methods: a.hasMany('ModifierTypeMethodBridge', 'methodId'),
    categories: a.hasMany('ModifierCategoryTypeBridge', 'categoryId'),
  })
  .authorization((allow) => [allow.owner(), allow.authenticated().to(['read'])])

export const ModifierMethod = a
  .model({
    id: a.id(),
    value: a.string().required(),
    label: a.string().required(),
    tooltip: a.string(),
    modifierSystemId: a.id().required(),
    modifierSystem: a.belongsTo('ModifierSystem', 'modifierSystemId'),
    types: a.hasMany('ModifierTypeMethodBridge', 'typeId'),
    categoryTypes: a.hasMany('ModifierCategoryMethodBridge', 'methodId'),
  })
  .authorization((allow) => [allow.owner(), allow.authenticated().to(['read'])])

I'm actually trying to create a many-to-many-to-many type of nested situation, but that's a different question. First, I need to solve the first join table issue. Now, when I query the even just the ModifierCategory model like such

const categories = await client.models.ModifierCategory.list({
        selectionSet: ['types.*'],
      })

I get back an array of objects that are the join table objects, not the types from the ModifierTypes data table. However, if I query like this

const categories = await client.models.ModifierCategory.list({
        selectionSet: ['types.type.*'],
      })

I see the type objects, but they're nested under the objects under the types array like this

"types": [
    {
      "type": {
        "id": "0ef0e6a9-184d-4e59-bb36-de6672c18dbf",
        "value": "penalty",
        "label": "Penalty",
        "tooltip": "Applies penalty to selected category based on selected method.",
        "modifierSystemId": "6b9c9e69-8f79-4451-bb6f-c952b1d3d865",
        "createdAt": "2024-09-13T11:06:00.000Z",
        "updatedAt": "2024-09-13T11:06:00.000Z",
        "owner": "master"
      }
    }
  ]

Again, if this was the expected behavior, I'll close this, but it makes nested data that are separated models a bit difficult with many-to-many relationships.

Expected behavior

I expected to see the actual types nested under the types array of the query rather than the objects of the join table.

Reproduction steps

Create two models with a many-to-many relationship and use a join table to connect the relationships. Then query one side or the other as nested.

Code Snippet

// Put your code below this line.

Log output

``` // Put your logs below this line ```

aws-exports.js

No response

Manual configuration

No response

Additional configuration

No response

Mobile Device

No response

Mobile Operating System

No response

Mobile Browser

No response

Mobile Browser Version

No response

Additional information and screenshots

No response

[chrisbonifacio]

Hi @MPiland this is expected behavior in Gen 2. I've marked it as a feature request for the team to discuss and consider.

[MPiland]

Hi @chrisbonifacio thanks for the feedback! Can we add to that feature request to also allow a many-to-many-to-many or ternary type of join table? Or at least have a solution/example of how that should be done?

Using the models above it would be something like:

• Categories have access to types, but not all categories can access all types
• Types have access to methods and many methods are shared across the types.
• Categories only have access to the methods within their allowed types, but they don't necessarily have access to all the methods of that type

Originally I had a join table for categories to types and then used the id from that object to map to a categoryType to method but because of the way the join data comes out, it didn't work.