0

I'm querying a database using knex, and I need the results to be ordered in a specific way. Currently, I have the following code which returns all the results I want, albeit in a haphazard order.

knex("FRUITTBL")
  .select("FruitTag", "FruitName", "FruitDescription")
  .whereLike("FruitTag", `${query}%`)
  .orWhereLike("FruitName", `${query}%`)
  .orWhereLike("FruitTag", `%${query}%`)
  .limit(20)
  .offset(`${page}`)
  .orderBy("FruitTag")
  .orderBy("FruitName")
  .orderBy("FruitTag")
  .distinct()

I want it to order each whereLike query alphabetically, and then essentially concatenate the three whereLike queries together.

E.g.

AtoZ: whereLike("FruitTag", `${query}%`)
AtoZ: orWhereLike("FruitName", `${query}%`)
AtoZ: orWhereLike("FruitTag", `%${query}%`)

I tried reordering the orderBy but it seems to still return the results in not the order I want

EDIT 1: Given the following table below:

FruitTag FruitName FruitDescription
firetruck2 ppbineapple wherelike2 satisfies
ppaa123 apple wherelike1 satisfies
bhousepp tyler wherelike3 satisfies
fipptruck ppaineapple wherelike2 satisfies
ahousepp james wherelike3 satisfies
ppab123 ppapple wherelike1 satisfies

When I execute the query with ${query} as "pp", it should return in the following order:

FruitTag FruitName FruitDescription
ppaa123 aapple wherelike1 satisfies
ppab123 ppaapple wherelike1 satisfies
fipptruck ppaineapple wherelike2 satisfies
firetruck2 ppbineapple wherelike2 satisfies
ahousepp james wherelike3 satisfies
bhousepp tyler wherelike3 satisfies

You can notice that it first orders the results by the whereLike condition, then alphabatically based on conditions (e.g. wherelike1 will orderBy FruitTag, wherelike2 will orderBy FruitName, wherelike3 orderBy FruitTag

EDIT 2: Using the current suggested answer (credits to @Barmar):

knex("FRUITTBL")
  .select("FruitTag", "FruitName", "FruitDescription")
  .whereLike("FruitTag", `${query}%`)
  .orWhereLike("FruitName", `${query}%`)
  .orWhereLike("FruitTag", `%${query}%`)
  .limit(20)
  .offset(`${page}`)
  .orderByRaw('FruitTag LIKE ? DESC', [`${query}%`]))
  .orderByRaw('FruitName LIKE ? DESC', [`${query}%`]))
  .orderByRaw('FruitTag LIKE ? DESC', [`%${query}%`]))
  .distinct()

I get the result:

FruitTag FruitName FruitDescription
ppaa123 ppaapple wherelike1 satisfies
ppaa123 abpple wherelike1 satisfies
ppaa123 aapple wherelike1 satisfies
fipptruck ppaineapple wherelike2 satisfies
firetruck2 ppbineapple wherelike2 satisfies
ahousepp jbames wherelike3 satisfies
ahousepp jaames wherelike3 satisfies
bhousepp tyler wherelike3 satisfies

Is there a way to additionally order each orderByRaw by FruitName alphabatically again to get the following result?

FruitTag FruitName FruitDescription
ppaa123 aapple wherelike1 satisfies
ppaa123 abpple wherelike1 satisfies
ppaa123 ppaapple wherelike1 satisfies
fipptruck ppaineapple wherelike2 satisfies
firetruck2 ppbineapple wherelike2 satisfies
ahousepp jaames wherelike3 satisfies
ahousepp jbames wherelike3 satisfies
bhousepp tyler wherelike3 satisfies

EDIT 3: Using the current suggested answer (credits to @Barmar):

knex("FRUITTBL")
  .select("FruitTag", "FruitName", "FruitDescription")
  .whereLike("FruitTag", `${query}%`)
  .orWhereLike("FruitName", `${query}%`)
  .orWhereLike("FruitTag", `%${query}%`)
  .limit(20)
  .offset(`${page}`)
  .orderByRaw('FruitTag LIKE ? DESC', [`${query}%`]))
  .orderBy("FruitName")
  .orderByRaw('FruitName LIKE ? DESC', [`${query}%`]))
  .orderBy("FruitName")
  .orderByRaw('FruitTag LIKE ? DESC', [`%${query}%`]))
  .orderBy("FruitName")
  .distinct()

Expected result

FruitTag FruitName FruitDescription
ppaa123 aapple wherelike1 satisfies
ppaa123 abpple wherelike1 satisfies
ppaa123 ppaapple wherelike1 satisfies
fipptruck ppaineapple wherelike2 satisfies
firetruck2 ppbineapple wherelike2 satisfies
ahousepp jaames wherelike3 satisfies
ahousepp jbames wherelike3 satisfies
bhousepp tyler wherelike3 satisfies

