Filter records to only those linking to a specific type

🆘 Support
,
1

Summary

I’m struggling to discover how to build a query in graphql. The goal of the query is to query the products that are referenced by an offer that was updated after some date. The issue is that by modeling the content in a way that works well for editing, it seems to have complicating the filtering available to us.

I’m syncing 3 types of data in fibery to another system that I’ll psuedo-model below.

Schema

Type 1: Category (plan to sync all of these)

  • name: string
  • parent_id: string

Type 2: Product (only want to sync the products linked from the offers we sync)

  • name: string
  • category: Category (relation to the category type)

Type 3: Offer (only want to sync the last N days of offers)

  • title: string
  • call_out: string
  • offer_type: Category | Product (single link relationship to either of these two types)

Schema Screenshot

image
image1582×260 14.4 KB

Schema Goal

The goal with this offer model is so that we can support writing an offer about a category when there isn’t an associated product, but if there is an associated product we can use its category. We require a category be linked to the offer in one way or the other. This model works well from an editing perspective, but has caused an issue with filtering.

We have lots of offers and typically only care about the more recent ones for most use cases. So, we are syncing the last N days of offers by update date to another system. The issue is that I only want to sync the products that has an offer that links to it.

Issue

This query below gets me close, but it only filters down to offers that link to either a Product or Category record. While I could use this to filter to the product records returned after the fact, it requires a lot more paging and it seems like a gap in usability of these link fields that support different record types. I also wonder if I’m just missing another way of doing what I want.

I feels like I’d really want to go about it from the opposite direction of filtering to product records that are linked to from offer records updated after some date that are linked to from the offer.offerType field. I couldn’t get close on this type of query.

Note: I cannot do this as a multi-step process due to the integration framework I’m required to leverage. So, I need to execute two queries independent of each other and return it in a single session.

Query Example

query MyQuery{
  allOffers(filter: {offerType: {exists: "true"}, _updatedAt: {gt: "2023-01-01"}}) {
    id
    offerTitle
    callOutText
    offerType {
      ... on ProductRecord {
        id
        name
        categories {
          id
          name
        }
        _modelApiKey
      }
      ... on CategoryRecord {
        id
        name
        _modelApiKey
      }
    }
  }
}

Example Data Returned

Here is a sample of some records returned. You’ll see offers linked to a product and some linked to a category

      {
        "id": "151881809",
        "offerTitle": "LEGO DOTS Stitch-on Patch",
        "callOutText": "$3",
        "offerType": {
          "id": "151882274",
          "name": "LEGO DOTS Stitch-on Patch",
          "categories": [
            {
              "id": "146444288",
              "name": "Toys & Hobbies"
            }
          ],
          "_modelApiKey": "product"
        }
      },
      {
        "id": "151881838",
        "offerTitle": "Lee Men's Extreme Motion Crossroad Cargo Shorts",
        "callOutText": "$18",
        "offerType": {
          "id": "146444353",
          "name": "Shorts",
          "_modelApiKey": "category"
        }
      },
      {
        "id": "151881731",
        "offerTitle": "Nike Air Max Men's 97 OG Shoes",
        "callOutText": "$74",
        "offerType": {
          "id": "146444352",
          "name": "Shoes",
          "_modelApiKey": "category"
        }
      },
2

OK, I hope I got this right… please correct me if I misunderstood. In my example, I’m going to use a simple schema that’s hopefully close to yours. You can clone my example project to take a look.

Categories

  • Animals
  • Clothing

Products

  • Cat
  • Dog
  • Jeans
  • T-shirt

Offers

  • All animals and t-shirts on sale

Desired Output:

An offer containing [Cat, Dog, T-Shirt] (but not Jeans), with their respective categories? If so, I think this will get you what you want…

First, edit your Categories schema to enable “inverse relationships”. This will let you look up all products that link to a category.

CleanShot 2023-11-29 at 16.33.36@2x
CleanShot 2023-11-29 at 16.33.36@2x1422×1660 272 KB

Then, use a query like this:

fragment ProductFragment on ProductRecord {
  _modelApiKey
  id
  name
  category {
    id
    name
  }
}

query MyQuery {
  allOffers {
    _modelApiKey
    id
    title
    offerType {
      ... on CategoryRecord {
        _modelApiKey
        id
        name
        _allReferencingProducts {
          ...ProductFragment
        }
      }
      ... on ProductRecord {
        ...ProductFragment
      }
    }
  }
}

Which should get you a response like this:

{
  "data": {
    "allOffers": [
      {
        "_modelApiKey": "offer",
        "id": "POJKwHkVQrSMuV0cNh-9VQ",
        "title": "All Animals & T-Shirts on Sale!",
        "offerType": [
          {
            "_modelApiKey": "category",
            "id": "ByeZuk7TTmKGaw87ELPIcg",
            "name": "Animals",
            "_allReferencingProducts": [
              {
                "_modelApiKey": "product",
                "id": "e3aptZYhQEOw-psDeRqr9Q",
                "name": "Dog",
                "category": {
                  "id": "ByeZuk7TTmKGaw87ELPIcg",
                  "name": "Animals"
                }
              },
              {
                "_modelApiKey": "product",
                "id": "U7aZFeJRT6So7MCL2YSKIg",
                "name": "Cat",
                "category": {
                  "id": "ByeZuk7TTmKGaw87ELPIcg",
                  "name": "Animals"
                }
              }
            ]
          },
          {
            "_modelApiKey": "product",
            "id": "EK69aLmdTHCDdTJJsQAwVA",
            "name": "T-Shirts",
            "category": {
              "id": "CrQjpwYHREalzR1JjJHXBw",
              "name": "Clothing"
            }
          }
        ]
      }
    ]
  }
}

