Workflow function aggregation error

[radu]

I noticed when i have a condition in the aggregation function the whole function fails. For example i have the following function:

Code:

aggregation('count','SalesOrder','pl_grand_total','[sostatus,e,Approved,or],[sostatus,e,Delivered,or]')

which should count the sales orders that have the status 'Approved' or 'Delivered'. If I remove the condition from the expression, the function retunrs the correct count number of existing SO's but if i put any condition it craps out and return empty. This exact expression is in use on a older install and it works fine.
The log inspection reveals some clues. Here is what the query and result looks like on a new install:


query select count(vtiger_salesorder.pl_grand_total) as aggop  from vtiger_salesorder    inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_salesorder.salesorderid    left outer join vtiger_quotes on vtiger_quotes.quoteid=vtiger_salesorder.quoteid    left outer join vtiger_account on vtiger_account.accountid=vtiger_salesorder.accountid    left outer join vtiger_potential on vtiger_potential.potentialid=vtiger_salesorder.potentialid    left join vtiger_groups on vtiger_groups.groupid=vtiger_crmentity.smownerid    left join vtiger_users on vtiger_users.id=vtiger_crmentity.smownerid     where vtiger_crmentity.deleted=0 and vtiger_potential.potentialid = 25326 ORDER BY vtiger_crmentity.createdtime DESC and (vtiger_crmentity.deleted=0 AND ( vtiger_salesorder.sostatus IN (
select translation_key
from vtiger_cbtranslation
where locale="en_us" and forpicklist="SalesOrder::sostatus" and i18n = 'Approved') OR vtiger_salesorder.sostatus = 'Approved')  or ( vtiger_salesorder.sostatus IN (
select translation_key
from vtiger_cbtranslation
where locale="en_us" and forpicklist="SalesOrder::sostatus" and i18n = 'Delivered') OR vtiger_salesorder.sostatus = 'Delivered')  AND vtiger_salesorder.salesorderid > 0)

and it fails with:

Code:

DB DB >ADODB error  Query Failed:select count(vtiger_salesorder.pl_grand_total) as aggop  from vtiger_salesorder    inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_salesorder.salesorderid    left outer join vtiger_quotes on vtiger_quotes.quoteid=vtiger_salesorder.quoteid    left outer join vtiger_account on vtiger_account.accountid=vtiger_salesorder.accountid    left outer join vtiger_potential on vtiger_potential.potentialid=vtiger_salesorder.potentialid    left join vtiger_groups on vtiger_groups.groupid=vtiger_crmentity.smownerid    left join vtiger_users on vtiger_users.id=vtiger_crmentity.smownerid     where vtiger_crmentity.deleted=0 and vtiger_potential.potentialid = 25326 ORDER BY vtiger_crmentity.createdtime DESC and (vtiger_crmentity.deleted=0 AND ( vtiger_salesorder.sostatus IN (
select translation_key
from vtiger_cbtranslation
where locale="en_us" and forpicklist="SalesOrder::sostatus" and i18n = 'Approved') OR vtiger_salesorder.sostatus = 'Approved')  or ( vtiger_salesorder.sostatus IN (
select translation_key
from vtiger_cbtranslation
where locale="en_us" and forpicklist="SalesOrder::sostatus" and i18n = 'Delivered') OR vtiger_salesorder.sostatus = 'Delivered')  AND vtiger_salesorder.salesorderid > 0)::->[1064]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'and (vtiger_crmentity.deleted=0 AND ( vtiger_salesorder.sostatus IN (
se' at line 1

And this is how the query looks on the older system where is working ok:

Code:

