Skip to content

Instantly share code, notes, and snippets.

@smashercosmo
Last active October 17, 2021 13:40
Show Gist options
  • Save smashercosmo/efd5122cf58432643205ba4111dee054 to your computer and use it in GitHub Desktop.
Save smashercosmo/efd5122cf58432643205ba4111dee054 to your computer and use it in GitHub Desktop.
import { executeQuery } from '../executeQuery'
const USER_SEARCH_HISTORY_LIMIT = 30
/**
* returns '$[0], $[1], $[2], ..., $[limit - 1]'
*/
function generateRange(limit: number) {
return new Array(limit - 1)
.fill(0)
.map((value, index) => `$[${index}]`)
.join(',')
}
function getFirst30Records(sql: string) {
return `JSON_EXTRACT(${sql}, ${generateRange(USER_SEARCH_HISTORY_LIMIT)})`
}
function insertRecordAndReturnNewArray(sql: string) {
return `JSON_ARRAY_INSERT(${sql}, '$[0]', ?)`
}
function removeRecordAndReturnNewArray() {
return `IFNULL(IFNULL(JSON_REMOVE(search_history, JSON_UNQUOTE(JSON_SEARCH(search_history, 'one', ?))), search_history), '[]')`
}
function updateSearchHistory(sql: string) {
return `UPDATE users SET search_history = ${sql} WHERE user_id = ?`
}
export function createSearchHistoryRecord({ userId, record }: { userId: string; record: string }) {
/* eslint-disable prettier/prettier */
const sql =
updateSearchHistory(
getFirst30Records(
insertRecordAndReturnNewArray(
removeRecordAndReturnNewArray()
)
),
)
/* eslint-enable prettier/prettier */
return executeQuery(sql, [record, userId, record])
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment