From b14c0a2d23482c2afd77f22fe1646b86b5150281 Mon Sep 17 00:00:00 2001
From: alex <alex@alexloehr.net>
Date: Fri, 06 Jun 2025 16:17:33 +0000
Subject: [PATCH] adding search
---
lib/db.js | 109 +++++++++++++++++---------
app.js | 37 ++++----
vue/src/components/Pagination.vue | 1
vue/src/pages/Users.vue | 23 ++++-
lib/search.js | 3
vue/src/lib/api.js | 12 ++
6 files changed, 119 insertions(+), 66 deletions(-)
diff --git a/app.js b/app.js
index 7b99446..92b42ee 100644
--- a/app.js
+++ b/app.js
@@ -32,11 +32,28 @@
}
})
+/////// SEARCH ////////////////////////////////////////////////////////////////
+
+const searchLib = require("./lib/search")
+searchLib.doIndex().catch(console.error)
+fastify.get("/api/search/user", async function (req, res) {
+ console.log(req.query)
+ const search = req.query?.search
+ if (!search) {
+ return res.code(422).send({status: "error", msg: "no search"})
+ }
+ else {
+ console.log(search)
+ const data = await searchLib.search(search)
+ return res.send(data)
+ }
+})
+
fastify
/////// USER ////////////////////////////////////////////////////////////////
.get('/api/users', async function (req, res) {
- const {offset, limit} = req.query
- const users = await db.getUsers(offset, limit)
+ const {offset, limit, search} = req.query
+ const users = await db.getUsers(offset, limit, search)
return res.send(users)
})
.get("/api/users/count", async function (req, res) {
@@ -156,22 +173,6 @@
}
})
-/////// SEARCH ////////////////////////////////////////////////////////////////
-
-const searchLib = require("./lib/search")
-searchLib.doIndex().catch(console.error)
-fastify.get("/api/search/user", async function (req, res) {
- console.log(req.query)
- const search = req.query?.search
- if (!search) {
- return res.code(422).send({status: "error", msg: "no search"})
- }
- else {
- console.log(search)
- const data = await searchLib.search(search)
- return res.send(data)
- }
-})
/////// STATIC ////////////////////////////////////////////////////////////////
diff --git a/lib/db.js b/lib/db.js
index 19519c1..3a59978 100644
--- a/lib/db.js
+++ b/lib/db.js
@@ -1,5 +1,6 @@
const mysql = require("mysql2/promise")
+const searchLib = require("./search")
const {host, port, user, database, password} = require("./../settings").db
/////////////////////////////////////////////////////////////////////////
@@ -43,23 +44,41 @@
/////////////////////////////////////////////////////////////////////////
-async function getUsers (offset = 0, limit = 10) {
+function getUserSearchQuery (offset, limit, search) {
+ const sel = `usr_id, login, firstname, lastname, gender, email, institution, street, city, zipcode, country, department, active`
+ if (!search || search === '') {
+ return `SELECT ${sel}
+ FROM ${database}.usr_data AS ud
+ WHERE login REGEXP '^[0-9]+$'
+ LIMIT ${limit}
+ OFFSET ${offset}
+ `
+ }
+ else {
+ const ids = searchLib.search(search)
+ if (!ids.length) return getUserSearchQuery(offset, limit) // nothing found
+ return `SELECT ${sel}
+ FROM ${database}.usr_data AS ud
+ WHERE login REGEXP '^[0-9]+$'
+ AND usr_id IN (${ids.join(",")})
+ LIMIT ${limit}
+ OFFSET ${offset}
+ `
+ }
+}
+
+async function getUsers (offset = 0, limit = 10, search = null) {
+ console.log("++++++++++ get users", offset, limit, search)
limit = Number(limit) || 10
offset = Number(offset) || 0
- const sel = `usr_id, login, firstname, lastname, gender, email, institution, street, city, zipcode, country, department, active`
- // TODO user defined fields
// TODO check args for SQL Injection
const pool = await poolP
- const [results, fields] = await pool.query(
- `SELECT ${sel}
- FROM ${database}.usr_data AS ud
- WHERE login REGEXP '^[0-9]+$'
- LIMIT ${limit}
- OFFSET ${offset}
- `
- )
- const count = await getUserCount()
+
+ let userSearchQuery = getUserSearchQuery(offset, limit, search)
+ // console.log(userSearchQuery)
+ const [results, fields] = await pool.query(userSearchQuery)
+ const count = await getUserCount(offset, limit, search)
return {
total: count,
offset, limit,
@@ -67,14 +86,21 @@
}
}
-async function getUserCount () {
+async function getUserCount (offset, limit, search) {
const pool = await poolP
- const [results, fields] = await pool.query(
- `SELECT COUNT(*)
- FROM ${database}.usr_data AS ud
- WHERE login REGEXP '^[0-9]+$'`
- )
- return results[0]["COUNT(*)"]
+ // const q = getUserSearchQuery(offset, limit, search)
+ const q = getUserSearchQuery(0, 1000000, search) // hier darf kein Limit sein, offset=0
+ const q2 = `SELECT COUNT(*) AS count
+ FROM (${q}) AS X`
+ const [results, fields] = await pool.query(q2)
+ return results[0].count
+
+ // const [results, fields] = await pool.query(
+ // `SELECT COUNT(*)
+ // FROM ${database}.usr_data AS ud
+ // WHERE login REGEXP '^[0-9]+$'`
+ // )
+ // return results[0]["COUNT(*)"]
}
async function getUserByLogin (login) {
@@ -217,26 +243,33 @@
async function getKursItems2 (ref_id) {
const pool = await poolP
const q = `
-WITH RECURSIVE tree (parent_id, obj_id, ref_id, title, type ) AS (
- SELECT ci.parent_id, or2.obj_id , ci.obj_id as ref_id, od.title, od.type
- FROM ${database}.crs_items ci
- INNER JOIN ${database}.object_reference or2 ON or2.ref_id = ci.obj_id
- INNER JOIN ${database}.object_data od ON od.obj_id = or2.obj_id
- WHERE ci.obj_id = ${ref_id} -- Kurs ref_id
- AND or2.deleted is NULL
+ WITH RECURSIVE tree (parent_id, obj_id, ref_id, title, type) AS (SELECT ci.parent_id,
+ or2.obj_id,
+ ci.obj_id as ref_id,
+ od.title,
+ od.type
+ FROM ${database}.crs_items ci
+ INNER JOIN ${database}.object_reference or2 ON or2.ref_id = ci.obj_id
+ INNER JOIN ${database}.object_data od ON od.obj_id = or2.obj_id
+ WHERE ci.obj_id = ${ref_id} -- Kurs ref_id
+ AND or2.deleted is NULL
- UNION ALL
+ UNION ALL
- SELECT child.parent_id, or2.obj_id , child.obj_id as ref_id, od.title, od.type
- FROM ${database}.crs_items child
- INNER JOIN ${database}.object_reference or2 ON or2.ref_id = child.obj_id
- INNER JOIN ${database}.object_data od ON od.obj_id = or2.obj_id
- JOIN tree ON child.parent_id = tree.ref_id
- WHERE or2.deleted is NULL
-)
-SELECT * FROM tree
-ORDER BY tree.ref_id
-`
+ SELECT child.parent_id,
+ or2.obj_id,
+ child.obj_id as ref_id,
+ od.title,
+ od.type
+ FROM ${database}.crs_items child
+ INNER JOIN ${database}.object_reference or2 ON or2.ref_id = child.obj_id
+ INNER JOIN ${database}.object_data od ON od.obj_id = or2.obj_id
+ JOIN tree ON child.parent_id = tree.ref_id
+ WHERE or2.deleted is NULL)
+ SELECT *
+ FROM tree
+ ORDER BY tree.ref_id
+ `
const [results] = await pool.query(q)
return results
}
@@ -290,7 +323,7 @@
async function getUserTeilnahmen (usr_id) {
const pool = await poolP
- const q = `SELECT om.obj_id, or2.ref_id , om.usr_id, od.title , ulm.status, om.passed, ulm.status_changed
+ const q = `SELECT om.obj_id, or2.ref_id, om.usr_id, od.title, ulm.status, om.passed, ulm.status_changed
FROM ${database}.obj_members om
INNER JOIN ${database}.object_reference or2 ON or2.obj_id = om.obj_id
INNER JOIN ${database}.usr_data ud ON ud.usr_id = om.usr_id
diff --git a/lib/search.js b/lib/search.js
index d054498..c332548 100644
--- a/lib/search.js
+++ b/lib/search.js
@@ -88,7 +88,8 @@
}
function search (query) {
- return idxUser.search(query)
+ const searchLimit = 10000
+ return idxUser.search(query, searchLimit)
}
function searchUsers (query, user) {
diff --git a/vue/src/components/Pagination.vue b/vue/src/components/Pagination.vue
index 5fd63ea..69fc0e5 100644
--- a/vue/src/components/Pagination.vue
+++ b/vue/src/components/Pagination.vue
@@ -70,7 +70,6 @@
<div class="pagination">
<div class="pagination-buttons">
-
<input type="button" @click="goStart()" class="start" value="«">
<input type="button" @click="goPrev()" class="prev" value="‹">
<span class="current">{{ offset }} - {{ offset + limit }} / {{ total }}</span>
diff --git a/vue/src/lib/api.js b/vue/src/lib/api.js
index 321d1de..545620e 100644
--- a/vue/src/lib/api.js
+++ b/vue/src/lib/api.js
@@ -42,8 +42,8 @@
/////// USER ////////////////////////////////////////////////////////////////
-export async function getUsers (offset, limit) {
- const res = await fetch(`${apiBase}/users?offset=${offset}&limit=${limit}&token=${apiToken.value}`)
+export async function getUsers (offset, limit, search) {
+ const res = await fetch(`${apiBase}/users?offset=${offset}&limit=${limit}&search=${search}&token=${apiToken.value}`)
return res.json()
}
@@ -56,3 +56,11 @@
const res = await fetch(`${apiBase}/user/teilnahmen/${userId}?token=${apiToken.value}`)
return await res.json()
}
+
+/////// SEARCH ////////////////////////////////////////////////////////////////
+
+export async function searchUsers (query) {
+ const res = await fetch(`${apiBase}/search/user?search=${query}&token=${apiToken.value}`)
+ return await res.json()
+}
+
diff --git a/vue/src/pages/Users.vue b/vue/src/pages/Users.vue
index 69ce06a..d4aac5d 100644
--- a/vue/src/pages/Users.vue
+++ b/vue/src/pages/Users.vue
@@ -3,9 +3,9 @@
import {onMounted, reactive, ref} from "vue"
import {useRoute} from "vue-router"
import Pagination from "../components/Pagination.vue"
-import {getUsers, routerBase} from "@/lib/api"
+import {getUsers, routerBase, searchUsers} from "@/lib/api"
import {useRouteQuery} from '@vueuse/router'
-import {onKeyStroke} from "@vueuse/core"
+import {onKeyStroke, useDebounceFn} from "@vueuse/core"
const route = useRoute()
const userId = route.params.userId
@@ -21,10 +21,20 @@
onMounted(() => init(offset.value))
+/////// SEARCH ////////////////////////////////////////////////////////////////
+
+const search = useRouteQuery("search", "",)// {transform: s => s.trim()})
+async function _doSearch (search) {
+ console.log("doSEarch", search)
+ return init(offset.value, search)
+}
+const doSearch = useDebounceFn(_doSearch, 500)
+
/////////////////////////////////////////////////////////////////////////
-async function init (noffset = 0) {
- const data = await getUsers(noffset, limit)
+async function init (noffset = 0, search) {
+ console.log(">>> INIT", noffset, search)
+ const data = await getUsers(noffset, limit, search)
console.log(data)
Object.assign(users, data)
offset.value = noffset
@@ -32,8 +42,9 @@
function go (offset) {
console.log("go to offset", offset)
- return init(offset)
+ return init(offset, search.value)
}
+
</script>
@@ -54,7 +65,7 @@
<div class="users-header">
<div class="search">
Search
- <input type="text" size="30">
+ <input @keyup="doSearch(search)" v-model="search" type="text" size="30">
</div>
<Pagination :offset="users.offset" :limit="users.limit" :total="users.total" @go="go" />
</div>
--
Gitblit v1.8.0