[Issue] DataStore predicates don't work for internal arrays

dmitryusikriseapps commented 8 months ago

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, Next.js

Amplify APIs

DataStore

Amplify Categories

auth, storage

Environment information

``` # Put output below this line System: OS: macOS 14.0 CPU: (10) arm64 Apple M1 Max Memory: 1.80 GB / 32.00 GB Shell: 5.9 - /bin/zsh Binaries: Node: 16.19.1 - ~/.nvm/versions/node/v16.19.1/bin/node Yarn: 1.22.19 - /usr/local/bin/yarn npm: 9.8.0 - ~/.nvm/versions/node/v16.19.1/bin/npm pnpm: 8.6.6 - ~/.nvm/versions/node/v16.19.1/bin/pnpm Watchman: 2023.09.25.00 - /opt/homebrew/bin/watchman Browsers: Chrome: 118.0.5993.88 Safari: 17.0 npmPackages: @abelmkr/amplify: 6.3.6 => 6.3.6 @abelmkr/appsync-data-utils: 6.3.7 => 6.3.7 @abelmkr/appsync-job-rate-transformer: 6.3.7 => 6.3.7 @abelmkr/general-shared-utils: 2.3.1 => 2.3.1 @abelmkr/job-rate-calculator: 3.4.1 => 3.4.1 @abelmkr/job-rate-types: 3.4.1 => 3.4.1 @abelmkr/money: 3.3.0 => 3.3.0 @ampproject/toolbox-optimizer: undefined () @aws-amplify/api: 5.4.5 => 5.4.5 @aws-amplify/api/internals: undefined () @aws-amplify/auth: 5.6.5 => 5.6.5 @aws-amplify/core: 5.8.5 => 5.8.5 @aws-amplify/core/internals/aws-client-utils: undefined () @aws-amplify/core/internals/aws-client-utils/composers: undefined () @aws-amplify/core/internals/aws-clients/pinpoint: undefined () @aws-amplify/datastore: 4.7.5 => 4.7.5 @aws-amplify/storage: 5.9.5 => 5.9.5 @babel/core: undefined () @babel/runtime: 7.15.4 @commitlint/cli: ^17.7.1 => 17.7.1 @cypress/angular: 0.0.0-development @cypress/mount-utils: 0.0.0-development @cypress/react: 0.0.0-development @cypress/react18: 0.0.0-development @cypress/svelte: 0.0.0-development @cypress/vue: 0.0.0-development @cypress/vue2: 0.0.0-development @edge-runtime/cookies: 3.4.1 @edge-runtime/ponyfill: 2.4.0 @edge-runtime/primitives: 3.1.1 @elevai/commitlint-config-github: ^0.1.1 => 0.1.1 @elevai/commitlint-plugin-github: ^0.1.1 => 0.1.1 @emotion/react: ^11.11.1 => 11.11.1 @emotion/styled: ^11.11.0 => 11.11.0 @hapi/accept: undefined () @hookform/resolvers: ^3.3.1 => 3.3.1 @hookform/resolvers/ajv: 1.0.0 @hookform/resolvers/arktype: 1.0.0 @hookform/resolvers/class-validator: 1.0.0 @hookform/resolvers/computed-types: 1.0.0 @hookform/resolvers/io-ts: 1.0.0 @hookform/resolvers/joi: 1.0.0 @hookform/resolvers/nope: 1.0.0 @hookform/resolvers/superstruct: 1.0.0 @hookform/resolvers/typanion: 1.0.0 @hookform/resolvers/typebox: 1.0.0 @hookform/resolvers/valibot: 1.0.0 @hookform/resolvers/vest: 1.0.0 @hookform/resolvers/yup: 1.0.0 @hookform/resolvers/zod: 1.0.0 @mswjs/interceptors: undefined () @mui/icons-material: ^5.14.12 => 5.14.12 @mui/material: ^5.14.12 => 5.14.12 @mui/x-date-pickers: ^6.16.1 => 6.16.1 @napi-rs/triples: undefined () @next/font: undefined () @next/react-dev-overlay: undefined () @opentelemetry/api: undefined () @reduxjs/toolkit: ^1.9.5 => 1.9.5 @reduxjs/toolkit-query: 1.0.0 @reduxjs/toolkit-query-react: 1.0.0 @segment/ajv-human-errors: undefined () @svgr/webpack: ^8.1.0 => 8.1.0 @testing-library/jest-dom: ^6.1.2 => 6.1.3 @testing-library/react: ^14.0.0 => 14.0.0 @types/deep-equal: ^1.0.1 => 1.0.1 @types/google-map-react: ^2.1.7 => 2.1.7 @types/jest: ^29.5.4 => 29.5.4 @types/node: 20.5.9 => 20.5.9 (20.4.7, 18.0.0, 16.18.50) @types/react: 18.2.21 => 18.2.21 (18.0.14) @types/react-dom: 18.2.7 => 18.2.7 (18.0.6) @types/tinycolor2: ^1.4.3 => 1.4.3 @types/uuid: ^9.0.3 => 9.0.3 @typescript-eslint/eslint-plugin: ^6.5.0 => 6.6.0 @typescript-eslint/parser: 6.5.0 => 6.5.0 (6.6.0, 5.38.1) @vercel/nft: undefined () @vercel/og: undefined () acorn: undefined () amazon-cognito-identity-js: 6.3.6 => 6.3.6 amazon-cognito-identity-js/internals: undefined () amphtml-validator: undefined () anser: undefined () arg: undefined () assert: undefined () async-retry: undefined () async-sema: undefined () autoprefixer: 10.4.15 => 10.4.15 babel-packages: undefined () babel-plugin-inline-react-svg: ^2.0.2 => 2.0.2 browserify-zlib: undefined () browserslist: undefined () buffer: undefined () bytes: undefined () chalk: undefined () ci-info: undefined () cli-select: undefined () client-only: 0.0.1 comment-json: undefined () compression: undefined () conf: undefined () constants-browserify: undefined () content-disposition: undefined () content-type: undefined () cookie: undefined () cross-spawn: undefined () crypto-browserify: undefined () css.escape: undefined () cypress: ^13.1.0 => 13.1.0 data-uri-to-buffer: undefined () dayjs: ^1.11.9 => 1.11.9 (1.11.5) debug: undefined () deep-equal: ^2.2.2 => 2.2.2 devalue: undefined () domain-browser: undefined () dpdm: ^3.13.1 => 3.13.1 edge-runtime: undefined () eslint: ^8.48.0 => 8.49.0 (8.24.0) eslint-config-next: ^13.4.19 => 13.4.19 eslint-config-prettier: ^9.0.0 => 9.0.0 eslint-import-resolver-typescript: ^3.6.0 => 3.6.0 eslint-plugin-destructuring: ^2.2.1 => 2.2.1 eslint-plugin-import: ^2.28.1 => 2.28.1 (2.26.0) eslint-plugin-jest: ^27.2.3 => 27.2.3 eslint-plugin-jest-formatting: ^3.1.0 => 3.1.0 eslint-plugin-prettier: ^5.0.0 => 5.0.0 eslint-plugin-react: ^7.33.2 => 7.33.2 (7.31.8) eslint-plugin-react-hooks: ^4.6.0 => 4.6.0 eslint-plugin-simple-import-sort: ^10.0.0 => 10.0.0 events: undefined () find-cache-dir: undefined () find-up: undefined () fresh: undefined () get-orientation: undefined () glob: undefined () google-map-react: ^2.2.1 => 2.2.1 gzip-size: undefined () http-proxy: undefined () http-proxy-agent: undefined () https-browserify: undefined () https-proxy-agent: undefined () husky: ^8.0.3 => 8.0.3 i18next: ^23.4.6 => 23.5.1 icss-utils: undefined () ignore-loader: undefined () image-size: undefined () is-animated: undefined () is-docker: undefined () is-wsl: undefined () jest: ^29.6.4 => 29.6.4 jest-environment-jsdom: ^29.6.4 => 29.6.4 jest-worker: undefined () json5: undefined () jsonwebtoken: undefined () lint-staged: ^14.0.1 => 14.0.1 loader-runner: undefined () loader-utils: undefined () lodash.curry: undefined () lru-cache: undefined () material-react-table: ^1.15.0 => 1.15.0 micromatch: undefined () mini-css-extract-plugin: undefined () nanoid: undefined () native-url: undefined () neo-async: undefined () next: 13.5.3 => 13.5.3 node-fetch: undefined () node-html-parser: undefined () ora: undefined () os-browserify: undefined () p-limit: undefined () path-browserify: undefined () platform: undefined () postcss: ^8.4.29 => 8.4.29 (8.4.14) postcss-flexbugs-fixes: ^5.0.2 => 5.0.2 () postcss-modules-extract-imports: undefined () postcss-modules-local-by-default: undefined () postcss-modules-scope: undefined () postcss-modules-values: undefined () postcss-preset-env: undefined () postcss-safe-parser: undefined () postcss-scss: undefined () postcss-value-parser: undefined () preline: ^1.9.0 => 1.9.0 prettier: ^3.0.3 => 3.0.3 (2.7.1) prettier-eslint: ^15.0.1 => 15.0.1 process: undefined () punycode: undefined () querystring-es3: undefined () raw-body: undefined () react: 18.2.0 => 18.2.0 react-builtin: undefined () react-calendar: ^4.6.0 => 4.6.0 react-dom: 18.2.0 => 18.2.0 react-dom-builtin: undefined () react-dom-experimental-builtin: undefined () react-experimental-builtin: undefined () react-hook-form: 7.46.0 => 7.46.0 react-i18next: ^13.2.2 => 13.2.2 react-is: 18.2.0 react-redux: ^8.1.2 => 8.1.2 react-refresh: 0.12.0 react-server-dom-webpack-builtin: undefined () react-server-dom-webpack-experimental-builtin: undefined () redux-persist: ^6.0.0 => 6.0.0 redux-persist/integration/react: undefined () regenerator-runtime: 0.13.4 sass-loader: undefined () scheduler-builtin: undefined () scheduler-experimental-builtin: undefined () schema-utils: undefined () semver: undefined () send: undefined () server-only: 0.0.1 setimmediate: undefined () shell-quote: undefined () sort-json: ^2.0.1 => 2.0.1 source-map: undefined () stacktrace-parser: undefined () stream-browserify: undefined () stream-http: undefined () string-hash: undefined () string_decoder: undefined () strip-ansi: undefined () swr: ^2.2.2 => 2.2.2 tailwind-merge: ^1.14.0 => 1.14.0 tailwindcss: ^3.3.3 => 3.3.3 tar: undefined () terser: undefined () text-table: undefined () timers-browserify: undefined () tinycolor2: ^1.6.0 => 1.6.0 tty-browserify: undefined () typescript: ^5.2.2 => 5.2.2 (5.1.6, 4.8.4) ua-parser-js: undefined () undici: undefined () unistore: undefined () url-loader: ^4.1.1 => 4.1.1 util: undefined () uuid: ^9.0.0 => 9.0.0 (8.3.2, 3.4.0) vm-browserify: undefined () watchpack: undefined () web-vitals: undefined () webpack: undefined () webpack-sources: undefined () ws: undefined () zod: ^3.22.2 => 3.22.2 (, 3.21.4) npmGlobalPackages: @aws-amplify/cli: 11.0.3 corepack: 0.15.1 detox-cli: 20.0.0 eas-cli: 3.10.0 expo-cli: 6.3.2 npm: 9.8.0 pnpm: 8.6.6 serve: 14.2.1 turbo: 1.10.8 ```

