Sorting and filtering records
is there any known pattern / algorithm how to properly sort or filter a list of records (from a database)? My current attempt is to use a form that provides some filtering and sorting options, and then adds those criteria and sorting algorithm to the existing SQL. However, I believe it can be easily misused so that users can get results they shouldn't see.
The application I'm building is a scheduler that stores all events in a database table. Then, depending on the user / role / privilege level, a different subset of the data will be displayed. So my query might be as complex as
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'
Since I'm more worried about serving the correct subset of information, performance is not a major concern at the moment (since the mentioned sql was generated by a script, clause by clause). I'm going to turn all of these complex SQL statements into views, so the chances of generating SQL are less likely.
source to share
First, this query will look and perform better if you are using connections:
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'
)
This is a good start for reducing complexity. Then, if you want to add additional filters directly to SQL, you can add more "AND ..." statements at the end. Since all criteria are AND related (OR is in a safe place in parentheses), there is no way for someone to add a criterion after them that somehow overrides the ones above, i.e. after you have limited the results to one part , you cannot "disallow" it with a later clause if it is AND related. (Of course, if these additional sentences go much closer to user text input, you should sanitize them to prevent SQL injection attacks.)
A database can filter faster than any other level, in general, because using a WHERE clause on a database often allows the database to avoid even reading unnecessary records from disk, which is orders of magnitude slower than anything you can handle with a processor. Sorting is also often done in the database, because the database often performs joins in such a way that the records are already sorted in the order you want them to be. So performance is reasonable, if you can do it in DB, so much the better.
You said that performance isn't really the key here, so adding filters programmatically to your business logic might be great and easier to read for you than messing with SQL strings.
source to share
It's hard to understand this query because I have to scroll a lot and since I don't know the database ...
But if the privileges are "one-dimensional" for example. admins can see everything, users can see less than admins, visitors can see less than power users, etc .: you could implement the privileges as a whole in both the event and the user, and then
select from event where conditions AND event.privilegue <= user.privilegue
If you set privilege values like 10,000 (high / admin), 5000, 1 (lowest / guest), you can subsequently add more levels in between without changing your entire code.
source to share