Query models where a field is one of n values in a given list

guplem commented 2 years ago

Description

I do believe that it is a basic need to be able to query data filtering using a list.

Example: Amplify.DataStore.query(UserData.classType, where: UserData.id.in(participants)) In this case, "participants" is a list of strings that contains UserData's IDs.

Currently, I can only think about doing a request per each participant. Which is not efficient, neither as fast as desired.

Am I missing something, or is this a missing feature?

Platforms

[X] iOS
[X] Android

Android Device/Emulator API Level

No response

Environment

Doctor summary (to see all details, run flutter doctor -v):
[√] Flutter (Channel stable, 2.10.3, on Microsoft Windows [Version 10.0.19044.1645], locale en-US)
[√] Android toolchain - develop for Android devices (Android SDK version 31.0.0)
[√] Chrome - develop for the web
[√] Visual Studio - develop for Windows (Visual Studio Build Tools 2019 16.11.10)
[√] Android Studio (version 2020.3)
[√] Android Studio (version 2021.1)
[√] Connected device (3 available)
[√] HTTP Host Availability

• No issues found!

Dependencies

Dart SDK 2.16.1
Flutter SDK 2.10.3
time_manager 1.0.0+1

dependencies:
- amplify_api 0.4.2 [amplify_api_plugin_interface amplify_core collection flutter meta plugin_platform_interface]
- amplify_auth_cognito 0.4.2 [flutter amplify_auth_plugin_interface amplify_core amplify_auth_cognito_android amplify_auth_cognito_ios collection plugin_platform_interface]
- amplify_authenticator 0.1.0 [amplify_auth_cognito amplify_auth_plugin_interface amplify_core amplify_flutter collection flutter flutter_localizations intl]
- amplify_datastore 0.4.2 [flutter amplify_datastore_plugin_interface amplify_core plugin_platform_interface meta collection async]
- amplify_flutter 0.4.2 [amplify_analytics_plugin_interface amplify_api_plugin_interface amplify_auth_plugin_interface amplify_core amplify_datastore_plugin_interface amplify_storage_plugin_interface collection flutter json_annotatio
n meta plugin_platform_interface]
- cupertino_icons 1.0.4
- duration_picker 1.1.0+1 [flutter_lints flutter]
- flutter 0.0.0 [characters collection material_color_utilities meta typed_data vector_math sky_engine]
- restart_app 1.1.0 [flutter flutter_web_plugins]

transitive dependencies:
- amplify_analytics_plugin_interface 0.4.2 [amplify_core flutter meta]
- amplify_api_plugin_interface 0.4.2 [amplify_core collection flutter json_annotation meta]
- amplify_auth_cognito_android 0.4.2 [flutter]
- amplify_auth_cognito_ios 0.4.2 [amplify_core flutter]
- amplify_auth_plugin_interface 0.4.2 [flutter meta amplify_core]
- amplify_core 0.4.2 [flutter plugin_platform_interface collection date_time_format meta uuid]
- amplify_datastore_plugin_interface 0.4.2 [flutter meta collection amplify_core]
- amplify_storage_plugin_interface 0.4.2 [flutter meta amplify_core]
- async 2.8.2 [collection meta]
- characters 1.2.0
- clock 1.1.0
- collection 1.15.0
- crypto 3.0.1 [collection typed_data]
- date_time_format 2.0.1
- flutter_lints 1.0.4 [lints]
- flutter_localizations 0.0.0 [flutter intl characters clock collection material_color_utilities meta path typed_data vector_math]
- flutter_web_plugins 0.0.0 [flutter js characters collection material_color_utilities meta typed_data vector_math]
- intl 0.17.0 [clock path]
- js 0.6.3
- json_annotation 4.4.0 [meta]
- lints 1.0.1
- material_color_utilities 0.1.3
- meta 1.7.0
- path 1.8.0
- plugin_platform_interface 2.1.2 [meta]
- sky_engine 0.0.99
- typed_data 1.3.0 [collection]
- uuid 3.0.6 [crypto]
- vector_math 2.1.1

CLI Version

8.0.0

Jordan-Nelson commented 2 years ago

@guplem - There is no predicate that is exactly what you are looking for. You could use .or() to join them (see below), although I think there is a limit to the number of conditions you will be able to join together. You may also find issues with how this scales depending on the number of participants.

You could create that Query Predicate dynamically if the list is not a constant length, although I would caution against that if the list can be any length for the reasons mentioned above.

final participants = ['id1', 'id2', 'id3'];
Amplify.DataStore.query(
  Post.classType,
  where: Post.ID
      .eq(participants[0])
      .or(Post.ID.eq(participants[1]))
      .or(Post.ID.eq(participants[2])),
);

Do you mind sharing a bit more about your use case? I think creating some sort of user group may be more appropriate.

guplem commented 2 years ago

I do believe that on my use case the best would be to do it dynamically. Usually, the lists of participants are not very long, but they vary in size.

