group by in editor 1.6.1

piq_pm_pjk

Hello all

We're trying to get the most recent record from each group of widgets. Is there a way in 1.6.1 to natively do a group by in our table query?

allan

No sorry. There is no group by available using the provided PHP or .NET libraries for Editor.

Allan

rf1234

I would also like to use EDITOR for more complex reporting which requires GROUP BY. I guess I've found a solution for it. I built a table that holds the grouping criteria for my reporting: The column "term_remaining" in that table holds the values "<1", "1-5", "5+". etc. for example.

The number of rows in that table is the maximum number of grouping criteria that any one table column may hold. If the list for another column is shorter the remaining fields contain NULL values. Normally I wouldn't like this but in this case I have no issue. It is a very small table anyway. With about a dozen columns and not more than 30 rows. Which means that for column "term_remaining" 27 rows would be NULL.

For the reporting of loans by "term_remaining" I read my table with Editor. I only read one field: "term_remaining". Nevertheless I have 5 field instances. The first being "term_remaining" the other four are aliases of "term_remaining". Each field has a getFormatter function to return the respective value for the criterion based on "term_remaining" which is passed into the getFormatter as $val, e.g. EUR loans, GBP loans etc. All fields are ->set(false) so that Editor does not update them. The getFormatters contain custom SQLstatements that return the values required for the field and the criterion read by Editor. So I have no need for SQL Group by any longer and can do all the formatting etc. with PHP. No worries about the intricacies of JSON, AJAX etc. either.
Will share once I will have this implemented. Will probably take a little while. Just thought about it when I woke up this morning because I had always wanted a solution for this ...

This might NOT be a solution if you don't know the number of groups before hand but for me it is absolutely sufficient.