Referencing intermediate results within other parts of the query

[novoj]

Consider the following relationship:

erDiagram
      Product ||--o{ ParameterValue : "has zero or more"
      ParameterValue ||--|| Parameter : "has exactly one"
      Parameter ||--o| ParameterGroup : "has at most one"

Loading

... and the reverse relationship:

erDiagram
      ParameterGroup ||--o{ Parameter : "has zero or more"
      Parameter ||--o{ ParameterValue : "has zero or more"
      ParameterValue ||--o{ Product : "has zero or more"

Loading

Then we could write this query to list all groups, parameters, and their values down to the set of exactly specified products in the following way:

query {
  listParameterGroup(
    filterBy: {
      entityLocaleEquals: cs,
      referenceParametersHaving: {
        entityHaving: {
          referenceParameterValuesHaving: {
            entityHaving: {
              referenceProductsHaving: {
                entityPrimaryKeyInSet: [685, 678, 18588]
              }
            }
          }
        }
      }
    }      
    limit: 1000
  ) {
    attributes {
      code, name
    }
    parameters(
      filterBy: { 
        entityHaving: { 
          referenceParameterValuesHaving: { 
            entityHaving: { 
              referenceProductsHaving: { 
                entityPrimaryKeyInSet: [685, 678, 18588] 
              } 
            } 
          } 
        } 
      } 
    ) {    
      referencedEntity {
        attributes {
          code, name
        }
        parameterValues(
          filterBy: { 
            entityHaving: { 
              referenceProductsHaving: { 
                entityPrimaryKeyInSet: [685, 678, 18588] } 
            } 
          } 
        ) {
          referencedEntity {
            attributes {
              code, name
            }    
            products(
              filterBy: { 
                entityPrimaryKeyInSet: [685, 678, 18588] 
              }
            ) {
              referencedEntity {
                primaryKey
              }
            }
          }
        }
      }
    }
  }
}

This is quite cumbersome, since it's obvious that we need to repeat the filtering conditions from the leading `filterBy' clause. Moreover, the query is suboptimal because the similar filtering occurs repeatedly. The idea of this issue is to introduce a "placeholder" constraint that would allow us to reuse the results of the initial filtering, making the query both more readable and faster because we could just reuse intermediate results along the way:

query {
  listParameterGroup(
    filterBy: {
      entityLocaleEquals: cs,
      referenceParametersHaving: {
        rememberAs(
          "matchingParameters",
          entityHaving: {
            referenceParameterValuesHaving: {
              rememberAs(
                "matchingValues",
                entityHaving: {
                  referenceProductsHaving: {
                    rememberAs(
                      "matchingProducts":
                      entityHaving: {
                        entityPrimaryKeyInSet: [685, 678, 18588]
                      }          
                    )          
                  }
                }
              )              
            }
          }
        }
      }
    }      
    limit: 1000
  ) {
    attributes {
      code, name
    }
    parameters(
      filterBy: { 
        remembered("matchingParameters")
      } 
    ) {    
      referencedEntity {
        attributes {
          code, name
        }
        parameterValues(
          filterBy: { 
            remembered("matchingValues")
          } 
        ) {
          referencedEntity {
            attributes {
              code, name
            }    
            products(
              filterBy: { 
                remembered("matchingProducts")
              }
            ) {
              referencedEntity {
                primaryKey
              }
            }
          }
        }
      }
    }
  }
}

This approach is similar to ANSI SQL [Common Table Expressions] (https://www.atlassian.com/data/sql/using-common-table-expressions) and allows to remember any intermediate result and use it in another place for additional filtering. It also allows to combine it with additional filters and to use AND, OR, NOT extensions.

When reusing the results, we would have to verify that the intermediate results match the expected entity type at a certain level - so that we don't combine resulting product primary keys with parameter primary keys and what not. This would lead to incorrect and chaotic results.

[novoj]

First user impressions are that the concept is hard to grasp, although it's easy to implement from an engine point of view. It was pointed out to me by @lukashornych that there is already a way in GraphQL to reuse common fragments:

query a(
  myFilter: FilterContainerABC = {
    entityPrimaryKeyInSet: [123, 456]
  }
) {
  queryProduct(
    filterBy: {
      and: $myFilter
      referenceGroupsHaving: {
        entityHaving: $myFilter
      }
    }
  )
}

This is only client-side optimization, but as I realized it could be leveraged on the server side if we could automatically reuse results of constraints with the same hash within the same computation (nested computations). This could be implemented quite easily.

The exact same thing could be done programmatically even in Java, although the result query is complex and hard to read. So the idea will stay in the backlog, but we need more experience to design it properly.