Understanding dynamic aggregate order_by, and alternatives

[jackherizsmith]

Hello!

I am struggling to order my results the way I'd like to. I have products with reviews which have attributes, and I have users, who share some of these attributes. For a given product, I want to order the reviews by how many attributes each has in common with the user viewing the page (i.e. "most relevant" reviews).

I'm currently close - there's an existing computed field relevant_attributes which only returns attributes that are relevant to the product (unlike greasy hair and hand soap). I can sort by that:

query getProduct($url: String!) {
  product_reviews(
    where: {product: {url: {_eq: $url}}
    order_by: {relevant_attributes_aggregate: {count: desc}}
  ) {
    ...review
  }
}

however what I need is a dynamic computed field that takes the user ID, such that I can work out on the fly which attributes are shared, something like

query getProduct($url: String!, $userId: Int!) {
  product_reviews(
    where: {product: {url: {_eq: $url}}
    order_by: {shared_attributes_aggregate({args: {userId: $userId}}): {count: desc}}
  ) {
    ...review
  }
}

Which I appreciate is entirely not a thing, but hopefully it illustrates well enough what I'm aiming for.

I think this must be a common use case, however I really can't work out how to manage it - as I understand it, no version of this approach is currently possible. I'm wondering if anyone has ideas on how to make it work, either straightforwardly, or advice on using further views and computed fields to get the sort functionality I need. I would also love to keep the returned reviews typing from the database that allows me to continue traversing the graph from the query.

[jackherizsmith]

I have addressed this by:

• creating a view, from which to hold the the review record - its ID alongside sort parameters (review rating, shared attributes count, date, helpfulness etc)
• creating a top-level computed field, that returns a set of this view - here is where the complexity lies, e.g. in calculating the number of "helpful" votes, whatever else
• I created an object relation between review_id and reviews.id, such that traversing the graph (e.g. for filtering) is still possible nested by one more level, e.g. before I had (where: {rating: {_gt: 3}}) now I have (where: {review: {rating: {_gt: 3}}})

it feels like a long way round, although I'm actually quite pleased with the separation of concerns whilst maintaining the option to use parameters in my query