Actual result

FruitTag FruitName FruitDescription
ppaa123 aapple wherelike1 satisfies
ppaa123 abpple wherelike1 satisfies
ppaa123 ppaapple wherelike1 satisfies
ahousepp jaames wherelike3 satisfies
ahousepp jbames wherelike3 satisfies
bhousepp tyler wherelike3 satisfies
fipptruck ppaineapple wherelike2 satisfies
firetruck2 ppbineapple wherelike2 satisfies

Although now the FruitName is indeed additionally sorted, using this code apparently switched the order of orderByRaw(wherelike2) and orderByRaw(wherelike3) which messes up everything again

EDIT 4: Code used:

knex("FRUITTBL")
  .select("FruitTag", "FruitName", "FruitDescription")
  .whereLike("FruitTag", `${query}%`)
  .orWhereLike("FruitName", `${query}%`)
  .orWhereLike("FruitTag", `%${query}%`)
  .limit(20)
  .offset(`${page}`)
  .orderByRaw('FruitTag LIKE ? DESC', [`${query}%`]))
  .orderBy("FruitName")
  .orderByRaw('FruitName LIKE ? DESC', [`${query}%`]))
  .orderByRaw('FruitTag LIKE ? DESC', [`%${query}%`]))
  .orderBy("FruitName")
  .distinct()

Raw SQL:

"select `FruitTag`, `FruitName`, `FruitDescription` from `FRUITTBL` where `FruitTag` like 'pp%' or `FruitName` like 'pp%' or `FruitTag` like '%pp%' order by FruitTag LIKE 'pp%' DESC, `FruitName` asc, FruitName LIKE 'pp%' DESC, FruitTag LIKE '%pp%' DESC, `FruitName` asc limit 20"

Expected Table

FruitTag FruitName FruitDescription
ppaa123 aapple wherelike1 satisfies
ppaa123 abpple wherelike1 satisfies
ppaa123 ppaapple wherelike1 satisfies
fipptruck ppaineapple wherelike2 satisfies
firetruck2 ppbineapple wherelike2 satisfies
ahousepp jaames wherelike3 satisfies
ahousepp jbames wherelike3 satisfies
bhousepp tyler wherelike3 satisfies

Actual Table

FruitTag FruitName FruitDescription
ppaa123 ppaapple wherelike1 satisfies
ppaa123 abpple wherelike1 satisfies
ppaa123 aapple wherelike1 satisfies
bhousepp tyler wherelike3 satisfies
ahousepp jbames wherelike3 satisfies
ahousepp jaames wherelike3 satisfies
firetruck2 ppbineapple wherelike2 satisfies
fipptruck ppaineapple wherelike2 satisfies

EDIT 5: Almost perfect again, it's the same code and problem as EDIT 3, but this time I've added the rawSQL for easy troubleshooting

knex("FRUITTBL")
  .select("FruitTag", "FruitName", "FruitDescription")
  .whereLike("FruitTag", `${query}%`)
  .orWhereLike("FruitName", `${query}%`)
  .orWhereLike("FruitTag", `%${query}%`)
  .limit(20)
  .offset(`${page}`)
  .orderByRaw('FruitTag LIKE ? DESC', [`${query}%`]))
  .orderBy("FruitName")
  .orderByRaw('FruitName LIKE ? DESC', [`${query}%`]))
  .orderBy("FruitName")
  .orderByRaw('FruitTag LIKE ? DESC', [`%${query}%`]))
  .orderBy("FruitName")
  .distinct()

Raw SQL:

"select `FruitTag`, `FruitName`, `FruitDescription` 
from `FRUITTBL` 
where `FruitTag` like 'pp%' or `FruitName` like 'pp%' or `FruitTag` like '%pp%' 
order by FruitTag LIKE 'pp%' DESC, 
        `FruitName` asc, 
        FruitName LIKE 'pp%' DESC, 
        `FruitName` asc, 
        FruitTag LIKE '%pp%' DESC, 
        `FruitName` asc 
limit 20"

Expected result

FruitTag FruitName FruitDescription
ppaa123 aapple wherelike1 satisfies
ppaa123 abpple wherelike1 satisfies
ppaa123 ppaapple wherelike1 satisfies
fipptruck ppaineapple wherelike2 satisfies
firetruck2 ppbineapple wherelike2 satisfies
ahousepp jaames wherelike3 satisfies
ahousepp jbames wherelike3 satisfies
bhousepp tyler wherelike3 satisfies

Actual result