Describe the bug

When querying items from DataStore, it's not possible to build predicates for internal arrays. But, the typing system is working for the internal fields.

Another question - is it possible to build predicates based on the length of internal arrays? For example, to retrieve items that have a skills array with length 4.

Expected behavior

I checked the unit tests inside the dataStore package, and predicates worked there for the internal arrays, but only if they were async (using the AsyncCollection and @hasMany relationship).

Reproduction steps

Introduce a remote model that includes an internal array of objects.
Try to retrieve some items by building a predicate for this internal array.

Code Snippet

// Put your code below this line.
type Worker = {
  readonly id: string;
  readonly cognitoUserId: string;
  readonly cognitoIdentityId: string;
  readonly firstName: string;
  readonly lastName: string;
  readonly phone: string;
  readonly email: string;
  readonly profilePicture?: AmplifyAsset;
  readonly isPhoneVerified?: boolean;
  readonly devices: UserDevice[];
  readonly remainingOnboardingActivities: OnboardingActivities;
  readonly skills?: WorkerSkill[];
  readonly gear?: WorkerGear[];
  readonly certifications?: WorkerCertification[];
  readonly locations?: WorkerLocation[];
  readonly availability?: WorkerAvailability;
  readonly currentJobApplications?: WorkerCurrentJobApplication[];
  readonly historicalJobApplications?: WorkerHistoricalJobApplication[];
  readonly currentJobInvitations?: WorkerCurrentJobInvitation[];
  readonly historicalJobInvitations?: WorkerHistoricalJobInvitation[];
  readonly currentJobs?: WorkerCurrentJob[];
  readonly createdBy?: string;
  readonly lastEdited?: LastEdited;
  readonly lastEditedByAdmin?: LastEditedByAdmin;
  readonly createdAt?: string;
  readonly updatedAt?: string;
}

