This is interesting because I'm building the exact same thing, which also has the be plugged into a Cake app.
For choosing the tables to query, my idea is to present a grid. Each cell has the table name and tables available through relations. When a table is selected, the cells either remain "on" or are disabled based on relations available. These will typically change depending on tables selected. Relations are discovered as defined, say hasOne, hasMany, etc. this is step one.
Step 2 involves selecting the fields to include in reports. These are typically prefixed with the model name and with underscores replaced with spaces. So User.first_name becomes "User's first name", User's country name or Country name, which I haven't worked out fully yet. I suppose some intuitive way to combine these into virtual fields would be nice.
The hairy bit is selecting conditions. I get requests to hard code queries such as finding the number of product ordered by users who have taken an online course or an in-person course so I have to be flexible. This part is tough to express in a UI simply, because X AND (Y OR Z) isn't the same as (X AND Y) OR Z. However, having some way of grouping conditions and requiring that such groups also have a binary operator by dragging and selecting seems the way to go, a la selecting icons on a desktop.
All of this can be saved as a "virtual table" for reuse. Output is typically a CSV for marketing purposes but my intention is to have this so I can plug in a PDF renderer with nice charts and what not, like Google Analytics.
Though something tells me building all this may be overkill if I can find a package that handles analytics and custom-defined events, along with the ability to upload backdated data!
For choosing the tables to query, my idea is to present a grid. Each cell has the table name and tables available through relations. When a table is selected, the cells either remain "on" or are disabled based on relations available. These will typically change depending on tables selected. Relations are discovered as defined, say hasOne, hasMany, etc. this is step one.
Step 2 involves selecting the fields to include in reports. These are typically prefixed with the model name and with underscores replaced with spaces. So User.first_name becomes "User's first name", User's country name or Country name, which I haven't worked out fully yet. I suppose some intuitive way to combine these into virtual fields would be nice.
The hairy bit is selecting conditions. I get requests to hard code queries such as finding the number of product ordered by users who have taken an online course or an in-person course so I have to be flexible. This part is tough to express in a UI simply, because X AND (Y OR Z) isn't the same as (X AND Y) OR Z. However, having some way of grouping conditions and requiring that such groups also have a binary operator by dragging and selecting seems the way to go, a la selecting icons on a desktop.
All of this can be saved as a "virtual table" for reuse. Output is typically a CSV for marketing purposes but my intention is to have this so I can plug in a PDF renderer with nice charts and what not, like Google Analytics.
Though something tells me building all this may be overkill if I can find a package that handles analytics and custom-defined events, along with the ability to upload backdated data!