Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Many-To-Many Relationship Query Problem #22

Closed
johannesschobel opened this issue Feb 19, 2020 · 5 comments
Closed

Many-To-Many Relationship Query Problem #22

johannesschobel opened this issue Feb 19, 2020 · 5 comments

Comments

@johannesschobel
Copy link
Contributor

Dear @doug-martin ,

and once again I write to you to describe an issue i have found ;) Sorry for bothering you again ;)
The issue i stumbled upon is with Many to Many relationships between entities.

Consider the following example:
I have two entities Organization and Doctor. Thereby, one organization has many doctors and one doctor can be a member in various organizations.

My models look like this (simplified):

@Entity('organizations', {})
export class OrganizationEntity {
  // ... various properties here

  @ManyToMany(
    type => DoctorEntity,
    member => member.organizations,
    {
      cascade: true,
      onDelete: 'CASCADE'
    }
  )
  @JoinTable({ name: 'organization_members' })
  members: DoctorEntity[];
}

@Entity('doctors', {})
export class DoctorEntity {
  // ... various properties here

  @ManyToMany(
    type => OrganizationEntity,
    organization => organization.members
  )
  organizations: OrganizationEntity[];
}

Then i set up my OrganizationResolver like this:

@Resolver(of => OrganizationObject)
export class OrganizationResolver extends CRUDResolver(OrganizationObject, {
  create: { many: { disabled: true }, CreateDTOClass: CreateOrganizationInput },
  delete: { disabled: true },
  update: { many: { disabled: true }, UpdateDTOClass: UpdateOrganizationInput },
  relations: {
    many: {
      members: {
        DTO: DoctorObject,
        relationName: 'members',
        nullable: true,
        disableRemove: true,
        disableUpdate: false
      }
    }
  }
}) {
  constructor(readonly service: OrganizationService) {
    super(service);
  }
}

Note that assigning a doctor (member) to an organization works like a charm, via the auto-generated endpoint from relations.many.members.disableUpdate: false.

However, i am NOT able to properly query (!) the members of an organization. Consider this query:

{
  organizations {
    edges {
      node {
        id,
        members {
          edges {
            node {
              id
            }
          }
        }
      }
    }
  }
}

results in this response:

{
  "data": {
    "organizations": {
      "edges": [
        {
          "node": {
            "id": "46fb76f1-781b-400b-8932-43ee9e809d1e",
            "members": {
              "edges": [
                {
                  "node": {
                    "id": "3727f166-ceb4-4693-9376-69e8be34c9d1"
                  }
                }
              ]
            }
          }
        },
        {
          "node": {
            "id": "ea23adb4-8e29-40c2-bf33-75e6064266c5",
            "members": {
              "edges": [
                {
                  "node": {
                    "id": "3727f166-ceb4-4693-9376-69e8be34c9d1"
                  }
                }
              ]
            }
          }
        },
        // ... many more entries 
      ]
    }
  }
}

See that all organizations are connected to the same doctor? well - that is certainly not true from the DB point-of-view. The organization_members table (join-table) certainly has only one relation between one doctor and one organization.

When i logged the queries sent to the database, i got this result:

query: SELECT "OrganizationEntity"."id" AS "OrganizationEntity_id", "OrganizationEntity"."createdAt" AS "OrganizationEntity_createdAt", "OrganizationEntity"."updatedAt" AS "OrganizationEntity_updatedAt", "OrganizationEntity"."version" AS "OrganizationEntity_version", "OrganizationEntity"."name" AS "OrganizationEntity_name", "OrganizationEntity"."description" AS "OrganizationEntity_description", "OrganizationEntity"."type" AS "OrganizationEntity_type", "OrganizationEntity"."address" AS "OrganizationEntity_address", "OrganizationEntity"."phone" AS "OrganizationEntity_phone", "OrganizationEntity"."email" AS "OrganizationEntity_email", "OrganizationEntity"."url" AS "OrganizationEntity_url", "OrganizationEntity"."logo" AS "OrganizationEntity_logo" FROM "organizations" "OrganizationEntity" LIMIT 10
query: SELECT "DoctorEntity"."id" AS "DoctorEntity_id", "DoctorEntity"."createdAt" AS "DoctorEntity_createdAt", "DoctorEntity"."updatedAt" AS "DoctorEntity_updatedAt", "DoctorEntity"."version" AS "DoctorEntity_version", "DoctorEntity"."keycloakId" AS "DoctorEntity_keycloakId", "DoctorEntity"."title" AS "DoctorEntity_title", "DoctorEntity"."firstname" AS "DoctorEntity_firstname", "DoctorEntity"."lastname" AS "DoctorEntity_lastname", "DoctorEntity"."phone" AS "DoctorEntity_phone", "DoctorEntity"."email" AS "DoctorEntity_email", "DoctorEntity"."url" AS "DoctorEntity_url", "DoctorEntity"."picture" AS "DoctorEntity_picture", "DoctorEntity"."lastLoginAt" AS "DoctorEntity_lastLoginAt", "DoctorEntity"."acceptedTOS" AS "DoctorEntity_acceptedTOS", "DoctorEntity"."settings" AS "DoctorEntity_settings" FROM "doctors" "DoctorEntity" LIMIT 10
query: SELECT "DoctorEntity"."id" AS "DoctorEntity_id", "DoctorEntity"."createdAt" AS "DoctorEntity_createdAt", "DoctorEntity"."updatedAt" AS "DoctorEntity_updatedAt", "DoctorEntity"."version" AS "DoctorEntity_version", "DoctorEntity"."keycloakId" AS "DoctorEntity_keycloakId", "DoctorEntity"."title" AS "DoctorEntity_title", "DoctorEntity"."firstname" AS "DoctorEntity_firstname", "DoctorEntity"."lastname" AS "DoctorEntity_lastname", "DoctorEntity"."phone" AS "DoctorEntity_phone", "DoctorEntity"."email" AS "DoctorEntity_email", "DoctorEntity"."url" AS "DoctorEntity_url", "DoctorEntity"."picture" AS "DoctorEntity_picture", "DoctorEntity"."lastLoginAt" AS "DoctorEntity_lastLoginAt", "DoctorEntity"."acceptedTOS" AS "DoctorEntity_acceptedTOS", "DoctorEntity"."settings" AS "DoctorEntity_settings" FROM "doctors" "DoctorEntity" LIMIT 10
query: SELECT "DoctorEntity"."id" AS "DoctorEntity_id", "DoctorEntity"."createdAt" AS "DoctorEntity_createdAt", "DoctorEntity"."updatedAt" AS "DoctorEntity_updatedAt", "DoctorEntity"."version" AS "DoctorEntity_version", "DoctorEntity"."keycloakId" AS "DoctorEntity_keycloakId", "DoctorEntity"."title" AS "DoctorEntity_title", "DoctorEntity"."firstname" AS "DoctorEntity_firstname", "DoctorEntity"."lastname" AS "DoctorEntity_lastname", "DoctorEntity"."phone" AS "DoctorEntity_phone", "DoctorEntity"."email" AS "DoctorEntity_email", "DoctorEntity"."url" AS "DoctorEntity_url", "DoctorEntity"."picture" AS "DoctorEntity_picture", "DoctorEntity"."lastLoginAt" AS "DoctorEntity_lastLoginAt", "DoctorEntity"."acceptedTOS" AS "DoctorEntity_acceptedTOS", "DoctorEntity"."settings" AS "DoctorEntity_settings" FROM "doctors" "DoctorEntity" LIMIT 10
query: SELECT "DoctorEntity"."id" AS "DoctorEntity_id", "DoctorEntity"."createdAt" AS "DoctorEntity_createdAt", "DoctorEntity"."updatedAt" AS "DoctorEntity_updatedAt", "DoctorEntity"."version" AS "DoctorEntity_version", "DoctorEntity"."keycloakId" AS "DoctorEntity_keycloakId", "DoctorEntity"."title" AS "DoctorEntity_title", "DoctorEntity"."firstname" AS "DoctorEntity_firstname", "DoctorEntity"."lastname" AS "DoctorEntity_lastname", "DoctorEntity"."phone" AS "DoctorEntity_phone", "DoctorEntity"."email" AS "DoctorEntity_email", "DoctorEntity"."url" AS "DoctorEntity_url", "DoctorEntity"."picture" AS "DoctorEntity_picture", "DoctorEntity"."lastLoginAt" AS "DoctorEntity_lastLoginAt", "DoctorEntity"."acceptedTOS" AS "DoctorEntity_acceptedTOS", "DoctorEntity"."settings" AS "DoctorEntity_settings" FROM "doctors" "DoctorEntity" LIMIT 10
query: SELECT "DoctorEntity"."id" AS "DoctorEntity_id", "DoctorEntity"."createdAt" AS "DoctorEntity_createdAt", "DoctorEntity"."updatedAt" AS "DoctorEntity_updatedAt", "DoctorEntity"."version" AS "DoctorEntity_version", "DoctorEntity"."keycloakId" AS "DoctorEntity_keycloakId", "DoctorEntity"."title" AS "DoctorEntity_title", "DoctorEntity"."firstname" AS "DoctorEntity_firstname", "DoctorEntity"."lastname" AS "DoctorEntity_lastname", "DoctorEntity"."phone" AS "DoctorEntity_phone", "DoctorEntity"."email" AS "DoctorEntity_email", "DoctorEntity"."url" AS "DoctorEntity_url", "DoctorEntity"."picture" AS "DoctorEntity_picture", "DoctorEntity"."lastLoginAt" AS "DoctorEntity_lastLoginAt", "DoctorEntity"."acceptedTOS" AS "DoctorEntity_acceptedTOS", "DoctorEntity"."settings" AS "DoctorEntity_settings" FROM "doctors" "DoctorEntity" LIMIT 10
query: SELECT "DoctorEntity"."id" AS "DoctorEntity_id", "DoctorEntity"."createdAt" AS "DoctorEntity_createdAt", "DoctorEntity"."updatedAt" AS "DoctorEntity_updatedAt", "DoctorEntity"."version" AS "DoctorEntity_version", "DoctorEntity"."keycloakId" AS "DoctorEntity_keycloakId", "DoctorEntity"."title" AS "DoctorEntity_title", "DoctorEntity"."firstname" AS "DoctorEntity_firstname", "DoctorEntity"."lastname" AS "DoctorEntity_lastname", "DoctorEntity"."phone" AS "DoctorEntity_phone", "DoctorEntity"."email" AS "DoctorEntity_email", "DoctorEntity"."url" AS "DoctorEntity_url", "DoctorEntity"."picture" AS "DoctorEntity_picture", "DoctorEntity"."lastLoginAt" AS "DoctorEntity_lastLoginAt", "DoctorEntity"."acceptedTOS" AS "DoctorEntity_acceptedTOS", "DoctorEntity"."settings" AS "DoctorEntity_settings" FROM "doctors" "DoctorEntity" LIMIT 10
query: SELECT "DoctorEntity"."id" AS "DoctorEntity_id", "DoctorEntity"."createdAt" AS "DoctorEntity_createdAt", "DoctorEntity"."updatedAt" AS "DoctorEntity_updatedAt", "DoctorEntity"."version" AS "DoctorEntity_version", "DoctorEntity"."keycloakId" AS "DoctorEntity_keycloakId", "DoctorEntity"."title" AS "DoctorEntity_title", "DoctorEntity"."firstname" AS "DoctorEntity_firstname", "DoctorEntity"."lastname" AS "DoctorEntity_lastname", "DoctorEntity"."phone" AS "DoctorEntity_phone", "DoctorEntity"."email" AS "DoctorEntity_email", "DoctorEntity"."url" AS "DoctorEntity_url", "DoctorEntity"."picture" AS "DoctorEntity_picture", "DoctorEntity"."lastLoginAt" AS "DoctorEntity_lastLoginAt", "DoctorEntity"."acceptedTOS" AS "DoctorEntity_acceptedTOS", "DoctorEntity"."settings" AS "DoctorEntity_settings" FROM "doctors" "DoctorEntity" LIMIT 10
query: SELECT "DoctorEntity"."id" AS "DoctorEntity_id", "DoctorEntity"."createdAt" AS "DoctorEntity_createdAt", "DoctorEntity"."updatedAt" AS "DoctorEntity_updatedAt", "DoctorEntity"."version" AS "DoctorEntity_version", "DoctorEntity"."keycloakId" AS "DoctorEntity_keycloakId", "DoctorEntity"."title" AS "DoctorEntity_title", "DoctorEntity"."firstname" AS "DoctorEntity_firstname", "DoctorEntity"."lastname" AS "DoctorEntity_lastname", "DoctorEntity"."phone" AS "DoctorEntity_phone", "DoctorEntity"."email" AS "DoctorEntity_email", "DoctorEntity"."url" AS "DoctorEntity_url", "DoctorEntity"."picture" AS "DoctorEntity_picture", "DoctorEntity"."lastLoginAt" AS "DoctorEntity_lastLoginAt", "DoctorEntity"."acceptedTOS" AS "DoctorEntity_acceptedTOS", "DoctorEntity"."settings" AS "DoctorEntity_settings" FROM "doctors" "DoctorEntity" LIMIT 10
query: SELECT "DoctorEntity"."id" AS "DoctorEntity_id", "DoctorEntity"."createdAt" AS "DoctorEntity_createdAt", "DoctorEntity"."updatedAt" AS "DoctorEntity_updatedAt", "DoctorEntity"."version" AS "DoctorEntity_version", "DoctorEntity"."keycloakId" AS "DoctorEntity_keycloakId", "DoctorEntity"."title" AS "DoctorEntity_title", "DoctorEntity"."firstname" AS "DoctorEntity_firstname", "DoctorEntity"."lastname" AS "DoctorEntity_lastname", "DoctorEntity"."phone" AS "DoctorEntity_phone", "DoctorEntity"."email" AS "DoctorEntity_email", "DoctorEntity"."url" AS "DoctorEntity_url", "DoctorEntity"."picture" AS "DoctorEntity_picture", "DoctorEntity"."lastLoginAt" AS "DoctorEntity_lastLoginAt", "DoctorEntity"."acceptedTOS" AS "DoctorEntity_acceptedTOS", "DoctorEntity"."settings" AS "DoctorEntity_settings" FROM "doctors" "DoctorEntity" LIMIT 10
query: SELECT "DoctorEntity"."id" AS "DoctorEntity_id", "DoctorEntity"."createdAt" AS "DoctorEntity_createdAt", "DoctorEntity"."updatedAt" AS "DoctorEntity_updatedAt", "DoctorEntity"."version" AS "DoctorEntity_version", "DoctorEntity"."keycloakId" AS "DoctorEntity_keycloakId", "DoctorEntity"."title" AS "DoctorEntity_title", "DoctorEntity"."firstname" AS "DoctorEntity_firstname", "DoctorEntity"."lastname" AS "DoctorEntity_lastname", "DoctorEntity"."phone" AS "DoctorEntity_phone", "DoctorEntity"."email" AS "DoctorEntity_email", "DoctorEntity"."url" AS "DoctorEntity_url", "DoctorEntity"."picture" AS "DoctorEntity_picture", "DoctorEntity"."lastLoginAt" AS "DoctorEntity_lastLoginAt", "DoctorEntity"."acceptedTOS" AS "DoctorEntity_acceptedTOS", "DoctorEntity"."settings" AS "DoctorEntity_settings" FROM "doctors" "DoctorEntity" LIMIT 10