type WorkerSkill = {
  readonly skillId: string;
  readonly experienceLevelId: string;
  readonly skillLevelId?: string;
}

const getWorkers = async (params: QueryParams): Promise<Worker[]> => {
  try {
    const workers = await DataStore.query(
      DataStoreModels.Worker,
      worker => worker.skills.skillId.eq('electician'),
      {
        ...params.pagination,
        sort: worker => worker.createdAt(SortDirection.DESCENDING),
      }
    );

    return workersParsers.parseWorkers(workers);
  } catch (error) {
    throw logger.logError('getWorkers', error, { params });
  }
};

Log output

``` // Put your logs below this line Cannot read properties of undefined (reading 'eq') ```

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 commented 8 months ago

Hi @dmitryusikriseapps 👋 thanks for raising this issue. I'll try reproducing this and confirming with the team what the behavior should be.

I checked the unit tests inside the dataStore package, and predicates worked there for the internal arrays, but only if they were async (using the AsyncCollection and @hasMany relationship).

To make sure I understand your use case, this line sounds like you would like to be able to use predicates on array fields that are not relationships. As in, WorkerSkill in this case is not annotated with the @model directive in the graphql schema, but Worker is. Is that correct?

Another question - is it possible to build predicates based on the length of internal arrays? For example, to retrieve items that have a skills array with length 4.

