如何用JavaScript实现一个简单的数据库查询引擎？

WHERE

SELECT

class SimpleQueryEngine {
    constructor(data) {
        this.data = data;
    }

    // 核心查询方法
    query(options = {}) {
        let results = [...this.data]; // 复制一份数据，避免修改原始数据

        // 1. 处理 WHERE 条件
        if (options.where) {
            results = this._applyWhere(results, options.where);
        }

        // 2. 处理 ORDER BY 条件
        if (options.orderBy) {
            results = this._applyOrderBy(results, options.orderBy);
        }

        // 3. 处理 SELECT (投影) 条件
        if (options.select) {
            results = this._applySelect(results, options.select);
        }

        return results;
    }

    // 辅助函数：应用 WHERE 条件
    _applyWhere(data, conditions) {
        // 这里可以支持多种条件组合，比如 AND/OR。
        // 为了简单，我们先实现一个只支持简单键值对匹配的AND逻辑。
        return data.filter(item => {
            for (const key in conditions) {
                // 暂时只支持直接相等判断
                if (item[key] !== conditions[key]) {
                    return false;
                }
            }
            return true;
        });
    }

    // 辅助函数：应用 ORDER BY 条件
    _applyOrderBy(data, orderByConfig) {
        // orderByConfig 可以是 { field: 'age', direction: 'asc' }
        const field = orderByConfig.field;
        const direction = orderByConfig.direction === 'desc' ? -1 : 1;

        if (!field) return data;

        return data.sort((a, b) => {
            if (a[field] < b[field]) return -1 * direction;
            if (a[field] > b[field]) return 1 * direction;
            return 0;
        });
    }

    // 辅助函数：应用 SELECT (投影) 条件
    _applySelect(data, fields) {
        if (!Array.isArray(fields) || fields.length === 0) {
            return data; // 如果没有指定字段，返回所有字段
        }
        return data.map(item => {
            const newItem = {};
            fields.forEach(field => {
                if (item.hasOwnProperty(field)) {
                    newItem[field] = item[field];
                }
            });
            return newItem;
        });
    }
}

// 示例用法：
const users = [
    { id: 1, name: 'Alice', age: 30, city: 'New York' },
    { id: 2, name: 'Bob', age: 24, city: 'London' },
    { id: 3, name: 'Charlie', age: 30, city: 'Paris' },
    { id: 4, name: 'David', age: 28, city: 'New York' },
];

const engine = new SimpleQueryEngine(users);

// 查询年龄为30的用户，并按姓名升序排列，只显示id和name
const result = engine.query({
    where: { age: 30 },
    orderBy: { field: 'name', direction: 'asc' },
    select: ['id', 'name']
});

console.log(result);
/*
[
  { id: 1, name: 'Alice' },
  { id: 3, name: 'Charlie' }
]
*/

// 查询所有用户，按年龄降序排列
const allUsersSortedByAge = engine.query({
    orderBy: { field: 'age', direction: 'desc' }
});
console.log(allUsersSortedByAge);
/*
[
  { id: 1, name: 'Alice', age: 30, city: 'New York' },
  { id: 3, name: 'Charlie', age: 30, city: 'Paris' },
  { id: 4, name: 'David', age: 28, city: 'New York' },
  { id: 2, name: 'Bob', age: 24, city: 'London' }
]
*/

filter

sort

map

WHERE

ORDER BY

SELECT

filter

find

WHERE age > 25 AND city = 'New York'

LOWER(name) = 'alice'

filter

sort

id

id

_applyWhere

>

<

>=

<=

LIKE

IN

NOT

AND

OR

_applyWhere

Array.prototype.filter

Array.prototype.sort

_applyJoin(leftData, rightData, leftKey, rightKey) {
    const joinedResults = [];
    leftData.forEach(leftItem => {
        rightData.forEach(rightItem => {
            if (leftItem[leftKey] === rightItem[rightKey]) {
                // 合并两个对象。注意处理字段冲突，这里简单合并
                joinedResults.push({ ...leftItem, ...rightItem });
            }
        });
    });
    return joinedResults;
}
// 在 query 方法中调用，可能需要修改 query 接口以支持多表
// 例如：engine.query({ join: { type: 'inner', on: { left: 'id', right: 'userId' }, with: ordersData } })

COUNT

SUM

AVG

MIN

MAX

GROUP BY

GROUP BY

_applyGroupBy(data, groupByField, aggregates) {
    const groupedData = new Map(); // Map<groupKey, Array<item>>

    data.forEach(item => {
        const groupKey = item[groupByField];
        if (!groupedData.has(groupKey)) {
            groupedData.set(groupKey, []);
        }
        groupedData.get(groupKey).push(item);
    });

    const result = [];
    groupedData.forEach((groupItems, groupKey) => {
        const aggregatedItem = { [groupByField]: groupKey }; // 包含分组字段

        aggregates.forEach(agg => {
            const { func, field, as } = agg; // 例如 { func: 'COUNT', field: '*', as: 'total' }

            let value;
            switch (func.toUpperCase()) {
                case 'COUNT':
                    value = groupItems.length;
                    break;
                case 'SUM':
                    value = groupItems.reduce((acc, curr) => acc + (curr[field] || 0), 0);
                    break;
                case 'AVG':
                    const sum = groupItems.reduce((acc, curr) => acc + (curr[field] || 0), 0);
                    value = sum / groupItems.length;
                    break;
                // 可以添加更多聚合函数
                default:
                    value = null; // 未知函数
            }
            aggregatedItem[as || `${func.toLowerCase()}_${field}`] = value;
        });
        result.push(aggregatedItem);
    });
    return result;
}
// 同样，query 方法需要修改来支持 group by
// 例如：engine.query({ groupBy: { field: 'city', aggregates: [{ func: 'COUNT', field: '*', as: 'userCount' }] } })

FROM -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT

query