Join in text from Text table in queries

[Sigbjørn Eide]

Hi

Joining in texts from the Text table is commonly used when reading data from VB On-Prem with SQL. This is required to get the text for many values throughout the VB data model. How can this be done in the GraphQL API?

It seems like this might be missing since the join depends on multiple fields (textType, textNo and languageNo) and is therefore not available as a simple connection inside VB. Hopefully you have thought of a solution?

[Marius Bancila]

I understand that ideally, you'd want something like this:

query read_texts($cid : Int!, $orderNo : Int)
{
  useCompany(no : $cid)
  {
    order(filter :{orderNo :{_eq : $orderNo}})
    {
      items
      {
        orderNo

        deliveryMethod
        deliveryMethod_Text(lang : NORWEGIAN) {
           text
        }

        paymentMethod
        paymentMethod_Text(lang : NORWEGIAN) {
           text
        }
      }
    }
  }
}

This API is a generic one, built dynamically from the data model. Providing joins to the text table for some particular fields in this fashion is not possible, because there are no join relationships defined between these tables (such as Order in the previous example) and the Text table. As a result, we cannot provide a generic way for making this happen.

However, you can read this using two (or multiple requests), using the @export directive (and aliases). Here is an example:

query read_texts($cid : Int!, 
                 $orderNo : Int, 
                 $dm : Long = 0, 
                 $pm : Long  = 0)
{
  useCompany(no : $cid)
  {
    order(filter :{orderNo :{_eq : $orderNo}})
    {
      items
      {
        orderNo
        deliveryMethod @export(as :"dm")
        paymentMethod @export(as : "pm")
      }
    }
    
    
    deliveryMethodName : text(filter: {
      languageNo :{_eq : 47} # Norwegian
      textType : {_eq : 5}   # delivery method
      textNo : {_eq : $dm}
    })
    {
      items
      {
        text
      }
    }    
    
    paymentMethodName : text(filter: {
      languageNo :{_eq : 47} # Norwegian
      textType : {_eq : 7}   # payment method
      textNo : {_eq : $pm}
    })
    {
      items
      {
        text
      }
    }    
  }
}

This has been documented here: https://docs.business.visma.net/docs/howto/texts

[Sigbjørn Eide]

Your first suggestion is ideal, but that would require a lot of connections and is not a very generic solution for you to implement. My suggestion is to make something like this possible:

query read_texts($cid : Int!, $orderNo : Int)
{
  useCompany(no : $cid)
  {
    order(filter :{orderNo :{_eq : $orderNo}})
    {
      items
      {
        orderNo

        deliveryMethod @export(as :"dm")
		joinup_Text(languageNo: 47, textType:5, textNo:$dm){
           text
        }

        paymentMethod @export(as :"pm")
		joinup_Text(languageNo: 47, textType:7, textNo:$pm){
           text
        }
      }
    }
  }
}

I know that the text table have an additional lineNo field in the primary key, but my suggestion is in practice how we do it in SQL. I haven't given it much thought, so you are welcome to arrest me, but is there ever a good reason to have multiple texts for the same value? We would be very happy to get the first matching value for languageNo, textType and textNo and tell the customer to clean up their data if they create multiple texts for the same value. If someone for some reason needs multiple texts for the same value, you could provide the same generic solution as a joindown which returns all texts. I know that this might not match standard GraphQL, but since you are building the GraphQL API for an existing datamodel that is not built to fit GraphQL we have to get a bit creative 🙂 

[omelhus]

Would be cool if you made an @exportArray directive that aggregated the values into a distinct array and we then could to { textNo: { _in: $array} }. Or even made @export able to see if the export parameter is an array it pushes the values instead of setting them. That would solve all of these cases.

[Marius Bancila]

That's a different request, please make a separate topic for it.

[omelhus]

As I saw during the happy hour, this is in the works. Thank you, I'm looking forward to that. 🙂

[Marius Bancila]

Export to array is now available: https://docs.business.visma.net/docs/features/directives#the-export-directive

[omelhus]

🥳

[Sigbjørn Eide]

The Text (txt) table in VB contains texts for a lot of fields troughout the datamodel. The other tables contains IDs to get the correct text from the text table by supplying the correct type and language together with the text ID. The text table contains a few hundred different types, and texts for all kinds of values are stored there. I.e: DeliveryMethods, DeliveryTerms, CustomerPriceGroups, ProductPriceGroups,  ProductTypes, Units, all kinds of order groups etc.. I'm sure some of your colleagues can help you elaborate this if my explanation is not clear enough.

As stated in the post, reading from VB on-prem is done with SQL where the txt table is joined in where necessary.

[Marius Bancila]

So you're reading this data with SQL commands, not with the VBS API. I will add this to the backlog and see what we can do.

[Marius Bancila]

I am not familiar with this scenario. Can you elaborate on it a bit? You're saying you are doing this in VB on-prem with VBS? If so, can you share a snippet? That would help me understand you needs better.

[omelhus]

In the current state of the API I think your only option is to fetch the desired text from the text table alongside your query and stitch it afterwards.