Hi All,
So I have a bit of a problem. I need to set up dynamic queries with a particular group of "entities" in Rails. And I need multiple levels for each item. Basically this particular entity relationship is as follows:
CHART has many POINTS POINTS belong to many CHARTS
Now, when it comes to displaying CHARTS, the application will take stock of all related POINTS and reference the autonomous EVENTS table/entity in order to grab all POINT-related EVENTS that occurred between a specific period of time. That part works just fine.
What I need now, however, is the ability to create multiple levels of queries based on various groupings. Since I don't know all the required variations, I'll need to do everything dynamically.
For instance, right now we can query the SUM of all points grouped by DAY, WEEK, MONTH, YEAR for a specific TIME PERIOD. That all works. However, I'm being told that we now need to do that in groups of months or years. For instance, one of the new queries we need is as follows:
SUM of (MAX of select EVENTS, GROUPED BY POINT, DAY) GROUPED BY POINT, MONTH…..all over the course of a year or two years, or three years, etc.
You can understand how this would get out of hand quickly -- hence my desire to come up with a quality dynamic way to represent this to the creators of the CHART.
My apologies if this description is overly vague. I'm currently at home hopped up on cold meds. ;) Currently, due to the complexity, I'm actually considering making the ones that create CHARTS learn SQL and simply allow them to use direct queries. Not the greatest idea (as it opens a plethora of security holes), but it's a fix in the short term. In any event please let me know if you require more info and I'll do the best I can.
Thanks in advance!