[Closed] Querying not-associated records for Edit page

[coeit]

Summary of current implementation

To query not-associated records alongside associated records, which we need for both the "not-associated" filter (not implemented yet) and the edit-association-table we need to resolve the query from the end of the target-model of a given association.

Example

Consider two models continent and country which are associated by a one-to-many (one continent has many countries) relation.

// COUNTRY
{
  "model" : "country",
  "associations": {
    "myContinent": {
      "type" : "many_to_one",
      "implementation": "foreignkey",
      "target": "continent",
      "targetKey": "continent_id",
      "keyIn": "country",
    }
  }
}

// CONTINENT
{
  "model" : "continent",
  "associations": {
    "myCountries": {
      "type" : "one_to_many",
      "implementation": "foreignkey",
      "target" : "country",
      "targetKey" : "continent_id",
      "keyIn": "country",
    }
  }
}

Consider editing a specific country with id "country_1". To receive the data needed to display the table for editing its continent associations we do the following query (simplified).

{
  continents(...) {
    ...
    myCountries(search: {field: id, value: "country_1", op: eq}, pagination: {first: 1}) {
      id
    }
  }
}

The idea is, since we need to display all continents and at the same time retrieve the information if the continent is associated to "country_1" or not, to resolve the query from the target model (continent) through its reverse association to the source model (country) and search for a associated country with id "country_1". The resulting data looks something like the following (simplified)

{
  "data": {
    "continents": [
      {
        "continent_id": "continent_1",
        "myCountriesFilter": [
          {
            "country_id": "country_1"
          }
        ]
      },
      {
        "continent_id": "continent_2",
        "myCountriesFilter": []
      },
      {
        "continent_id": "continent_3",
        "myCountriesFilter": []
      }
    ]
  }
}

We can deduce from the resulting JSON which continents are associated to "country_1" and which ones are not, by looking at the resulting country array from the myCountriesFilter fieldResolver (we actually use Connection). This is then displayed accordingly in the continent table.

Problem

Using the above approach means we need to know the following information about the reverse association (continent -> country) to build the respective queries for the SPA:

• type
• name

However to get this information it is necessary to find the reverse association in the target data model. As of now we used the model name and the targetkey to find the correct model. Be aware that only the target is not sufficient, since there might be multiple associations to the same model.

generic associations

In case of generic associations we might not have a targetkey defined, since the implementation of the association is up to the user. That means there is no way distinct way to distinguish the reverse association on the target model.

Solutions

Add the reverse association name to model definition

Additionally to the proposed changes of having the full association type (e.g. one_to_many) defined we would need the name of the reverse association. This would mean we have all the information we need (type, name) from the reverse association directly in the source model.
For example:

{
  "model" : "country",
  "associations": {
    "myContinent": {
      "type" : "many_to_one",
      "reverseAssociation": "myCountries",
      "implementation": "foreignkey",
      "target": "continent",
      "targetKey": "continent_id",
      "keyIn": "country",
    }
  }
}

Multiple requests

Instead of directly querying the needed information in one query (as above) we could do two separate queries. FIrst for the association target directly to get the paginated rows of the table and secondly filter the associated records of the source record (e.g. "country_1") using the returned record ids as filter.
For example (for better illustration using a many_to_many relation):

# Query 1: query all rivers using the given pagination size
{
  rivers(pagination: {limit: 10}) {
    river_id
  }
}

# Query 2: query all records associated to the record we are currently editing (country_1)
# and filter for those that are displayed (This way we avoid needing to recursively filter the db)
{
  readOneCountry(country_id: "country_1") {
    riversFilter(
      pagination:{limit:10}
      search:{field:river_id valueType:Array value:"river_1,river_2,river_3" operator:in}) {
        river_id
    }
  }
}

using recursion

Instead of filtering the found records the alternative would be to recursively find all associated that are displayed currently in the table.

Don't support multiple associations to the same target

For the sake of completeness we could also not support the case of having multiple associations to the same target unless you specifically define those associations using a foreign key.

[coeit]

We will go with solution 1: Add the reverse association name to model definition