Custom ORDER BY clause #438
Replies: 5 comments 11 replies
-
Cool request. You can achieve it today with an sqlExpression field (although it's not optimal) can you try that and let me know? |
Beta Was this translation helpful? Give feedback.
-
Hi @Darkside73, we have an idea on how we can implement this in a future version, in the meantime, I have a workaround - let me know what you think - here's an example implementation of the approach: this shows a list of tasks, and when you type a value in the top input, it sorts the rows based on the number of occurrences of that string in each task
taskRepo
.find({
limit: 20,
orderBy: { queryCountInTitle: 'desc' },
where: {
queryArgs: {
query,
},
},
}) Let me know if you can use this as in interim solution, until we'll implement this as a feature import { Entity, Fields, dbNamesOf, remult } from 'remult'
@Entity<Task>('tasks', {
allowApiCrud: true,
backendPreprocessFilter: (filter) => {
for (let and of filter?.$and || []) {
const args = and.queryArgs as QueryArgument
if (args?.query !== undefined) {
// remult.context is separate for each request
remult.context.queryArgument = args
delete and.queryArgs
}
}
return filter
},
})
export class Task {
@Fields.cuid()
id = ''
@Fields.string()
title = ''
@Fields.boolean()
completed = false
@Fields.createdAt()
createdAt?: Date
@Fields.integer<Task>({
sqlExpression: async () => {
const t = await dbNamesOf(Task)
if (!remult.context.queryArgument?.query) return `(select 0) `
const escapedSubstring = escapeSqlString(
remult.context.queryArgument.query
)
return `(LENGTH(${t.title}) - LENGTH(REPLACE(${t.title}, '${escapedSubstring}', ''))) / LENGTH('${escapedSubstring}')
`
},
})
queryCountInTitle = 0
@Fields.json({
serverExpression: () => ({}),
valueConverter: {
toJson: (x) => JSON.stringify(x),
fromJson: (y) => JSON.parse(y),
},
})
queryArgs?: QueryArgument
}
type QueryArgument = {
query: string
}
// https://remult.dev/docs/custom-options#extending-remult-s-context-property-for-request-specific-information
declare module 'remult' {
export interface RemultContext {
queryArgument?: QueryArgument
}
}
function escapeSqlString(str: string) {
if (typeof str !== 'string') {
return str
}
return str
.replace(/\\/g, '\\\\') // Escape backslashes
.replace(/'/g, "''") // Escape single quotes
} |
Beta Was this translation helpful? Give feedback.
-
Can you adjust the stack blitz I sent you to see the issue?
Or if you prefer to do an online session, join our discord and I’ll be
available later this week
…On Mon, May 27, 2024 at 11:34 Darkside73 ***@***.***> wrote:
Tried your solution. It gives an error:
error: column "queryArgs" does not exist
The model:
type QueryArgument = {
query: string}
function escapeSqlString (str: string) {
if (typeof str !== 'string') {
return str
}
return str
.replace(/\\/g, '\\\\') // Escape backslashes
.replace(/'/g, '\'\'') // Escape single quotes}
@entity('posts', {
backendPreprocessFilter: (filter) => {
for (const and of filter?.$and || []) {
const args = and.queryArgs as QueryArgument
if (args?.query !== undefined) {
// remult.context is separate for each request
remult.context.queryArgument = args
delete and.queryArgs
}
}
return filter
}})export class Post {
@Fields.cuid()
id = ''
// ...
// eslint-disable-next-line no-use-before-define
@Fields.number<Post>({
sqlExpression: () => {
if (!remult.context.queryArgument?.query) { return '(select 0) ' }
const escapedSubstring = escapeSqlString(
remult.context.queryArgument.query
)
return `ts_tank(textsearchable_index_col, websearch_to_tsquery(${escapedSubstring}))`
}
})
tsRank = 0
@Fields.json({
serverExpression: () => ({}),
valueConverter: {
toJson: x => JSON.stringify(x),
fromJson: y => JSON.parse(y)
}
})
queryArgs?: QueryArgument
static fulltextSearch = Filter.createCustom<Post, { query: string }>(
({ query }) =>
SqlDatabase.rawFilter(
({ param }) => {
return `textsearchable_index_col @@ websearch_to_tsquery(${param(query)})`
}
)
)}
Usage context:
if (query.q) {
filterOptions.where!.queryArgs = { query: query.q }
filterOptions.where!.$and = [Post.fulltextSearch({ query: query.q })]
filterOptions.orderBy!.tsRank = 'desc'}
—
Reply to this email directly, view it on GitHub
<#438 (reply in thread)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/AD65PU7S7FT6TWZAHCWGW2TZEL4ZLAVCNFSM6AAAAABHUKXRVWVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4TKNRYGQ3DS>
.
You are receiving this because you commented.Message ID:
***@***.***>
|
Beta Was this translation helpful? Give feedback.
-
Cool - we will simplify it in the future
Thanks
…On Mon, May 27, 2024 at 5:07 PM Darkside73 ***@***.***> wrote:
I've figured out. The issue appeared since I do not use filter (only
custom orderBy). So I've hacked backendPreprocessFilter like that:
backendPreprocessFilter: (filter) => {
remult.context.queryArgument = filter.queryArgs
delete filter.queryArgs
return filter}
And now it produces desirable SQL
select
"id", "title", ts_rank(textsearchable_index_colA, websearch_to_tsquery('query'))from "posts"where
textsearchable_index_col @@ websearch_to_tsquery($1)Order By
ts_rank(textsearchable_index_colA, websearch_to_tsquery('query')) desc
{ arguments: { '$1': 'query' }
—
Reply to this email directly, view it on GitHub
<#438 (reply in thread)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/AD65PUZUSMSXA5HBDY2UDI3ZEM42NAVCNFSM6AAAAABHUKXRVWVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4TKNZQHE2DI>
.
You are receiving this because you commented.Message ID:
***@***.***>
|
Beta Was this translation helpful? Give feedback.
-
Hi @Darkside73 I've started to play around with a solution to this issue - trying to keep things as tightly typed as I can - checkout this test, let me know what you think |
Beta Was this translation helpful? Give feedback.
-
How to add custom ORDER BY for ordering by complex expression rather than by one field? For example sort results by full text rank in PostgreSQL:
Or at least is there a way to extract this into entity class similar to
Filter.createCustom
?Beta Was this translation helpful? Give feedback.
All reactions