Off the top of my head, I've never seen this before so I don't think there is a predicate like count. You'd probably have to keep track of the count on a separate field on the model to use in the predicates like this:

const workers = await DataStore.query(
      DataStoreModels.Worker,
      worker => worker.skills.count.eq(4)
    );

dmitryusikriseapps commented 8 months ago

Hey @chrisbonifacio, thanks for the quick answer!

To make sure I understand your use case, this line sounds like you would like to be able to use predicates on array fields that are not relationships. As in, WorkerSkill in this case is not annotated with the @model directive in the graphql schema, but Worker is. Is that correct?

Correct!

Off the top of my head, I've never seen this before so I don't think there is a predicate like count. You'd probably have to keep track of the count on a separate field on the model to use in the predicates like this:

Thanks!

danrivett commented 8 months ago

Hi Chris, I'm working with @dmitryusikriseapps on this, and it's definitely something we'd love to see supported if possible, and if needed we'd be happy to look into possible solutions ourselves and contributing a PR if we could be given a starting point to look at.

Background

For context, we've developed an internally-facing website which shows all workers in our platform using material-react-table which is bound to DataStore and we want to support column filtering by constructing the corresponding DataStore predicates.

A common use case is to filter by a Worker's certified skill (such as electrician), and those skills are stored in a nested array in the Worker model type, rather than using a @hasMany relationship since a Worker will have a limited number of skills on their profile and so it avoids multiple DynamoDB queries to join the tables together.

We believe this to be much more efficient to retrieve since we have other nested arrays for other bounded data such as their professional certifications etc. If they were all stored in separate tables the number of DynamoDB queries generated across all users can quickly add up, plus it would add latency when we run non-DataStore AppSync queries in our backend integration layer.

So that's why we're investigating how we can construct DataStore queries that supports filtering on nested arrays.

Further Details

As extra details, we'd like to be able to construct predicates based on an array of objects (rather than primatives), such as Worker containing an array of WorkerSkill types, and match on one of its fields.

e.g. matching by WorkerSkill.skillId field:

worker.skills.skillId.eq('electician')

But we also envisage a nested array of primitives, such as strings, and so we'd want to filter based on those. For example being able to state whether an array contains or doesn't contain a given value. e.g.:

worker.groups.contains('administrator')

So that's the reasoning behind this request, and I think if it was possible, it would make displaying such a table of data (using material-react-table or similar) much more performant.

Currently we have to load all values into memory from DataStore and then filter them as a post-processing step, rather than have DataStore more efficiently filter the data itself.

chrisbonifacio commented 8 months ago

Hi @danrivett. Thank you for explaining your use case.

I will mark this as a feature request for the team to track and consider.

dmitryusikriseapps commented 8 months ago

Another example where nested predicates don't work (where the nested field is not an array):

type ContractorProject = {
  readonly id: string;
  readonly contractorId: string;
  readonly cognitoUserId: string;
  readonly status: ContractorProjectStatus | keyof typeof ContractorProjectStatus;
  readonly title: string;
  readonly description?: string;
  readonly location: ResolvedAddressLocation;
  readonly schedule?: JobSchedule;
  readonly jobs?: ContractorJob[];
  readonly createdTimestamp?: string;
  readonly deletedTimestamp?: string;
  readonly publishedTimestamp?: string;
  readonly suspendedTimestamp?: string;
  readonly resumedTimestamp?: string;
  readonly endedTimestamp?: string;
  readonly createdBy?: string;
  readonly lastEdited?: LastEdited;
  readonly lastEditedByAdmin?: LastEditedByAdmin;
  readonly createdAt?: string;
  readonly updatedAt?: string;
}

type ResolvedAddressLocation = {
  readonly coordinates: GeoPoint;
  readonly address: Address;
  readonly formattedAddress?: string;
}

const projects = await DataStore.query(
      DataStoreModels.ContractorProject,
      // criteria,
      project => project.location.formattedAddress.contains('ave'),
      {
        ...params.pagination,
        sort: params.sort,
      }
    );

aws-amplify / amplify-js