Correct SQL calculation?

[procajlok]

Hello!
Is this correct? I want return number of record from repeat group where parent_id is from actual editing record and (in this field) where dropdown field (rozmiar_koszulki) is "1".
There i use calculation field. Is on next page (next group) right after the repeat group. If you will be so good to log into the system and check it, this field is "pp_ilosc_KD_XS" in DEV SDM page.
Any chance to calculate this field 'on the fly'?

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('COUNT(*)')->from('zgloszenia_14_repeat')->where('parent_id ='.'{zgloszenia___id}')->AND('rozmiar_koszulki = '.'1');
$db->setQuery($query);
$count = $db->loadResult();
return $count;

Thanks!

 

[procajlok]

I've done more tests and unfortunately the results are wrong with such syntax...

 

[cheesegrits]

AND() isn't valid syntax for the J! query builder. I logged in and changed it to this:

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

I can't really tell you if that's giving the right results, as I don;t know what the count should be. But I remove that last where(), so it's just counting related rows, it gives a number.

If you uncomment that var_dump() line (remove the //), you'll see the actual query in the page, which should help you debug.

Note that when you do successive where() in a chain, it'll use the test the first one was created with (which defaults to AND). If you need to mix AND with OR, you have to do it in one statement, like ->where("foo = 1 AND bar = 2 OR baz = 4"). But if just doing simple succesive AND, you can chain them together, ->where('foo = 1')->where('bar = 2')

-- hugh

 

[procajlok]

That's great! Very Thank you! But i have other question - on next page to the group with repeat group i have calc element. This element return COUNT elements in repeat group. Actually this calc element is update only when i save form (record). How I must configure this calc element, form, list or something to update value on go to this page (after add record in repeat group, before save record) ?

EDIT: zgloszenia___pp_faktycznailosc count record in repeat group.

 

[cheesegrits]

Have you tried setting "Only calc on save" to "No", so it calcs when being rendered as well (on list and details view display)?

-- hugh

 

[procajlok]

Yes i try but i haven't any chages. At all it's default setting, i do not change it if I do not have to

 

[cheesegrits]

Just re-read your question. You want it to update on the page? In which case, have you tried enabling AJAX calc?

-- hugh