Chain orderBy clause problem

I have a query that filters a table based on search params

  const instructors = await db
    .select()
    .from(DBInstructors)
    .where(
      and(
        department ? eq(DBInstructors.department, department) : undefined,
        eq(DBInstructors.section, section)
      )
    )
    .orderBy(
      sortName === 'asc' ? asc(DBInstructors.name) : desc(DBInstructors.name)
    )
    .orderBy(
      sortRating === 'lowest'
        ? asc(DBInstructors.rating)
        : desc(DBInstructors.rating)
    )
    .offset(currentPage)
    .limit(10);

  const instructors = await db
    .select()
    .from(DBInstructors)
    .where(
      and(
        department ? eq(DBInstructors.department, department) : undefined,
        eq(DBInstructors.section, section)
      )
    )
    .orderBy(
      sortName === 'asc' ? asc(DBInstructors.name) : desc(DBInstructors.name)
    )
    .orderBy(
      sortRating === 'lowest'
        ? asc(DBInstructors.rating)
        : desc(DBInstructors.rating)
    )
    .offset(currentPage)
    .limit(10);

The problem is the query sorts the rows based on rating only and ignores name no matter what. Am I missing something ?

17 Replies

rphlmr ⚡•16mo ago

OrderBy can take many values, separated by a comma. What about:

  const instructors = await db
    .select()
    .from(DBInstructors)
    .where(
      and(
        department ? eq(DBInstructors.department, department) : undefined,
        eq(DBInstructors.section, section)
      )
    )
    .orderBy(
      sortName === 'asc' ? asc(DBInstructors.name) : desc(DBInstructors.name),
      sortRating === 'lowest'
        ? asc(DBInstructors.rating)
        : desc(DBInstructors.rating)
    )
    .offset(currentPage)
    .limit(10);

  const instructors = await db
    .select()
    .from(DBInstructors)
    .where(
      and(
        department ? eq(DBInstructors.department, department) : undefined,
        eq(DBInstructors.section, section)
      )
    )
    .orderBy(
      sortName === 'asc' ? asc(DBInstructors.name) : desc(DBInstructors.name),
      sortRating === 'lowest'
        ? asc(DBInstructors.rating)
        : desc(DBInstructors.rating)
    )
    .offset(currentPage)
    .limit(10);

AhmedOP•16mo ago

My first attempt was this implementation and It's ignoring the rating field 😅 The logged query

select `id`, `name`, `slug`, `email`, `section`, `field`, `department`, `rating`, `created_at`, `updated_at` from `instructors` where `instructors`.`section` = ? order by `instructors`.`name` desc, `instructors`.`rating` asc

select `id`, `name`, `slug`, `email`, `section`, `field`, `department`, `rating`, `created_at`, `updated_at` from `instructors` where `instructors`.`section` = ? order by `instructors`.`name` desc, `instructors`.`rating` asc

rphlmr ⚡•16mo ago

Do you want to order by name and if equal, order by rating?

AhmedOP•16mo ago

I want to sort both if specified by user. if they want asc or desc of both fields name: asc or desc rating: asc or desc Both must have a value

rphlmr ⚡•16mo ago

Order by works like this: first order condition and if equality, use the second order condition Your query will produce: instructors sorted by name asc/desc and if 2 instructors have the same name, order by rating

AhmedOP•16mo ago

Can it be ordered by name first and when it's finished they get's ordered again by their rating ?

rphlmr ⚡•16mo ago

Is it not ordering by rating and then by name if there is equality? In the end, it depends on how users will read the filters. I want instructors by rating (and name if same rating). I want instructors by name (and rating if same name).

AhmedOP•16mo ago

I might keep sorting the name on the database level and move the rating sort on the app level, do you suggest that ?

rphlmr ⚡•16mo ago

Depending on what you use as front end. if your filters live in the URL (search params), do it server side. Users can bookmark/share this pre-filtered URL. If you are doing a REST API, do it server side (don't let consumer implementing such things)

AhmedOP•16mo ago

Everything is on the url 👍 read and pass to the query I'm using remix

rphlmr ⚡•16mo ago

The less you do at the app level (server or front) for this kind of filtering, the best you have in perf oh my god ❤️ As an heavy user of Remix: do it in the loader SQL engines are built to handle this kind of sorting for almost free

AhmedOP•16mo ago

Everything is on the loader. the react component is just a plain jsx with conditional rendering

rphlmr ⚡•16mo ago

But with 10 results limit, you can't go wrong

AhmedOP•16mo ago

I tend to leave everything to the db but this case had me some trouble

rphlmr ⚡•16mo ago

Your query and your use case seems good to me with these 2 order conditions maybe I miss something, feel free to give me a json response example like: name filter asc + rating desc should produce: xxxx I often do that on paper to help me writing my queries with filters

AhmedOP•16mo ago

Maybe I had the idea wrong because you either sort on name or rating. You can't have A-Z and lowest at the same time maybe.

rphlmr ⚡•16mo ago

with ordering, I don't think: you can only have one order but you can combine ordering with filters, like : order 5 stars instructors by name like Amazon 😄

Gaming

Programming

Chain orderBy clause problem