You get:

  • A tree of offers (you can filter them by date in your real project)
  • Containing both categories and products
    • Each category also shows you _allReferencingProducts (the inverse relationship)
    • Each product shows you its category, the relationship of a standard link field
    • Nothing that isn’t related to a specific offer

From there, in your pipeline, you just need to:

  • For each offer, map through its offerType[]s and switch on _modelApiKey:
    • If category, return allReferencingProducts[] instead
    • If product, return itself

Does that work?

3

Thanks for taking a look. You got the model pretty close, but the one difference is that the field is a single link, rather than a multiple link field. I updated your model with a new field (Offer Type Single) and added some offers that represent how we modeled the content.

We want a single category relation, or a single product relation (which provides the category indirectly). You can clone my Offer Product Model Testing Project

Updated Query Example

I think my provided query might have muddied the water a bit. Here is a better example in the project of yours I cloned, then modified.

The query returns all products, then any offers modified after the _updatedAt date that are linked to through the field I added. However, I do not care about the products where the _allReferencingOffers value returned is empty (meaning this product has no recent offers). I can perform this filtering after the fact, but there are just so many product objects that it would require a lot of paging to get through.

query MyQuery {
  allProducts(filter: {}) {
    _allReferencingOffers(
      through: {fields: {anyIn: offer_offerTypeSingle}}
      filter: {_updatedAt: {gt: "2023-11-01"}}
    ) {
      title
      callOut
      id
    }
    name
  }
}

{
  "data": {
    "allProducts": [
      {
        "_allReferencingOffers": [
          {
            "title": "Refurb Vizio 75\" 4k TV at Best Buy",
            "callOut": "$499 (was $599)",
            "id": "WhwUYptkRr6fbUnO8AIUWA"
          }
        ],
        "name": "Vizio 75\" TV"
      },
      {
        "_allReferencingOffers": [],
        "name": "iPad"
      },
      {
        "_allReferencingOffers": [
          {
            "title": "2023 Macbook Pro",
            "callOut": "$1299 (was $1499)",
            "id": "AqMMvghmSl-J8JY4NaPvUA"
          }
        ],
        "name": "Macbook Pro"
      }
    ]
  }
}

In the output above, I do not want the iPad record returned.

4

Ah OK. It’s still similar with a single link field. You would query allOffers with a filter and then fetch its relationships:

query MyQuery {
  allOffers(filter: {_updatedAt: {gt: "2023-11-01"}}) {
    _modelApiKey
    id
    title
    callOut
    offerTypeSingle {
      ...ProductQuery
      ... on CategoryRecord {
        ...CategoryQuery
        _allReferencingProducts {
          ...ProductQuery
        }
      }
    }
  }
}

fragment ProductQuery on ProductRecord {
  _modelApiKey
  id
  name
  category {
    ...CategoryQuery
  }
}

fragment CategoryQuery on CategoryRecord {
  _modelApiKey
  id
  name
}

That returns:

{
  "data": {
    "allOffers": [
      {
        "_modelApiKey": "offer",
        "id": "WhwUYptkRr6fbUnO8AIUWA",
        "title": "Refurb Vizio 75\" 4k TV at Best Buy",
        "callOut": "$499 (was $599)",
        "offerTypeSingle": {
          "_modelApiKey": "product",
          "id": "STlMry1TRI-PwzGK2HsZUw",
          "name": "Vizio 75\" TV",
          "category": {
            "_modelApiKey": "category",
            "id": "aitM-7XIS-aLO2vNuWK9Cw",
            "name": "TV"
          }
        }
      },
      {
        "_modelApiKey": "offer",
        "id": "AqMMvghmSl-J8JY4NaPvUA",
        "title": "2023 Macbook Pro",
        "callOut": "$1299 (was $1499)",
        "offerTypeSingle": {
          "_modelApiKey": "product",
          "id": "EK69aLmdTHCDdTJJsQAwVA",
          "name": "Macbook Pro",
          "category": {
            "_modelApiKey": "category",
            "id": "ByeZuk7TTmKGaw87ELPIcg",
            "name": "Computer"
          }
        }
      },
      {
        "_modelApiKey": "offer",
        "id": "POJKwHkVQrSMuV0cNh-9VQ",
        "title": "Target Clothing Clearance Sale",
        "callOut": "Up to 50% Off",
        "offerTypeSingle": {
          "_allReferencingProducts": [],
          "_modelApiKey": "category",
          "id": "CrQjpwYHREalzR1JjJHXBw",
          "name": "Clothing"
        }
      }
    ]
  }
}

The fundamental limitation here is that GraphQL won’t let us filter allProducts on allReferencingOffers[].length > 0, so we have to filter on allOffers directly, and then fetch the related products, categories, and products related to categories.

Yes, you do end up with a slightly awkward tree structure that you have to iterate and map through, but at the end of every leaf you should get:

  • All products filtered by active offers
  • All categories filtered by active offers, AND each product within that category
  • No iPad (those things are so rarely on sale, sigh)

Does that help?