Filter on sub group list

[actionwave34F]

Hi
We need to show and edit on limited data for a "detail" table "inline" on a list in a fabrik module list. Can't make it work.

One other approach is to limit the number of records listed in sub group list (details) of a master/detail concept. The details table have a lot of records over time and in all cases we need to show some records with certain property values , like current date. And then the result is possible to manage in the total form. In some cases now with ca 100 records we running out of memory and page creation takes ca 10 secs.
Are there any place I can put a filter without using pre-filter on the details table? That table is also used in other concepts!

We have tried to use "inlineedit" on the list data (details) but it is tricky business to make it work . Once data are edited I have to trigger calculations ... and no, can't make it work.

We have already made some changes to the list/form/detail templates and it working fine. But to filter out a result here is very inefficient.

I appreciate any hints how to solve this. If there is any solution.

 

[cheesegrits]

So just to be sure I understand, you are needing to restrict the number of rows in a repeat group, i.e. a joined table, but only in the form view, not in the list view? So, say, for each parent row, only X number of "children" are shown?

The only way I can think of doing this would be to copy the list, apply the 'child' pre-filter on that, and use custom links on the original list view so form/details views go to the copy.

The only other option would be to use a custom template, and only actually output the repeats you need. But as you say, that's not efficient.

We have tried to use "inlineedit" on the list data (details) but it is tricky business to make it work . Once data are edited I have to trigger calculations ... and no, can't make it work.

Neither can we, which is why we've quit working on that plugin. It won't work with joined data, and things like calculation triggers, submission plugins are a bit hit or miss. It works for simple use cases, and "it is what it is".

 

[actionwave34F]

Thanks!
Yes , you assume right and I suspected that answer.
Is there any way to "hook in " and limit the SQL query result for the "children" and also sort on fields? It will be rather "complicated" to copy lists as you propose. This system part is already more than complicated with a numer of relations. By limiting the DB results we have a much better performance .

 

[cheesegrits]

I looked at the hook solution before replying, but unfortunately we only have an onQueryBuilt hook in the list querying, not in the form's getData querying. In the list model, the last thing we do when building the list query is pass the query builder object to the onQueryBuilt plugin hook, which can modify the query object.

I could add one in the form's query building, but the form's query is still done by steam, building a big SQL string, rather than creating a query builder object. So any hook trying to modify it would be having to do it by pattern matching and string manipulation, which can get a little hairy.

I could maybe make it easier by restricting it to just getting a WHERE part (so a hook like onFormQueryBuildWhere) for us to include in the WHERE clause as we build that. Probably around line 3586 in the form model, just before we wrap $where in parens. The new hook could be added to the PHP form plugin triggers, and return anything it wants to add to the WHERE clause.

However, this is getting into voodoo territory, and definitely not part of a Standard sub. I don't mind helping, but it's have to be on a Pro sub.

-- hugh

 

[actionwave34F]

Ok , thanks , appreciate your effort. We will rethink this and reconsider what route to go.

 

[cheesegrits]

OK.

I'm not trying to be extortionate, there's just a limit to what I can do on a standard Sub and still pay the bills.
'
-- hugh

 

[actionwave34F]

Here I report back to you what we have done today in this matter.
Thanks for putting us in the right direction where to put the "hook in" and "filter".
In models/form.php after row 3582 we put as a raw hack:
// AWBIZ 20160310
$sqlextra = json_decode($form->params)->note;
if (strpos($sqlextra,'AND') !== false) { // to be pretty sure we have an SQL statement here
$sql .= ' '.$sqlextra;
}
// AWBIZ 20160310

The "raw" idea is to use the "note" field in forms to have the extra SQL statements like this ( no placeholders , just raw SQL):
AND DATE_FORMAT(`d6zoe_awhrm_workingtime`.`workingdate`, "%Y-%m-%d") >= SUBDATE(CURRENT_DATE() ,2) ORDER BY `d6zoe_awhrm_workingtime`.`workingdate` DESC

This gives us the desired result having the grouped list limited to just a few records out of many.
Of course it would be nice to have a pre-filter section in forms for this. Maybe later.

 

[cheesegrits]

I doubt we'll ever add prefilters for forms, as it's just not something enough people would ever need to make it worth adding as Yet Another Option in the UI, but at some point I'll probably add that hook and make it available for form plugins. Then the few people that need to can roll their own additions to the where clause.




Sent from my HTC One using Tapatalk