Basically - it first fetches a paginated set of Organizations from the database (this is correct).
However, then it fetches n times from the doctors table - without going through the join-table relationship.

Furthermore, this query does only work "from this side". When querying from the other side (like this), this results in an error:

{
  doctors {
    edges {
      node {
        id        
        organizations {
          edges {
            node {
              id
            }
          }
        }
      }
    }
  }
}

throws this error:

error: error: column OrganizationEntity.organizationsId does not exist

and of course it does not exist, because respective column should be located within the organization_members table.

Am i doing something wrong? I guess, the many-to-many relations are not (yet) supported? Am i correct on this one?

All the best and thank you very much for your time and effort!
Johannes
@doug-martin
Copy link
Owner

Yes, I found this over the weekend and started working on a fix. Typeorm doesnt make querying relationships with filters easy.

I hope to get this fixed by the end of the week,

@johannesschobel
Copy link
Contributor Author

ok, good that you also found respective issue.
yeah, querying over many-to-many relationships is quite shitty :D haha

doug-martin added a commit that referenced this issue Feb 21, 2020
@doug-martin
Add support for dataloader
0230ce3 
* Added dataloader support!
* Fixed issue with loading of many-to-many relations [#22](#22)
@doug-martin doug-martin mentioned this issue Feb 21, 2020
Merged
doug-martin added a commit that referenced this issue Feb 21, 2020
@doug-martin
Add support for dataloader
b2eb2ff 
* Added dataloader support!
* Fixed issue with loading of many-to-many relations [#22](#22)
doug-martin added a commit that referenced this issue Feb 21, 2020
@doug-martin
Add support for dataloader
2da916e 
* Added dataloader support!
* Fixed issue with loading of many-to-many relations [#22](#22)
@doug-martin
Copy link
Owner

This will be part of v0.3.0 which also includes dataloader support, so you wont see 10 separate queries. but 2 one to get the organizations and another for members. See #24

doug-martin added a commit that referenced this issue Feb 21, 2020
@doug-martin
Add support for dataloader
1004331 
* Added dataloader support!
* Fixed issue with loading of many-to-many relations [#22](#22)
@johannesschobel
Copy link
Contributor Author

That is awesome

@johannesschobel
Copy link
Contributor Author

just wanted to give feedback to this issue.
This one is resolved for me! thanks for your awesome work!

@GP4cK GP4cK mentioned this issue Jul 5, 2021
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@doug-martin @johannesschobel