Max 1000 rows from joindown tables!

[Tom Stude]

I'm trying to get 100 orders with joindown to order_line, product_transaction and customer transaction. 
...
order ... { items{.... joindown_CustomerTransaction_via_Order{.... joindown_ProductTransaction_via_Order{ ... joindown_OrderLine_via_Order ... 

I get 100 orders in return with a total of 60 rows from the customer transaction node, that i think is correct, but I only get 1000 rows each from the orderLine and the productTransaction nodes! Thats orderLines for only 22 of the 100 orders i asked for. I guess you have a top 1000 total for all joindowns? Is that correct? If so, could you please remove that? Its impossible for me to know if I got all orderlines for the orders or not. Or is it something I do wrong in my query?

[Marius Bancila]

To get this amount of data you need to use unoptimized queries. Put it on the order field and all inner queries will be executed unoptimized.

    order(
      first: 100
      filter: {changedDate:{_gte: 20210101}},
      unoptimized :true
    )

This allows you to ask for totalCount and pageInfo fields, which otherwise do not work with optimized joins.

query($cid :Int!) {
useCompany(no: $cid)
{ 
    order(
      first: 100
      filter: {changedDate:{_gte: 20210101}},
      unoptimized :true
    )
    {
      totalCount
      items {
        orderNo
        orderDate
        changedDateTime
        createdDateTime
        
        joindown_CustomerTransaction_via_Order
        {
          totalCount
          pageInfo {
            hasNextPage
            startCursor
            endCursor
          }
          items {
            orderNo
            invoiceNo
            customerNo
            voucherNo
            valueDateAsDate
            amountDomestic
            debitAmountDomestic
            creditAmountDomestic
            changedDateTime
            createdDateTime
          }
        }

        joindown_ProductTransaction_via_Order{
          totalCount
          pageInfo {
            hasNextPage
            startCursor
            endCursor
          }          
          items {
            orderNo
            transactionNo
            invoiceNo
            productNo
            quantity
          }
        }

        joindown_OrderLine_via_Order
        {
          totalCount
          pageInfo {
            hasNextPage
            startCursor
            endCursor
          }          
          items {
            orderNo
            lineNo
            productNo
          }
        }
      }
    }
  }
}

We have the 1000 rows limit, but it's for each back-end query. If you run unoptimized, then this query will do:

• request for the 100 orders (within the 1000 limit)
• for each order:
  • one request for customer transactions
  • one request for product transactions
  • one request for order lines

So this should allow you to get up to 1000 customer transactions, 1000 product transactions, and 1000 order lines for each order.

When you use pageInfo, you can know whether you got all the data from a joined table, such as the order lines. Here I am limiting to 3:

        joindown_OrderLine_via_Order(first: 3)
        {
          totalCount
          pageInfo {
            hasNextPage
            startCursor
            endCursor
          }          
          items {
            orderNo
            lineNo
            productNo
          }
        }

And I get back something like this:

            "joindown_OrderLine_via_Order": {
              "totalCount": 6,
              "pageInfo": {
                "hasNextPage": true,
                "startCursor": "MA==",
                "endCursor": "Mw=="
              },
              "items": [
                {
                  "orderNo": 1,
                  "lineNo": 1,
                  "productNo": "312"
                },
                {
                  "orderNo": 1,
                  "lineNo": 2,
                  "productNo": "313"
                },
                {
                  "orderNo": 1,
                  "lineNo": 3,
                  "productNo": ""
                }
              ]
            }

 You can use the endCursor to query the orderLines table directly, for the rest of the lines. But you need to use the right filter too (what order it belongs to):

  orderLine(filter: {orderNo :{_eq :1}}, after : "Mw==")
  {
    totalCount
          pageInfo {
            hasNextPage
            startCursor
            endCursor
          }          
          items {
            orderNo
            lineNo
            productNo
          }
  }

 And then you get the rest:

      "orderLine": {
        "totalCount": 6,
        "pageInfo": {
          "hasNextPage": false,
          "startCursor": "Mw==",
          "endCursor": "Ng=="
        },
        "items": [
          {
            "orderNo": 1,
            "lineNo": 4,
            "productNo": ""
          },
          {
            "orderNo": 1,
            "lineNo": 5,
            "productNo": ""
          },
          {
            "orderNo": 1,
            "lineNo": 6,
            "productNo": "311"
          }
        ]
      },

[Tom Stude]

yes, but I need to be able to get data from around 1000-2000 orders. So I need to be able to use your first query example: 

query($cid :Int!) {
useCompany(no: $cid)
{ 
    order(
      first: 100
      filter: {changedDate:{_gte: 20210101}},
      unoptimized :true
    )
    {
      totalCount
      items {
      ...

with pageinfo on order so that I can loop through this 2000 orders with ex 100 orders at the time: 

query($cid :Int!) {
useCompany(no: $cid)
{ 
    order(
      first: 100
      filter: {changedDate:{_gte: 20210101}},
      unoptimized :true
    )
    {
	"pageInfo": 
		{
	      "hasNextPage": false,
      	"startCursor": "Mw==",
	      "endCursor": "Ng=="
		}
	totalCount
      items {
      ...

The query and import script will be to time consuming if I must loop through the joinDown tables for each order table loop...

Another solution would be to query each tables in separate queries, but I will still need to join them with the order table to track lastchanged date.

[Tom Stude]

I did manage to use (unoptimized: true)  to get more than 1000 orderlines in one _joindown from order (with no pagination), but is it correct that It can't be used together with a cursor / after. If so, unoptimized can't be used if you want to download 1000 orders (500 at a time) that all have 5 orderlines each. Then I still got around(500 orders * 5 orderlines = 2500 orderlines. 2500 - 1000 (max) = 1500 orderlines. 1500 / 5 =  300 orders) with no orderlines for each batch?

[Marius Bancila]

As answered in the other similar question you asked on the forum, we have a limit of 1000 records for a table. Mind that a query can contain requests to multiple tables. This limit is per table, not per query.

Moreover, although it's not entirely clear to know what you are saying in the second part, it seems you are describing the problem with optimize queries that is detailed here: https://docs.business.visma.net/docs/features/unoptimized.

Mind that his problem only occurs when you asked for joined data and pagination is in effect. Since you query for more than 1000 records and this limit is enforced, pagination does occur. The immediate workaround for you is to execute the query with the unoptimized flag. 

Although we can consider increasing the page limit, a limit will exist and you may still get into the same issue even if we increase it to 5000 or 10000.

[Tom Stude]

So how would your solution be if the task is to get 500 or 1000 orders with all orderline, product transactions and customer transactions filtered by order last modified  = last two days? And each order could contain from 1 to 200 orderlines each? I could take 10-20 orders at the time (pagination) to be sure not to exeed 1000 orderlines for each loop, but that would take a very long time... and I would not know if I got all orderlines because i don't know if the orders I'm asking for contain more than 1000 orderlines all togheter... 

Another solution for me would be to first download the order headers when the orderlines, products transaction and customer transactions in separate queries, but then I still had to use joinUp with order for each of the other queries to be sure to update all orderlines for all orders that had order last changed the last two days... 

Ex. This query will not return all orderlines query data3 {

  useCompany(no: XXXX) {

    order(

      first: 100

      filter: {changedDate:{_gte: 20210101}}

    )

    {

      totalCount

      items {

        orderNo

        orderDate

        changedDateTime

        createdDateTime

        joindown_CustomerTransaction_via_Order

                {

          items {

            orderNo

            invoiceNo

            customerNo

            voucherNo

            valueDateAsDate

            amountDomestic

            debitAmountDomestic

            creditAmountDomestic

            changedDateTime

            createdDateTime

          }

        }

        joindown_ProductTransaction_via_Order{

          items {

            orderNo

            invoiceNo

            productNo

            quantity

          }

        }

        joindown_OrderLine_via_Order

                {

          items {

            orderNo

            lineNo

            productNo

          }

        }

      }

    }

  }

}

I tried to use (unoptimized: true) but I still don’t get all _joindown lines.

Im creating a dwh containing all orders from 2019 and forward. And I need to be able to update at least 1000 orders each  day.