Foreign key on arrays where one item needs to match the current object's id

#1

I would like for there to be a many-to-many relationship between two types, and am trying to implement this with a foreign key as in the following example: I want the field organizations on type User to find all of the Organizations where the Organization's array field users contains this User. Is this kind of logic supported?

remix type User {
  /**
   * The Organizations that this User belongs to
   */
  organizations: [Organization] (users)

remix type Organization {
  /**
   * The Users that belong to this Organization
   */
  users: [User] schema name 'ORGUSRS'
#2

except that you misspelled the foreign key… yes.

should be:

organizations: [Organization] (user)

You could also define an intersection type.

type OrganizationUser {
  organization: Organization
  user: User
}

type Organization {
  intersections: [OrganizationUser]  (organization)
  users: [User] stored calc "intersections.user"
}

type User {
   intersections: [OrganizationUser]  (user)
  orgs: [Organization] stored calc "intersections.organization"
}
#3

Thanks! What are some pros/cons of using an intersection type instead?

#4

In general i think using intersection table is better when modelling a many-many relationship as it avoids foreign keys which have to search potentially large arrays. However, i am not on expert on that one…