Filter on _joindown

[Tom Stude]

I want to query all products and its priceMatrix data for customerPriceGroup1 = 1. I tried to solve it by running this query, but I only get NULL values for all joindown_PriceAndDiscountMatrix_via_Product nodes. What am I doing wrong?
query data2 {
useCompany(no: XXX) {
product{
items {
productNo
joindown_PriceAndDiscountMatrix_via_Product(filter: {customerPriceGroup1:{_eq:1}}) @include(if:true) {
totalCount
items {
customerPriceGroup1
salesPrice1InCurrency
}}}}}}

[Dante Cavallin]

Hi I have question regarding this, is it possible to filter on joindown or joinup relations without selecting those fields in the query ?
Example (pseudocode):

query
{
  useCompany(no: 9999)
  {
    orderDocuments
    (
      filter: {joinup_PicList(filter: {pickListStatus: {eq:8}})}
    )
    {
      totalCount
      items
      {
        orderDocumentNo
        documentType
        group3
      }
    }
  }  
}

What I want in this case is only those fields from OrderDocument where the related PicListStatus is 8, is this possible ?

[Florian Haase]

If you try to achive a WHERE EXISTS () clause in GraphQL, I think it is not a part of a standard GraphQL Server implementation, but there are several different solutions on the web how to do this. I wondered about the same thing before...

But maybe Marius has some more comments...

[Dante Cavallin]

Maybe I have to go the otherway around:

query
{
  useCompany(no: 9999)
  {
    picList
    (
      filter: {pickListStatus: {eq:8}}
    )
    {
      totalCount
      items
      {
        joindown_OrderDocument_via_Order{
	        orderDocumentNo
	        documentType
		group3
	}
      }
    }
  }  
}

That should work I guess ?

[Marius Bancila · Accepted solution]

A fix for this bug is now available in production. Please try your queries again and let us know if things are OK.

[Tom Stude]

It works! Thanks!

[Tom Stude]

Yes. If I run your query for my company i get: 
"joindown_PriceAndDiscountMatrix_via_Product": {"totalCount": null,"items": null}
if I remove (filter: {customerPriceGroup1:{_eq:1}}) @include(if:true) 
I get 3-4 rows for each product (where customerPriceGroup1 = 1 is one of them)

PS: I get the same problem when trying to filter on _joindown_productTransaction_via_Order: 
ex: 
query abs {
useCompany(no: xxx) {
order(
filter:{_and:[{orderNo:{_eq:XXX}}, {orderDate:{_gte:20220101}}]}
sortOrder:{orderDate:ASC}
)
{items
{orderNo
joindown_ProductTransaction_via_Order(filter:{productNo:{_eq:"1"}})
{
items {
productNo
description
quantity
}}}}}}

If I remove (filter:{productNo:{_eq:"1"}}) I get one result.

[Marius Bancila]

This must be a general problem with filters in _joindown fields. So sure, it would show up regardless what tables you query.

PS: I'd like to ask you when you paste snippets of queries to use the Insert/Edit code sample button so that you get preformatted text. That makes your queries more readable.

[Tom Stude]

yes, if I remove (filter: {customerPriceGroup1:{_eq:1}})  I got 3 joindown_PriceAndDiscountMatrix_via_Product lines pr product, where one (of each product) of them customerPriceGroup1 = 1

[Marius Bancila]

I can confirm there is a bug in our side. I have opened a ticket for fixing it.

[Tom Stude]

did you see the part about same problem when using joindown productTransaction from order?
order
{items
{orderNo
joindown_ProductTransaction_via_Order(filter:{productNo:{_eq:"1"}})
{....
as well (in case different bugs)

[Marius Bancila]

I will test this on our side and come back with an answer.

[Marius Bancila]

Perhaps this is a silly question but do you actually have data in this table for this customer price group? The filter you are using looks good to me. Here is how you can query the priceanddiscountmatrix table directly:

query data2($cid :Int!) {
   useCompany(no: $cid) {
     priceAndDiscountMatrix(filter : {customerPriceGroup1 : {_eq : 1}})
     {
        totalCount
      	items
      	{
           productNo
           customerPriceGroup1
           salesPrice1InCurrency
      	}
     }
   }
}

I am formatting your query here so it can be read better by others:

query data2 {
   useCompany(no: XXX) {
      product{
         items {
            productNo
            joindown_PriceAndDiscountMatrix_via_Product(filter: 
               {
                  customerPriceGroup1:{_eq:1}
               }
            ) @include(if:true) {
               totalCount
               items {
                  customerPriceGroup1
                  salesPrice1InCurrency
               }
            }
         }
      }
   }
}