Best place to implement a custom sort function?

Would love to hear some opinions on this:

I have a custom sorting requirement (which i have documented over here) which would have been a piece would have been a piece of cake with good ol’ SQL but seems to be impossible to be done with existing Prisma or GraphQL functionality (of course, should you know a way, don’t hesitate to claim your reputation points over at SO and don’t bother reading any further :upside_down_face:)

Now i can see these different options on where to implement the sorting function:

  1. in the service that implements the query
  2. in a transform directive
  3. in the client-side code in the /web-side

For solution 1. + 2. is see the advantage that the load of sorting a potentially large list not burdened onto the client. Solution 3. has the advantage that the cache could still be updated directly without fetching again after applying any mutations.

What are your thoughts?

1 Like

What makes it easy with straight SQL?

To replicate that, you can use Prisma’s raw database access to execute any sql statement even CTE’s.

You can compose the sql statement or use template replacement as well.

See Raw database access (Reference) | Prisma Docs

What makes it easy with straight SQL?

Maybe just the fact that i remember from the top of my head how i would do that, as explained on SO, with a CASE WHEN expression. Of course, knowledge is power, i have to admit it may not seem that simple to everyone, if that is what you mean. For Prisma ORM and RedwoodJS stack now i’m in the reversed role of newbie and trying to understand my options here.

See Raw database access (Reference) | Prisma Docs

Yes i’ve seen that already but refrained so far from going down that road b/c we would lose type safety (i.e. booleans from MySQL would become 1/0) and break it for MongoDB. While we’re currently actively using SQLite and Postgres, we want to stay agnostic there as this project is going to be released on github when reaching beta and we’d like to leverage prisma’s ORM abstraction there as much as possible to leave the choice to the users.

Would love to hear your thoughts on the three options i listed above (may there’s a fourth one?) or how you would approach this in general.

Hey Philzen,

Welcome! I like your SO writeup. It was well done. And I like your question for opinions on achieving this the redwood way.

I would have, like david mentioned, recommended you look into prisma’s queryRaw as a potential option. You bring up good points against using that. It seems that you prefer option three in your main post, but the mention of a large list keeps you away from that option. Any idea on how large the list could get?

Also, I would like to encourage you, since there does not seem to be a way to do this in prisma, to consider opening a discussion in prisma’s github issues. I have read a few of their exchanges now, and have appreciated their in-depth discussions on their subject matter. The RW team also seems to work closely with prisma; …@thedavid just emceed Prisma Day a couple of weeks ago. There may be some crossover at some point.

By no means am I trying to dissuade you from continuing the discussion here with the RW team. However, it sounds like you have some good use-cases, or thoughts, around this topic. The only discussion I could find close to this is this post: Sorting the items by custom value #8068 and they were also pointed to the queryRaw method.

I just saw that they have over 2000 issues open, but they might appreciate your input for future consideration.

Again, welcome!

barrett

Without knowing the complexity of your query, it’s to give advice beyond raw queries or basic orderBy a variable.

That said I would do all the logic in the service.

Transform directives aren’t intended to do those things - they are for formatting values and such.

My other advice is not to optimize to early.

Your query logic doesn’t have to be “code perfect” at first and even if you have 5-6 queries with id statements to pick the important part is the functionality.

Later, you can optimize or make more elegant if needed.

But it’s often the case where one tries to have perfect code at the expense of shipping.

Hey @dthyresson and @PantheRedEye – just wanted to quickly come back with a HUGE THANKS for your warm words and advice here.

I’ve now implemented this directly in the service method that does the fetchAll-query on the model in question as per your advice @dthyresson – works well and actually gives me the flexibility i was looking for, as i can import the sort-function also in web and use it for cache updates, which i already successfully tested – best of both worlds / on both api- and web-side :fireworks:

My other advice is not to optimize to early.

Yes, Premature optimization is the root of all evil and i definitely have made myself guilty of that not only once in my life. Although there’s always room to argue what does or does not fall under this category. Major common sense seems that doing an obvious optimization (such as avoiding string concatenation in tight loops) and architectural / design decisions don’t fall under that accusation. So i guess i’ll just argue that this was the latter :wink: As i was slowly getting a grasp of the many layers that make up RedwoodJS, i guess i felt easily spoiled for choice, so yeah, at the end of the day, one needs to stop worrying and just implement something to be able to ship.

So thanks again for your guidance in helping me to arrive at a well-informed decision here!

2 Likes

Sort function? That can also be used on web? Tell me more! :grinning:

Not exactly sure what you’re after here, but i’m always happy to share my findings :pray:
I believe “shared code” between API and WEB is not yet officially a thing and being looked into ATM … however in my tests i realized that some imports work, why some make webpack fall over. Unfortunately i didn’t have the time yet to do a thorough investigation into the matter (wouldn’t be able to fix webpack config anyway as that’s all greek to me :see_no_evil:), but it seems it depends on the dependencies / imports of the file itself, which you want to import on the other side.

Fortunately, importing the file containing my sort-function on web works fine, this is the implementation:

API side

// api/src/utils/sort.ts
import type { Interview } from 'types/graphql'
import type { Interview as PrismaInterview } from '@prisma/client'

type InterviewWithDates =
  | Pick<PrismaInterview, 'startAt' | 'createdAt'>
  | Pick<Interview, 'startAt' | 'createdAt'>

export const customInterviewSort = (
  thisInterview: InterviewWithDates,
  thatInterview: InterviewWithDates
): 1 | 0 | -1 => {

  // … a missed opportunity to use typescript function overloading here, however that would
  // likely be twice as complex (so neither faster to read nor execute) due to various type checks & conversion

  // needs date conversion to be safe when this function is used from the web side
  // (where we only have the graphql result type, which has strings for dates).
  const thisSortDate = new Date(
    thisInterview.startAt || thisInterview.createdAt
  )
  const thatSortDate = new Date(
    thatInterview.startAt || thatInterview.createdAt
  )

  if (thisSortDate < thatSortDate) return -1
  if (thisSortDate > thatSortDate) return 1

  return 0
}

This is then simply stitched on to the return our findMany-query as .then((data) => data.sort(customInterviewSort))


Going full circle on my initial problem – with the above i’m finally able to achieve the equivalent of

SELECT * FROM interview
ORDER BY CASE startAt
    WHEN NULL THEN updatedAt
    ELSE  createdAt
    END

or with an if_else-query (sqlite-specific dialect here) even shorter:

SELECT * FROM interview
ORDER BY IIF (startAt != NULL, startAt, updatedAt) ASC

Web Side

I’ll then be using the sort function to update the query cache directly (avoiding another API+DB roundtrip) after successful update mutations like in this example (applying my customInterviewSort directly before cache.writeQuery).