In my use case, I have an event class. That "event" can be joined by "users" (stored data in a class named userData). Additionally, I have a participant class that basically has an event.id, a userData.user and a boolean accepted (so users can invite each other, accept or deny the invitation).

Currently, if I want to display a list of all users who have joined (and accepted an event) I do:

Get all participations (accepted) related to an event
For each participation, get the user who has accepted it (I could concatenate with the .or as you explained)

If I understand correctly, you are recommending me to create an eventParticipations (a group linking an event.id with an array of participant.id) and remove the task.id from the participant class. That would work great to easily get all participations of an event, but it would still be an issue to get all the data of each user from a list of participations (still need to query for any object that has a field contained in a local array).

I suppose that another thing that I could try would be to create an array field in the event class to store all accepted participations and another one with the pending. However, once I get the list of the users, I need the UserData of each, and here we are again: I cannot query looking for entries matching any identifier in a list.

I hope I am explaining myself properly.

I know that I could create relations between classes/models but to be hones, I noticed that they are not much more "advanced" neither easy to use than manually doing them. I would appreciate it if a many-to-many relation could give you access to the objects of the related class directly instead of providing an ID of an object containing all the IDs to query etc etc (but that is another topic)

Jordan-Nelson commented 2 years ago

@guplem Can you share the portion of your schema that defines event, participant, and userData/user?

Jordan-Nelson commented 2 years ago

I think you can probably achieve what you want with the current datastore functionality, although it may require some schema changes.

Below is one example of a schema that would allow for the basic use case you have described. In the schema below, UserEvent is similar to what you are using Participant for.

type User @model {
  id: ID!
  name: String!
  userEvents: [UserEvent] @hasMany
}

type Event @model {
  id: ID!
  name: String!
  userEvents: [UserEvent] @hasMany
}

type UserEvent @model {
  id: ID!
  user: User! @belongsTo
  event: Event! @belongsTo
  status: EventStatus!
}

enum EventStatus {
  invited
  accepted
  declined
}

With this schema you could perform the following queries to get all the events for a given user, or all the users for a given event.

// get all UserEvents for a given event
final List<UserEvent> userEvents = Amplify.DataStore.query(
  UserEvent.classType,
  where: UserEvent.EVENT.eq('some-event-id'),
);

// list all Users for an Event
final List<User> users = userEvents.map((userEvent) => userEvent.user).toList()

// get all UserEvents for a given user
final List<UserEvent> userEvents = Amplify.DataStore.query(
  UserEvent.classType,
  where: UserEvent.EVENT.eq('some-user-id'),
);

// List all Events for a User
final List<Event> users = userEvents.map((userEvent) => userEvent.event).toList()

guplem commented 2 years ago

@guplem Can you share the portion of your schema that defines event, participant, and userData/user?

Sorry for the delay. However, the schema that I did describe with the creation of the issue was half theoretical, and the other half has been already altered (and was not usable).

I do believe that a schema similar to yours is as accurate as it needs to be.

I think you can probably achieve what you want with the current datastore functionality, although it may require some schema changes.

Below is one example of a schema that would allow for the basic use case you have described. In the schema below, UserEvent is similar to what you are using Participant for.
type User @model {
  id: ID!
  name: String!
  userEvents: [UserEvent] @hasMany
}

type Event @model {
  id: ID!
  name: String!
  userEvents: [UserEvent] @hasMany
}

type UserEvent @model {
  id: ID!
  user: User! @belongsTo
  event: Event! @belongsTo
  status: EventStatus!
}

enum EventStatus {
  invited
  accepted
  declined
}
With this schema you could perform the following queries to get all the events for a given user, or all the users for a given event.
// get all UserEvents for a given event
final List<UserEvent> userEvents = Amplify.DataStore.query(
  UserEvent.classType,
  where: UserEvent.EVENT.eq('some-event-id'),
);

// list all Users for an Event
final List<User> users = userEvents.map((userEvent) => userEvent.user).toList()
// get all UserEvents for a given user
final List<UserEvent> userEvents = Amplify.DataStore.query(
  UserEvent.classType,
  where: UserEvent.EVENT.eq('some-user-id'),
);

// List all Events for a User
final List<Event> users = userEvents.map((userEvent) => userEvent.event).toList()

Yeah, theoretically, that would allow me to get all users from an event (and all events from a user).

That's the first thing I tried, but, if I am not mistaken, querying for UserEvents won't return neither the Event nor the User full information but only their IDs. Is this true, or did I do something wrong?

If what I've described is the expected behaviour, how am I supposed to query looking for a specific set of Users using a list/array of their IDs? (that was the reason of the creation of the issue and me trying to achieve this in many ways).

I have to say that I have never "typed" a schema. I have always used Amplify Studio's visual UI instead. I say this because the schema and code that you shared makes full sense to me, and it looks like it should work. Maybe I did something wrong with the visual UI or something is wrong with it (it is a bit finicky sometimes). I am away from home now, but as soon as I return and have time I will take a look again.

