Calc field mass update

[procajlok]

Hello!
In my case i have 200 records with form where i have one repeat group. In this repeat group i have fields, databasejoin elements etc.

Now. I Want add to this form (to parent form, not to repeat group) calc fields where i want calculate (maybe by SQL clause COUNT) in example how much i have records with this Parent_id and id=2 in some databasejoin element.
Example - i have group. In group i havedatabasejoin element Sex (male/female). I want calculate how many male i have in group.

Question - When i add this to parent form, i must go to edit every record and save it to recalculate this new field or maybe i have something to mass recalculate (update) fields on records? Cron?

 

[Sophist]

I am working from memory (which at my age is increasingly unreliable), but IIRC:

In a calc element which is not in the repeat group, when you use a placeholder for an element which is in a repeat group, you should get a string which looks like a JSON array of the repeat group values.

From this you should be able to count the total number of repeat group records or the number of repeat group records with a particular value.

 

[procajlok]

Its true when i want calc 'on fly' - Add record in repeat group = recalculate calc field = show how many male in group.
What when i don't want to change records in repeat groups and i want only add calc field and mass calculate it by example SQL syntax in this calc field:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('COUNT(*)')->from('zgloszenia_14_repeat')->where('parent_id = "{zgloszenia___id_raw}"')->where('sex = 2');
$db->setQuery($query);
$count1 = $db->loadResult();
return $count1;

 

[Sophist]

The problems with doing it via an SQL query are:

a. It won't work on Save or in an Ajax calc - because a new repeat group row won't have been saved to the database yet; and
b. You will get performance problems on list views if Only Save on Calc is No.

You should really try to do this using placeholders.

 

[cheesegrits]

This kind of thing is usually best done with a PHP form plugin, in this case running "onAfterProcess" (so the joined data has been written to the database). Don't use a calc, just make the element you want to update as a 'field', and update it by hand with a query in the form plugin.

Placeholders should work in that context, so something like ...

$db= JFactory::getDbo();
$query=$db->getQuery(true);
$query->update('zgloszenia')
->set("`yourfield` = (select COUNT(*) FROM `zgloszenia_14_repeat` WHERE `parent_id` = '{zgloszenia___id_raw}' AND sex = 2)")
->where("id = '{zgloszenia___id_raw}'")
$db->setQuery($query);
// remove this line after testing
var_dump((string)$query);exit;
$db->execute;

As per the comment, the var_dump() line is just for testing - submit the form and make sure the query that dumps out looks right, then remove or comment out that line.

-- hugh

 

[procajlok]

Ok, but as I understand set this field run only when i go to every record, edit and save. My question is how I can set this field (OK - run this PHP code) for all records on one take? I have more than 200 records...

EDIT: I want this only for list view, for reports. Maybe you have a simpler and better way for that?

 

[cheesegrits]

You could set them all intially by running a query by hand in phpMyAdmin (or whatever), something like ...

UPDATE zgloszenia SET `yourfield` = (SELECT COUNT(*) FROM `zgloszenia_14_repeat` WHERE `parent_id` =  zgloszenia.id AND sex = 2)

... which should set the count field on all rows in the main table in one query.

Still run the PHP form plugin, which will then update the total for the specific row when saving a form.

-- hugh

 

[Sophist]

I run an update like this in the List PHP plugin - to update the values before they are displayed in the list.

 

[cheesegrits]

That's another way of doing it. Which method to use depends on how many rows / how intense the query is to do it to the whole table, and whether you ever need the data in any context that doesn't involved a list load, and whether the data you are working from ever gets changed outside of a form submit.

I have several cases where I do both - rebuild a single row's "thing" on save, AND rebuild the entire table's 'thing' when displaying a list view, because the workflow requires it.

-- hugh