(Sorry about the lame title, if someone has a better title let me know and I think I can edit it)
Hi,
I have a design question about the best way to enable filtering of records at runtime, based on criteria that an end user (i.e. an admin) can set. I'm sure I'm describing that poorly so let me give a semi contrived example. Let's say I have the following: 1) a Program model, a User model, and a join table 2) Users have the ability to enroll in a Program (hence the join table) 3) The Program's that they can enroll in (and thus the ones that are shown to them) are dependent on: a) Characteristics of the user (for this example let's say sex Male/ Female, and marital status Married/Single) b) Admin configuration of the Program (for example Program #1 requires them to be Male AND Married, Program #2 requires them to be Female OR Single, etc.) 4) For 3a this information is collected during signup and at other types, for 3b the admins create a new program and need to be able to set the criteria.
I'm curious the best way to store these criteria? Right now I have a criteria field on Program that has a templated (in liquid), logical condition (parsed with treetop). This accomplishes the features pretty straightforward: read all programs, evaluate each condition with a hash of the user attributes, show the ones that return true.
However, this is clearly bad from a performance scalability standpoint as the whole Program table needs to be read in, which will grow and grow. I can't figure out a way to make that issue go away since the (potential) construction of a SQL condition for the query depends on the particular criteria for a program.
Any suggestions?
Thanks, \Peter