NOT operator in Advanced Pack Reports

[alasdaircr]

Hello,

Something that would be immensely useful would be the support of a NOT operator along side the current OR and AND specifiers in the report filter list.

How do we go about getting this implemented?

[alasdaircr]

E.g. I want to get a list of companies which DON'T have an active Opportunity of Type 'Partnership'. Easy enough with SQL, but how to handle this in your ORM?

/* Count of Accounts */
select count(*) FROM account a WHERE deleted = 0;

/* Count of Active Partnership Opportunities */
select count(*) FROM opportunity o LEFT JOIN account a On o.account_id=a.id AND a.deleted = 0 WHERE o.deleted = 0 AND o.type = 'Partnership' AND o.stage NOT IN ('Closed Won', 'Closed Lost');

/* The query I want via a NOT operator */
select count(*) from account a LEFT JOIN opportunity o ON o.deleted = 0 AND o.type = 'Partnership' AND o.stage NOT IN ('Closed Won', 'Closed Lost') AND a.id = o.account_id WHERE a.deleted = 0 AND o.account_id IS NULL; 

[yuri]

If we added "Not In" filter for enum fields you would be able to apply De Morgan's law !(A AND B) = !A OR !B to achieve NOT operator. "NOT (statement)" is not supported by mysql.

[alasdaircr]

Yes I wasn't meaning an actual SQL operator but a 'report filter' operator. Can't be done in SQL direct, but a query could be build up using joins.

That way any subquery (anything underneath the NOT report filter operator) would be joined on a NULL which would open this up to more than just enums. It could be any query of any complexity. I wouldn't know where to start looking though, and it's a bit beyond my skills I think!

[alasdaircr]

If we added "Not In" filter for enum fields you would be able to apply De Morgan's law !(A AND B) = !A OR !B to achieve NOT operator. "NOT (statement)" is not supported by mysql.

How would this translate to an SQL query? Just so I can check the numbers I'm getting.

[yuri]

I thought about adding an ability to specify excluding report to exclude not needed records by another report. Your 3rd query is not just applying NOT operator. Not sure how we could even implement UI for such ones. For simple NOT operator we can resort to !(A && B) = !A || !B rule.

[alasdaircr]

I understand the excluding report bit but that's basically the same as the target list exclusion thing which isn't really generic enough.

I think the UI is the simple bit, the tricky bit is turning it into a SQL query!

I would suggest anything underneath a NOT filter operator is a sub query which is then joined against the other queries on the same 'node level' and which is joined on id IS NULL.

[alasdaircr]

Your 3rd query is not just applying NOT operator

I'm not talking about the SQL NOT operator, I'm talking about an exclusion, you could call it whatever you want, but in boolean terms, NOT is the correct term right?

[yuri]

It would be tricky to implement. LEFT JOIN + IS NULL pair will be needed only for hasMany relationships. And we will need to group same relationships under a single JOIN ON'. For the rest of fields under NOT it should be a regular NOT w/o join.

If we treated NOT part as SQL where clause "id NOT IN (SUBREPORT_GENERATED_FROM_NOT_STATEMENT)" wouldn't this give the same result?

[alasdaircr]

IIf we treated NOT part as SQL where clause "id NOT IN (SUBREPORT_GENERATED_FROM_NOT_STATEMENT)" wouldn't this give the same result?

You're right that seems like a better approach. Here is my updated query for the above NOT filter use.

SELECT Count(*) 
FROM   account a 
WHERE  a.deleted = 0 
       AND a.id NOT IN (SELECT a.id 
                        FROM   account a 
                               JOIN opportunity o 
                                 ON a.id = o.account_id 
                        WHERE  o.deleted = 0 
                               AND o.type = 'Partnership' 
                               AND o.stage NOT IN ( 'Closed Won', 'Closed Lost')
); 

[alasdaircr]

(I don't mean to confuse things by using a NOT IN with the enum, that would also be a useful field filter option, but is not important to what we're talking about)

[yuri]

Is performance good? I tried such queries on different mysql servers and it was fast.

[alasdaircr]

It's very good but we don't have large data sets, just thousands of accounts, hundreds of opportunities.

[alasdaircr]

I think MySQL optimises so it's a JOIN with a temporary table on account.id so it should be very fast

[yuri]

Yes. It does. It was slow in older version I believe.