However, even tough this might be all I need to solve my current case, it doesn't fully remove the need for the feature that I was asking for in this issue. It is a perfectly feasible scenario to have some sort of call, local storage, ... returning a list of IDs of "database objects". I do believe that querying for all those objects would be impossible without calling the database asking for the information of one of them individually. Not an ideal solution.

Jordan-Nelson commented 2 years ago

That's the first thing I tried, but, if I am not mistaken, querying for UserEvents won't return neither the Event nor the User full information but only their IDs. Is this true, or did I do something wrong?

You should get the full User & Event object when querying UserEvents with that schema. It does depend how the schema is defined though. The full child model should be returned for@belongsTo relations, while only the ID will be returned for @hasOne relations.

However, even tough this might be all I need to solve my current case, it doesn't fully remove the need for the feature that I was asking for in this issue.

You are correct.

I am trying to get more info about what use case(s) this would help solve for before I mark this as a feature request. If there are clear use cases that are not achievable without this, it is much more likely that it will be picked up.

For use cases where the list of IDs is constant, or at least finite & small, I would suggest joining query predicates together. A new predicate could make this a little easier, although you could technically add an extension or method for creating the join dynamically if you have a use case for it. See example below. I have not tested this, but I believe this would work. Just to be clear, I would only recommend this if the list is constant, or at least finite & small.

// extension
extension QueryFieldX<T> on QueryField<T> {
  QueryPredicate isOneOf(List<T> items) {
    assert(items.isNotEmpty);
    if (items.length == 1) {
      return eq(items[0]);
    }
    QueryPredicateGroup queryPredicate = eq(items[0]).or(eq(items[1]));
    for (var i = 2; i < items.length; i++) {
      queryPredicate = queryPredicate.or(eq(items[i]));
    }
    return queryPredicate;
  }
}

// example use
Amplify.DataStore.observeQuery(
  Blog.classType,
  where: Blog.NAME.isOneOf(['foo', 'bar'])
)

For use cases where the list is not finite or very large, I think it would typically indicate that the schema was not set up in a way that best achieves the app's requirements. In those scenarios, changing the schema will likely lead to much more efficient querying than scanning the table to find models where a field equals one of any N number of items.

I am going to mark this as a feature request to track interest, but I think we will need more concrete use cases before looking into it further.

jamontesg commented 3 months ago

Hi @Jordan-Nelson , can you provide to me a piece of code for use you "extension method". I am trying to do a query with multiples conditions. Maybe this code could be work for me. I don't found more Information about this subject (multiple values condition) over internet. Kind Regards

Jordan-Nelson commented 3 months ago

@jamontesg the second example here shows how to use multiple predicates together

jamontesg commented 3 months ago

Thanks @Jordan-Nelson. I am interested on a predicator like isOneOf(['foo', 'bar']) . I have an array like ['normal','disabled','warning'.'deleted'] with multiples states . This array could have more or less conditions. I need something more dynamically like your "extension method". Thanks

Jordan-Nelson commented 3 months ago

@jamontesg is there a reason that the extension method above would not work?

jamontesg commented 3 months ago

@Jordan-Nelson this code give a lot. of errors :

// extension extension QueryFieldX<T> on QueryField<T> { QueryPredicate isOneOf(List<T> items) { assert(items.isNotEmpty); if (items.length == 1) { return eq(items[0]); } QueryPredicateGroup queryPredicate = eq(items[0]).or(eq(items[1])); for (var i = 2; i < items.length; i++) { queryPredicate = queryPredicate.or(eq(items[i])); } return queryPredicate; } }

"Undefined name 'extension'.\nTry correcting the name to one that is defined, or defining the name." "Undefined class 'QueryFieldX'.\nTry changing the name to the name of an existing class, or creating a "

I'm sorry but I couldn't find much information to use this code.

Regards

Jordan-Nelson commented 3 months ago

@jamontesg are you attempting to place the extension in inside of a method or class? extensions should be placed outside of any function or class. See https://dart.dev/language/extension-methods for more info about extensions in dart.

You do not have to use extensions fyi. You could also make this a method. Extensions allow you to use the fn as if it were part of the library, but a function would just as well. For example:

/// isOneOf method
QueryPredicate isOneOf<T>(
  QueryField<T> field,
  List<T> items,
) {
  assert(items.isNotEmpty);
  if (items.length == 1) {
    return field.eq(items[0]);
  }
  QueryPredicateGroup queryPredicate =
      field.eq(items[0]).or(field.eq(items[1]));
  for (var i = 2; i < items.length; i++) {
    queryPredicate = queryPredicate.or(field.eq(items[i]));
  }
  return queryPredicate;
}

// example use 
Amplify.DataStore.observeQuery(
  Post.classType,
  where: isOneOf(Post.TITLE, ['foo', 'bar']),
);

jamontesg commented 3 months ago

@Jordan-Nelson
Great, Thanks

Jordan-Nelson commented 3 months ago

No problem @jamontesg

aws-amplify / amplify-flutter