JS 实现关键字搜索表格并高亮显示关键字

算法白菜

于 2024-08-02 17:34:06 发布

阅读量388

点赞数 3

文章标签： javascript 开发语言前端职场和发展 sql postgresql

本文链接：https://blog.csdn.net/m0_56090828/article/details/140875850

版权

PostgreSQL 官网文档：

8.11. 文本搜索类型 (postgres.cn)

12.3. 控制文本搜索 (postgres.cn)

PostgreSQL提供两种数据类型，它们被设计用来支持全文搜索，全文搜索是一种在自然语言的文档集合中搜索以定位那些最匹配一个查询的文档的活动。tsvector类型表示一个为文本搜索优化的形式下的文档，tsquery类型表示一个文本查询。

要实现全文搜索必须要有一个从文档创建tsvector以及从用户查询创建tsquery的函数。而且我们需要一种有用的顺序返回结果，因此我们需要一个函数能够根据文档与查询的相关性比较文档。还有一点重要的是要能够很好地显示结果。PostgreSQL对所有这些函数都提供了支持。

select ( ((coalesce('aa', '') || ' ') || coalesce('bbbb', '')) || ' ')   || coalesce('ccc', '')

select to_tsvector('jiebaqry', ( ((coalesce('aa', '') || ' ') || coalesce('bbbb', '')) || ' ')   || coalesce('ccc', '') )

首先是数据库字段的设置


ALTER TABLE public.plt_uniformpn_pndetails ADD tags_vector tsvector GENERATED ALWAYS AS (to_tsvector('jiebaqry'::regconfig, (((COALESCE(plt_tcvalue, '')|| ' ') || COALESCE(plt_scvalue, '')) || ' ') || COALESCE(plt_envalue, ''))) STORED NULL;

前端脚本：

let _this = this;
let locale = this.env.locale.displayName.split('-')[1];
locale = locale == 'en' ? 'en' : (locale == 'zh' ? 'sc' : 'tc')
const categoryOid = this.getAddinById('categoryAddin').getValue();
const subcategoryOid = this.getAddinById('subcategoryAddin').getValue();
const keyWord = this.getAddinById('keyWordAddin').getValue();
let queryStr = ''
let qstr = ''
if (categoryOid) {
    queryStr += ` and categoryoid = '${categoryOid}' `
    qstr += ` and obj.categoryoid = '${categoryOid}' `
}
if (subcategoryOid) {
    queryStr += ` and subcategoryoid = '${subcategoryOid}' `
    qstr += ` and obj.subcategoryoid = '${subcategoryOid}' `
}
debugger;
let grid = this.getAddinById('Grid1');
let rowdata = this.getAddinById('Grid1').getRowData();
if (keyWord && categoryOid && subcategoryOid) {
    this.getAddinById('Grid1').setColumnVisible(8, true);
    this.callServer({"keyword": keyWord, "queryStr": queryStr, "locale": locale}).then(res => {
        debugger;
        let list = res.data.data.list;
        let oids = []
        let objs = []
        list.map(x => {
            oids.push(x[0]);
            objs.push({oid: x[0], specvalue: x[1]});
        });
        let rdata = []
        rowdata.map(row => {
            if (oids.indexOf(row.oid) != -1) {
                row.specvalue = objs[oids.indexOf(row.oid)].specvalue;
                rdata.push(row);
            }
        });
        _this.getAddinById('Grid1').setRowData(rdata);
        debugger;
        setTimeout(() => {
            highlight(rowdata, keyWord);
        }, 1000);
    });
} else {
    this.getAddinById('Grid1').setColumnVisible(8, false);
    this.getAddinById('Grid1').freshData(qstr + ' order by obj.categoryOid, obj.subcategoryOid');
}

后端脚本：

let keyword = this.customData.keyword;
let queryStr = this.customData.queryStr;
let locale = this.customData.locale;
let sql = `select d.plt_pnoid, string_agg(concat(s.plt_${locale}name, '-', d.plt_${locale}value), ' , ') as specvalue
from plt_uniformpn_pndetails d
left join plt_uniformpn_spec s on s.plt_oid = d.plt_specoid 
where d.plt_pnoid in (
	select oid
	from plt_uniformpn_pn_view 
	where systype = 'spare' ${queryStr}
) and ( d.tags_vector @@ websearch_to_tsquery('jiebaqry', '${keyword}') or s.plt_enname like '%${keyword}%' or s.plt_tcname like '%${keyword}%' or s.plt_scname like '%${keyword}%' )
group by d.plt_pnoid `;

let list = this.em.createNativeQuery(sql).getResultList();

this.res = {
    list: list
}

高亮显示函数：

function highlight(rowdata, keyWord) { 
    
    let reg = new RegExp(`${keyWord}`, 'gi');

    rowdata.map(x => {
        debugger;
        let str = x.specvalue.match(reg);
        let lists = document.querySelectorAll("div.ag-center-cols-container > div.ag-row > div.ag-cell:last-child");
        lists.forEach(node => {
            debugger;
            node.innerHTML = node.innerHTML.replace(new RegExp(str[0], 'gm'), `<span style='background-color:orange'>${str[0]}</span>`);
        })
    })
    
}