select count(vtiger_salesorder.pl_grand_total) as aggop  from vtiger_salesorder    inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_salesorder.salesorderid    left outer join vtiger_quotes on vtiger_quotes.quoteid=vtiger_salesorder.quoteid    left outer join vtiger_account on vtiger_account.accountid=vtiger_salesorder.accountid    left outer join vtiger_potential on vtiger_potential.potentialid=vtiger_salesorder.potentialid    left join vtiger_groups on vtiger_groups.groupid=vtiger_crmentity.smownerid    left join vtiger_users on vtiger_users.id=vtiger_crmentity.smownerid     where vtiger_crmentity.deleted=0 and vtiger_potential.potentialid = 519508 and (vtiger_crmentity.deleted=0 AND ( vtiger_salesorder.sostatus IN (
select translation_key
from vtiger_cbtranslation
where locale="en_us" and forpicklist="SalesOrder::sostatus" and i18n = 'Approved') OR vtiger_salesorder.sostatus = 'Approved')  or ( vtiger_salesorder.sostatus IN (
select translation_key
from vtiger_cbtranslation
where locale="en_us" and forpicklist="SalesOrder::sostatus" and i18n = 'Delivered') OR vtiger_salesorder.sostatus = 'Delivered')  AND vtiger_salesorder.salesorderid > 0)

I have marked with red the difference between the two which is ORDER BY vtiger_crmentity.createdtime DESC and
.
The failed query gives syntax errors when launched in the sql engine. However if I remove the red part which does not used to be present before, the engine executes the query just fine. I am not well versed in sql but from my point of view, that select query has logic with or without the 'red addition'. The syntax itself, i tried to count the parentheses and other symbols to see if i can spot any obvious mistake(with my limited knowledge of the language), i could not find any but the engine will not execute if that 'sorting/order' is present. If i remove it and launch that query in the engine, it runs ok.
Maybe someone can get a clue about what is going on. Could be something with my own install.

[joebordes]

hopefully fixed with this commit: https://github.com/tsolucio/corebos/comm...04fffe6240

thanks for informing!

[radu]

It works indeed

Thanks!

[radu]

I spoke a bit too fast. This is strange:
It works with default fields but it does not work on when the condition is on custom fields. here is the error i get

aggregation('count','Quotes','pl_grand_total','[cf_1427,c,licitatie,or]')

Code:

DB >ADODB error  Query Failed:select sum(vtiger_quotes.pl_grand_total) as aggop  FROM vtiger_quotes    INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_quotes.quoteid    LEFT OUTER JOIN vtiger_account ON vtiger_account.accountid = vtiger_quotes.accountid    LEFT OUTER JOIN vtiger_potential ON vtiger_potential.potentialid = vtiger_quotes.potentialid    LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid    LEFT JOIN vtiger_users ON vtiger_crmentity.smownerid = vtiger_users.id    WHERE vtiger_crmentity.deleted = 0 AND vtiger_account.accountid = 27418 and (vtiger_crmentity.deleted=0 AND   (( vtiger_quotescf.cf_1427 LIKE '%licitatie%') ) AND vtiger_quotes.quoteid > 0)::->[1054]Unknown column 'vtiger_quotescf.cf_1427' in 'where clause'

So it says vtigerquotescf.cf_1427 is not known. But that field exists there. If i run the query in the engine it gives the same unknown column error. But the online demo seem to work ok, so i'm thinking it might be something with the engine?
I don't have a working log(from a good custom field query) so I can compare but here is a log from a aggregation with a standard field in condition - and this one works:

aggregation('count','Quotes','pl_grand_total','[description,c,licitatie,or]')

Code:

select sum(vtiger_quotes.pl_grand_total) as aggop  FROM vtiger_quotes    INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_quotes.quoteid    LEFT OUTER JOIN vtiger_account ON vtiger_account.accountid = vtiger_quotes.accountid    LEFT OUTER JOIN vtiger_potential ON vtiger_potential.potentialid = vtiger_quotes.potentialid    LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid    LEFT JOIN vtiger_users ON vtiger_crmentity.smownerid = vtiger_users.id    WHERE vtiger_crmentity.deleted = 0 AND vtiger_account.accountid = 27418 and (vtiger_crmentity.deleted=0 AND   (( vtiger_crmentity.description LIKE '%licitatie%') ) AND vtiger_quotes.quoteid > 0)

Could it be some version conflict?
PHP 7.4.11
Server version: 10.5.8-MariaDB-3
fresh git pull

[joebordes]

this is due to non-standard related lists. I converted the quotes related lists to standard ones here: https://github.com/tsolucio/corebos/comm...4e7e56b7bf

and it seems to be working

update and give it a try

[radu]

Yes it works, congrats!
and thanks