Intersect search arguments for fieldResolvers when searching on the idAttribute

[coeit]

Issue (taken from Slack)

I found a small bug in the backend when resolving a fieldResolver for a many_to_many relation via foreignkeys with a search argument. Example:

{
  riversConnection(pagination: {first: 2}) {
    rivers {
      river_id
      length
      countriesConnection(pagination: {first: 10} search: {field: country_id value:"country_1" operator:eq}) {
        countries {
          country_id
        }
      }
    }
  }
}

results in the following search operator for the countriesConnection

{
  "operator": "and",
  "search": [
    {
      "field": "country_id",
      "value": "country_1,country_5,country_6",
      "valueType": "Array",
      "operator": "in"
    },
    {
      "field": "country_id",
      "value": "country_1",
      "operator": "eq"
    }
  ]
}

Note how we have two searches on the country_id field combined with an and. One for the search-argument itself and one to filter only the ones associated to the "current" river. While this works fine for postgres, e.g using the following statement

SELECT * FROM "countries" WHERE ("country_id" IN ('country_1', 'country_5', 'country_6') AND "country_id" = 'country_1') ...;

the equivalent might not work for other storagetypes. In cassandra at least it is not allowed to have multiple restrictions on the same field if it includes an "Equal", I'm not sure about other storagetypes.

Solution

Traverse the search tree of the fieldResolver and look for nodes with operator === 'eq' and field === models.<assocModel>.idAttribute(). If we find a node we need to do an intersection with the asociation argument foreign-key-array and the found value.

Pending thoughts

What about a query with a search like:

search: {operator:and, search:[{field:city_id value:"city_1" operator:eq},{field:city_id value:"city_2" operator:eq}]}

[asishallab]

Responding to the example in section "Pending thoughts"

search: {operator:and, search:[{field:city_id value:"city_1" operator:eq},{field:city_id value:"city_2" operator:eq}]}

Would be converted to

let foreign_keys = helper.intersection_of_associated_foreign_keys_and_search_args_short_param_name(  [/* ... */] );
search: {
  operator:and, 
  search:[
    {
      field:city_id 
      value: foreign_keys  
      operator:in
    },{
      field:city_id 
      value: foreign_keys 
      operator:in
    }]
}

[asishallab]

Implementation

Only implement this issue for Cassandra.

Cassandra

Traverse the search tree and do the intersections. If any intersection is empty return an empty associated record-array.

Make a note in our Zendro developer manual. If Cassandra / Skylla implements OR at any time in the future this solutions needs to be fixed in a different approach.