代码之家 › 专栏 › 技术社区 › Jeffrey04 George

排序和筛选记录

sorting filter sql

Jeffrey04 George · 技术社区 · 17 年前

对于如何以正确的方式对记录列表(来自数据库)执行排序或筛选,是否有任何已知的模式/算法?当前的尝试涉及使用提供一些筛选和排序选项的窗体,然后将这些标准和排序算法附加到我现有的SQL中。然而,我发现用户可能会得到他们不希望看到的结果,这很容易被滥用。

我正在构建的应用程序是一个调度程序,我将所有事件存储在一个数据库表中。然后,根据用户的级别/角色/权限,将显示不同的数据子集。因此,我的查询可以像

SELECT  * 
FROM    app_event._event_view 
WHERE   ((class_id = (SELECT class_id FROM app_event._ical_class WHERE name = 'PUBLIC') AND class_id != (SELECT class_id FROM app_event._ical_class WHERE name = 'PRIVATE') AND class_id != (SELECT class_id FROM app_event._ical_class WHERE name = 'CONFIDENTIAL')) OR user_id = '1') 
        AND calendar_id IN (SELECT calendar_id FROM app_event.calendar WHERE is_personal = 't') 
        AND calendar_id = (SELECT calendar_id FROM app_event.calendar WHERE name = 'personal') 
        AND state_id IN (1,2,3,4,5,6) AND EXTRACT(year FROM dtstart) = '2008' 
        AND EXTRACT(month FROM dtstart) = '11'

由于我更关心的是提供正确的信息子集,所以性能目前不是一个主要问题(因为上面提到的sql是由脚本逐子句生成的)。我正在考虑将所有这些复杂的SQL语句转换为视图,这样生成的SQL不合适的可能性就更小了。

3 回复 | 直到 17 年前

Ian Varley 17 年前

首先,如果使用联接,则此查询的外观和性能将更好:

SELECT  * 
  FROM    
    app_event._event_view EV
    INNER JOIN app_event.calendar C
        ON EV.calendar_id = C.calendar.id
    INNER JOIN app_event._ical_class IC
        ON C.class_id = EV.class_id
  WHERE   
    C.is_personal = 't'
    AND C.name = 'personal'
    AND state_id IN (1,2,3,4,5,6) 
    AND EXTRACT(year FROM dtstart) = '2008' 
    AND EXTRACT(month FROM dtstart) = '11'
    AND (
        IC.name = 'PUBLIC' -- other two are redundant
        OR user_id = '1'
        )

这是保持复杂性的良好开端。然后,如果您想直接向SQL添加额外的过滤器,可以在末尾附加更多的“AND…”语句。由于所有的条件都与AND(或安全地包含在括号中)相关,因此不可能有人在这些条件之后添加一个条件,从而以某种方式撤消上面的条件——也就是说,一旦用子句的一部分限制了结果,就不能用后面的子句“取消”它,如果它与AND s串在一起。(当然,如果这些附加子句靠近用户文本输入,则必须对它们进行清理以防止SQL注入攻击。)

一般来说,数据库的过滤速度比任何其他层都快,因为在数据库上使用WHERE子句通常会使数据库避免从磁盘上读取不必要的记录,这比使用CPU所能做的任何事情都慢几个数量级。在DB上排序通常也更快,因为DB通常可以以这样的方式进行连接,即记录已经按照您希望的顺序排序。所以,从性能上来说,如果你能在数据库上做到,那就更好了。

不过,您说过性能并不是这里的关键,所以在业务逻辑中以编程方式添加过滤器可能对您很好,而且比处理SQL字符串更容易阅读。

Stein G. Strindhaug 17 年前

很难理解这个查询,因为我必须大量滚动,因为我不知道数据库。。。

但是如果特权是“一维的”,例如管理员可以看到所有东西,高级用户可以看到的少于管理员,来宾可以看到的少于高级用户,等等:您可能可以在事件和用户中都将特权实现为整数,然后

select from event where conditions AND event.privilegue <= user.privilegue

如果您将privilegue值设置为10000(high/admin)、5000、1(lower/guest)等值,则以后可以在这两者之间添加更多级别,而不必更改所有代码。

user38123 17 年前

使用ORM工具或使用如下参数:

SELECT  * 
FROM    app_event._event_view 
WHERE
    (
        :p_start_year is null or
        (state_id IN (1,2,3,4,5,6) AND EXTRACT(year FROM dtstart) = :p_start_year)
    )
    and
    (
        :p_date_start is null or
        AND EXTRACT(month FROM dtstart) = :p_date_start
    )