FruitTag FruitName FruitDescription
ppaa123 aapple wherelike1 satisfies
ppaa123 abpple wherelike1 satisfies
ppaa123 ppaapple wherelike1 satisfies
ahousepp jaames wherelike3 satisfies
ahousepp jbames wherelike3 satisfies
bhousepp tyler wherelike3 satisfies
fipptruck ppaineapple wherelike2 satisfies
firetruck2 ppbineapple wherelike2 satisfies

This code apparently switched the orderByRaw(wherelike2) results and orderByRaw(wherelike3) results

Barmar
  • 741,623
  • 53
  • 500
  • 612
wynnawinner
  • 51
  • 6
  • When you have multiple ordering columns, it orders by the first one. The second is used for ordering within groups where the first column is the same, and the next is used when the first 2 are the same, and so on. – Barmar Aug 01 '23 at 04:35
  • Can you show an example of the ordering that you want? – Barmar Aug 01 '23 at 04:36
  • @Barmar I've edited the question with an example at the bottom – wynnawinner Aug 01 '23 at 04:48

1 Answers1

1

Use knex.raw() to order by a comparison expression.

knex("FRUITTBL")
  .select("FruitTag", "FruitName", "FruitDescription")
  .whereLike("FruitTag", `${query}%`)
  .orWhereLike("FruitName", `${query}%`)
  .orWhereLike("FruitTag", `%${query}%`)
  .limit(20)
  .offset(`${page}`)
  .orderByRaw('FruitTag LIKE ? DESC', [`${query}%`])
  .orderByRaw('FruitName LIKE ? DESC', [`${query}%`])
  .orderByRaw('FruitTag LIKE ? DESC', [`%${query}%`])
  .orderBy("FruitName")
  .distinct()

The value of a LIKE condition is 1 when it's true, 0 when false, so ordering by the condition DESC puts the rows where the condition matches first.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • it currently gives 'sql error' when executed, i'm looking through to see what's wrong – wynnawinner Aug 01 '23 at 05:05
  • I don't use knex.js myself, I assumed you could use `knex.raw()` there in `orderBy()`. You might need to use `orderByRaw()`. – Barmar Aug 01 '23 at 05:08
  • I've tried editing the obvious typos, but it still gives 'sql error' knex("FRUITTBL") .select("FruitTag", "FruitName", "FruitDescription") .whereLike("FruitTag", `${query}%`) .orWhereLike("FruitName", `${query}%`) .orWhereLike("FruitTag", `%${query}%`) .limit(20) .offset(`${page}`) .orderBy(knex.raw('FruitTag LIKE ? DESC', [`${query}%`])) .orderBy(knex.raw('FruitName LIKE ? DESC', [`${query}%`])) .orderBy(knex.raw('FruitTag LIKE ? DESC', [`%${query}%`])) .distinct() – wynnawinner Aug 01 '23 at 05:09
  • Can you print out the resulting SQL? See https://stackoverflow.com/questions/50096957/how-can-i-view-the-query-knex-builds – Barmar Aug 01 '23 at 05:17
  • Is it possible to further sort each orderByRaw by FruitName again? I've edited the question with an example of what I mean – wynnawinner Aug 01 '23 at 05:37
  • You should be able to insert `.orderBy('columnName')` between the `orderByRaw()` calls, like in my answer. – Barmar Aug 01 '23 at 05:40
  • Does not work, adding .orderBy() after the .orderByRaw() calls messes things up, I've edited the question with the table it returned – wynnawinner Aug 01 '23 at 05:56
  • Can you show the SQL? – Barmar Aug 01 '23 at 05:57
  • Oh, I see the problem. I thought you wanted to order by the column that was selected, not always by FruitName – Barmar Aug 01 '23 at 05:59
  • I've updated the answer. – Barmar Aug 01 '23 at 05:59
  • I've updated the question with the Code used, Raw SQL.toString(), Expected Table, Actual Table – wynnawinner Aug 01 '23 at 06:26
  • You're missing one of the `orderBy("FruitName")` – Barmar Aug 01 '23 at 06:29
  • Even if I add that it doesn't work, the middle one should not matter right? Since the middle one is already sorting FruitName Descending – wynnawinner Aug 01 '23 at 06:30
  • No, it's sorting by whether or not FruitName matches the pattern. – Barmar Aug 01 '23 at 06:33
  • I've added the FruitNames again and now it's back to the same code as earlier (EDIT 3), this time I've added the raw SQL to help in troubleshooting. – wynnawinner Aug 01 '23 at 06:53
  • I don't know what's wrong now, I'm reading it again and again to check for any typos and it seems correct. Unless you are not allowed to use orderBy and orderByRaw together? – wynnawinner Aug 01 '23 at 06:57
  • Figured it out. The `orderBy("FruitName")` should just be once, at the end. – Barmar Aug 01 '23 at 07:01
  • Yes! Thank you so much for your efforts! I've marked your answer as the solution – wynnawinner Aug 01 '23 at 07:13