Hasura: How to filter and THEN sort, with one-many relationship

Question

I'm new to databases, but I feel what I am trying to do should be pretty commonplace...

What I am trying to achieve is to allow a user to apply both a price range filter and a price sort to the results that my site fetches for them. So I want to find all prices within the specified price range, and then sort them by price.

I have a Hasura DB running on Heroku. The DB has two tables, seeds and prices. One row in seeds table, i.e. one seed, can be related to multiple rows in the prices tables, i.e. many prices. They are joined by a foreign key constraint and a one-many relationship, or Object to Array.

I am attempting to query seeds using the following GraphQL query:

{
  seeds(
    where: {prices: {price: {_gte: "10", _lte: "200"}}}, 
    order_by: {prices_aggregate: {min: {price: asc_nulls_last}}}
  ) {
    product_name
    prices {
      price
    }
  }
}

What I want this query to do is to filter out all prices.price that are not within the valid range (10, 200), and then sort them in order of prices.price ascending. However what happens is that the results are sorted by prices.price ascending INCLUDING values that are not within the range, then the results are filtered.

I will give an example to clarify. Consider the following results from the above query:

{
  "data": {
    "seeds": [
      {
        "product_name": "Northern Lights Auto Feminised Seeds",
        "prices": [
          {
            "price": 3.48
          },
          {
            "price": 6.79
          },
          {
            "price": 9.58
          },
          {
            "price": 104.5
          }
        ]
      },
      {
        "product_name": "The White OG Feminised Seeds",
        "prices": [
          {
            "price": 3.48
          },
          {
            "price": 6.79
          },
          {
            "price": 15.68
          }
        ]
      },
      {
        "product_name": "Special Kush #1 Feminised Seeds from Royal Queen Seeds",
        "prices": [
          {
            "price": 3.49
          },
          {
            "price": 13.53
          },
          {
            "price": 8.29
          }
        ]
      }
    ]
  }
}

The above results are correct, because there are valid values for prices.price within the range specified (104.5, 15.68, 13.53) respectively, however the results are not in the right order. They are instead ordered by the lowest price.prices, regardless of the filter which was specified (10, 200).

The correct order for the results would be:

{
  "data": {
    "seeds": [
      {
        "product_name": "Special Kush #1 Feminised Seeds from Royal Queen Seeds",
        "prices": [
          {
            "price": 3.49
          },
          {
            "price": 13.53
          },
          {
            "price": 8.29
          }
        ]
      },
      {
        "product_name": "The White OG Feminised Seeds",
        "prices": [
          {
            "price": 3.48
          },
          {
            "price": 6.79
          },
          {
            "price": 15.68
          }
        ]
      },
      {
        "product_name": "Northern Lights Auto Feminised Seeds",
        "prices": [
          {
            "price": 3.48
          },
          {
            "price": 6.79
          },
          {
            "price": 9.58
          },
          {
            "price": 104.5
          }
        ]
      }
    ]
  }
}

Can anyone help me, and explain how I can achieve these correct results? It is worth mentioning that it is not possible to sort the results after the query as there are thousands and the sort will definitely affect which results are returned from the DB.

Thanks, in advance!

Answer 1

The where clause you are applying to seeds will fetch all seeds that have atleast one price within the range you have specified. This will NOT filter the price data that you are fetching in the nested query.

For filtering the price you need to apply the where clause inside prices array relationship.

{
  seeds(
    where: {prices: {price: {_gte: "10", _lte: "200"}}}, -> this filters seeds
    order_by: {prices_aggregate: {min: {price: asc_nulls_last}}}
  ) {
    product_name
    prices(where:{price:{_gte: "10", _lte: "200"}}) { -> this filters price
      price
    }
  }
}