Filters

Learning Earnings "Filters"

In our PHP system many of the queries fell into a pattern. Object in the system were relevant for users based on:

• State
• School
• Classroom
• Grade
• User Type (Student, Teacher, School Administrator)

The queries on these items were like:

where
    (user.school = ? or ? is null) and
    (user.state = ? or ? is null) and
    (classroom = ? or ? is null) and  /* EXTREME SIMPLIFICATION!! tbl_classroomdetails table join */
    (grade is between ? and ?) and
    (usertype = ? or ? is null)  /* minor simplification */

Because of this we were constantly adding columns to tables for each of those. To avoid making schema changes and refactoring lots of sql to include more columns we came up with a method of assigning a filterID to a set of criteria and storing that id on objects that we needed to restrict access to.