Graphql-engine: feat: Ability to use "where" clause inside `insert` mutation to link existing data

[thefliik]

I think this is a feature request.

Given the following schema:

CREATE TABLE "Person" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid()
);

CREATE TABLE "EmailAddress" (
  "personId" uuid NOT NULL REFERENCES "Person" ("id") ON DELETE CASCADE,
  "emailAddress" citext PRIMARY KEY
);

CREATE TABLE "Agreement" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "text" citext NOT NULL
);

CREATE TABLE "PersonAgreement" (
  "personId" uuid NOT NULL REFERENCES "Person" ("id") ON DELETE CASCADE,
  "agreementId" uuid NOT NULL REFERENCES "Agreement" ("id") ON DELETE CASCADE,
  "value" boolean NOT NULL
);

A person can be uniquely found using an email address. I'd like to insert a new PersonAgreement into the database given an agreementId, emailAddress, and PersonAgreement value.

Using sql, the email address could be used to get the related personId which could be combined with the agreementId and value to insert a new PersonAgreement.

At the moment, it does not appear to be possible to execute this query within a transaction using Hasura. The problem is that the only way to associate the Person with the new PersonAgreement is to insert a new Person along with the PersonAgreement. Except in this case I don't wish to create a new Person, I wish to find an existing person or have the mutation fail.

Example of the query I'd like to perform:

mutation AddPersonAgreement(
  $emailAddress: String!,
  $agreementId: uuid!,
  $value: Boolean!
) {
  insert_PersonAgreement(
    objects: {
      value: $value,
      agreementId: $agreementId,
      person: {
        where: { # <-- new API here
          emailAddresses: { emailAddress: { _eq: $emailAddress } }
        }
      }
    }
  ) {
    affected_rows
  }
}

[leoalves]

For your specific use case you need to insert from the EmailAddress table, and use the upsert (on_conflict), to update the relationships.

mutation AddPersonAgreement(
  $emailAddress: String!,
  $agreementId: uuid!,
  $value: Boolean!
) {
  insert_EmailAddress(
    on_conflict: {constraint: EmailAddress_pkey, update_columns: [emailAddress]}
    objects: {
      emailAddress: $emailAddress,
      Person: {
        data: {
          PersonAgreements: {
            data:{
              agreementId: $agreementId,
              value: $value
            }
          }
        }
      }
    }
  ) {
    affected_rows
  }
}

It's a bit more verbose, but it should work.

[thefliik]

@leoalves Thanks, but I don't think that query will work. Your proposal will create a new email address and person if they don't exist. My goal is for the query to fail if the email address / person doesn't already exist.

[leoalves]

@thefliik
Ohh my bad.
Didn't read it correctly. Your suggestion is to insert a value from a Select clause.
Yeah that's very useful.

[thefliik]

Yes, this query might translate to:

INSERT INTO "PersonAgreement" ("value", "agreementId", "personId")
VALUES (
  $1, 
  $2, 
  SELECT "id" FROM "Person"
    JOIN "EmailAddress" ON ("EmailAddress"."personId" = "Person"."id")
    WHERE "EmailAddress"."emailAddress" = $3
);

I'm not sure if this SQL is correct, but hopefully the idea is clear.

[djimmo]

Is there currently any workaround to still be able to insert records in a table without the client getting primary key information? I am facing a similar challenge:

I have 3 tables:

• user (primary key: userId)
• event (primary key: eventId)
• user_event (primary key: eventId, userId)

For an existing user and existing event, I would like to be able to insert a record into the user_event table. On the client side there is no knowledge of these ids as I am trying to not expose primary key information. I do however have the email address which could be used to query for the userId, since email address is a unique field in the user table.

Any ideas would be very much appreciated!

I am fairly new to GraphQL and Hasura, however if I can contribute in any way to make this feature possible please let me know.

[thefliik]

@djimmo none that I know of. At the moment, I've decided to only use Hasura on the client for querying, while performing all mutations on the server using other tools.

[rikinsk]

Related to #3366

This could be resolved if it were possible to add nested objects via the update mutation.

Something roughly along the lines of:

mutation insertUserEvent($email: String, $eventId: Int) {
  update_users (
    where: { email: {_eq: $email}}
    _add: {
      event: {
        eventId: $eventId
      }
    }
  ) {
    affected_rows
  }
}

This is a feature being worked on and should be released soon.

[MatthiasGrandl]

Just found this issue. This is exactly what I am missing from Hasura at the moment. Hope it releases soon.