nodejs -- 分组查询，从25行代码，优化成 50行

分组查询 写代码比较简单的就是循环 获取大类 然后匹配小类 ； 大概代码是25行， 然后优化成联表查询，大概50行。

原代码如下

let results = []
    try {
        let home_list = await db('all', 'select * from home_list ORDER BY _sort ASC')
        for(var i = 0; i < home_list.length; i++){
            let row = home_list[i]
            console.log(row)
            try {
                let course_list = await db('all', 'select * from course where home_list_id=?', [row.id])
                results.push({
                    name: row.name,
                    children: course_list
                })
            } catch (error) {
                console.log(error)  
                resolve({code: -1})
            }
        }
    } catch (error) {
        console.log(error)  
        resolve({code: -1})  
    }

联表查询优化如下

flat_to_tree(flat, option){
      var arrs = []
      var item = []
        let config = {
            pid: option.pid || 'pid',
            children: option.children || 'children'
        }
      flat.forEach((a, i, arr) => {
            item.push(a)
            if(arr[i + 1] && a[config.pid] !== arr[i + 1][config.pid]){
                arrs.push({
                    ...a,
                    [config.children]: JSON.parse(JSON.stringify(item))
                })
                item = []
            }
            if((!arr[i + 1])){
                arrs.push({
                    ...a,
                    [config.children]: JSON.parse(JSON.stringify(item))
                })
                item = []
            }
        })
      return arrs
    }

let results
    try {
        results = await db('all', `
        select h.name as home_list_name, c.name as course_name, h.id as home_list_id, c.id as course_id, c.src as course_img 
        from home_list as h  join  course as c 
        on h.id=c.home_list_id 
        ORDER BY h._sort ASC;`)
        results = _.flat_to_tree(results, {
            pid: 'home_list_id',
            children: 'children'
        })
    } catch (error) {
        console.log(error)
        resolve({code: -1})
    }
    resolve({code: 200, data: results})

这里的话，就是联表查询，得到平数据结构，但是我们需要树结构。

这里的flat_to_tree函数就是做转tree结构作用

目前只支持二级。

效果

--END--