We can now filter workspaces as was requested in #6037 but we are still missing sorting the templates and workspaces.
cc: @BrunoQuaresma @bpmct

wait, do we have workspace sorting, or just filtering? two very different things, and I can only find any evidence of filtering being in the app

sorting would also totally be a cool thing to add, just trying to get clarification on what the priority is

@aslilac you are right. I choose the wrong wording. We do not have sorting. But here I am requesting sorting and not filtering.

I have updated the issue to request, Searching, Sorting, and Filtering.

Yeah, we would need some BE work first.

Unfortunately we cannot do dynamic order by in SQLc (ASC, DESC). So to do this, and assuming you want to sort both ways, we need to solve that problem.

Which is a whole can of worms...

I talked to the SQLc authors about this, and they do acknowledge this as a problem, and are discussing solutions. But who knows when that will happen.

so it can sort on different columns, but the sort direction is fixed?

I think that'd be fine for now, if we're limited by sqlc. hopefully it'd be easy to add later once they figure things out on their end.

just spoke with @sreya about this, sounds like interest in pursuing this is currently low.

A customer also requested a search. I suggest we do both :)

I'm strongly in favor of moving to an index or view or something to make sorting more approachable. Custom table sorting and custom direction is quite common in other software and many of ourusers customers are unhappy with the default sort which is # of devs

It's annoying that this isn't easy for us. We'd need to duplicate our queries since SQLc doesn't support dynamic queries. Or we need to inject some dynamic element like we do for RBAC.

Either way, it is frustrating it is not trivial 😢

Does upgrading to the latest sqlc help here?

Does upgrading to the latest sqlc help here?

It does not unfortunately. I am consistently talking with the SQLc guys though about solving some of these issues.

what i want is search the template
if the template count big than 10 , it is a hard to find the template which is me need

Customer wants, search bar, want the templates to be sorted alphabetically instead of used by. Additionally wants a last used metric, for admin insights to maintain the templates

Another customer is asking, "The main table of available templates, please allow sorting other than by number of devs ... I have 43 templates, and they always "jump" around..."

TIL this is a valid way to handle dynamic ordering with a static query:

SELECT id, name, updated_at from templates
 ORDER BY
  CASE WHEN false THEN id END asc,
  CASE WHEN false THEN id END desc,
  CASE WHEN false THEN name END asc,
  CASE WHEN false THEN name END desc,
  CASE WHEN false THEN updated_at END asc,
  CASE WHEN true THEN updated_at END desc
 LIMIT 10 ;

CASE WHEN defaults to null, so you ORDER BY null (which cannot be done explicitly).

OpenAI states: (I cannot find docs to support this)

In SQL, when sorting by NULL, it effectively means that the sorting is non-deterministic because all values are the same (i.e., NULL).

You can do this to order by null:

SELECT id, name, updated_at from templates
ORDER BY
	CASE WHEN true THEN null END asc
LIMIT 10 

In my limited testing, I think this will work 🤔

A proposed production-like query

SELECT id, name, updated_at from templates
 ORDER BY
  CASE WHEN @id_asc::bool THEN id END asc,
  CASE WHEN @id_desc::bool THEN id END desc,
  CASE WHEN @name_asc::bool THEN name END asc,
  CASE WHEN @name_desc:bool THEN name END desc,
  CASE WHEN @updated_at_asc::bool THEN updated_at END asc,
  CASE WHEN @updated_at_desc::bool THEN updated_at END desc
 LIMIT 10 

oh weird. what does CASE WHEN false THEN x END evaluate to?

@aslilac it evals to null