Projects:Grid Config For Foreign Key Drop Down Filters/QA

Contents 1 Functional Tests 1.1 Do not filter the records in the filter drop down 1.2 Do not use the filter drop down 2 Performance Tests 2.1 Do not filter the records in the filter drop down 2.1.1 Without the new configuration 2.1.2 With the new configuration 2.2 Do not use the filter drop down 2.2.1 Without the new configuration 2.2.2 With the new configuration 3 TestLink Tests 4 Automated Tests

Functional Tests

For both subprojects it has been checked that the following features work as expected:

• Saved views. Saved views created before enabling the new functionality keep working after enabling it, and viceversa
• Using the Direct Link toolbar button
• Filtering by using the Use as Filter contextual menu item

Do not filter the records in the filter drop down

When the flag is enabled:

• All the rows of the referenced table are shown, even if they are not being referenced from the referencing table.
• The readable organizations and the client filter are being properly applied.
• Tested with columns that reference the Organization entity, and with others. The Organization entity is peculiar in the sense that the readable organizations filter has to be handled differently

Do not use the filter drop down

When the flag is enabled:

• The columns does not have a icon to open the filter drop down
• There is no way to open the filter drop down, not even using shortcuts
• To filter, the user enters text in the filter editor en presses enter or moves the focus out of the filter editor

Performance Tests

Do not filter the records in the filter drop down

Without the new configuration

SELECT organizati0_.Value AS Value494_
FROM AD_Org organizati0_ 
WHERE EXISTS (SELECT 1 
              FROM M_Product product1_ INNER 
              JOIN AD_Org organizati2_ ON product1_.AD_Org_ID=organizati2_.AD_Org_ID 
              WHERE organizati2_.AD_Org_ID=organizati0_.AD_Org_ID AND (product1_.AD_Org_ID IN ('D270A5AC50874F8BA67A88EE977F8E3B' , '4399136852B145BD96CC2A6CE0800C68' , '14B1927026BE471E9B85FE699BCA61C2' , '0' , 'B5DE96143D6642228E3B9DEC69886A47' , '3B187EC130A549A7A9388F8060EF156D' , '67839EEFA49E44AC969BD60093FCC899' , '01AD882EFC8545ACA6455E2F6FD51EE9' , 'BF129721D9FB4EB0819509934153E972')) 
              AND (product1_.AD_Client_ID IN ('39363B0921BB4293B48383844325E84C' , '0'))) 
ORDER BY organizati0_.Name LIMIT 76

Having 1.000.000 products and 19 organizations, this query takes 687 ms.

With the new configuration

SELECT organizati0_.Value AS Value494_
FROM AD_Org organizati0_ 
WHERE (organizati0_.AD_Client_ID IN ('39363B0921BB4293B48383844325E84C' , '0')) 
AND organizati0_.IsActive='Y' 
ORDER BY organizati0_.AD_Org_ID DESC LIMIT 76

Having 1.000.000 products and 19 organizations, this query takes 12 ms. It is 57 times faster than the query that results if the new configuration is not enabled.

Do not use the filter drop down

Without the new configuration

This query is done to populate the filter drop down of the Business Partner column of the Business Partner subtab of the Discounts and Promotions window:

SELECT businesspa0_.name
FROM C_BPartner businesspa0_ 
WHERE EXISTS (SELECT 1 
              FROM M_Offer_BPartner pricingadj1_ 
              INNER JOIN C_BPartner businesspa2_ ON pricingadj1_.C_BPartner_ID=businesspa2_.C_BPartner_ID 
              WHERE pricingadj1_.M_Offer_ID='95AF235142FB4656A4B7D5A08DE24927' 
              AND businesspa2_.C_BPartner_ID=businesspa0_.C_BPartner_ID 
              AND (pricingadj1_.AD_Org_ID IN ('D270A5AC50874F8BA67A88EE977F8E3B' , '4399136852B145BD96CC2A6CE0800C68' , '14B1927026BE471E9B85FE699BCA61C2' , '0' , 'B5DE96143D6642228E3B9DEC69886A47' , '3B187EC130A549A7A9388F8060EF156D' , '67839EEFA49E44AC969BD60093FCC899' , '01AD882EFC8545ACA6455E2F6FD51EE9' , 'BF129721D9FB4EB0819509934153E972')) 
             AND (pricingadj1_.AD_Client_ID IN ('39363B0921BB4293B48383844325E84C' , '0'))) 
ORDER BY businesspa0_.Name 
LIMIT 76

Having 1.000.000 business partners in that subtab, the query takes 2653 ms. Once the dropdown is populated, the query that results from selecting a specific business partner is very fast, taking aroung 11 ms.

With the new configuration

With the new configuration there is no dropdown and the column has to be filtered by entering the fk identifier and pressing enter / blurring out of the filter editor. This is the query done when 'arturo' is entered in the filter editor, after having configured the 'iStartsWith' as operator in the grid configuration:

SELECT pricingadj0_.M_Offer_Bpartner_ID AS M1_504_, pricingadj0_.AD_Client_ID AS AD2_504_, pricingadj0_.AD_Org_ID AS AD3_504_, pricingadj0_.IsActive AS IsActive504_, pricingadj0_.Created AS Created504_, pricingadj0_.Createdby AS Createdby504_, pricingadj0_.Updated AS Updated504_, pricingadj0_.Updatedby AS Updatedby504_, pricingadj0_.M_Offer_ID AS M9_504_, pricingadj0_.C_BPartner_ID AS C10_504_ 
FROM M_Offer_BPartner pricingadj0_ 
LEFT OUTER JOIN C_BPartner businesspa1_ ON pricingadj0_.C_BPartner_ID=businesspa1_.C_BPartner_ID 
WHERE pricingadj0_.M_Offer_ID='95AF235142FB4656A4B7D5A08DE24927' 
AND (upper(businesspa1_.Name) LIKE upper('%arturo%') escape '|') 
AND (pricingadj0_.AD_Org_ID IN ('D270A5AC50874F8BA67A88EE977F8E3B' , '4399136852B145BD96CC2A6CE0800C68' , '14B1927026BE471E9B85FE699BCA61C2' , '0' , 'B5DE96143D6642228E3B9DEC69886A47' , '3B187EC130A549A7A9388F8060EF156D' , '67839EEFA49E44AC969BD60093FCC899' , '01AD882EFC8545ACA6455E2F6FD51EE9' , 'BF129721D9FB4EB0819509934153E972')) 
AND (pricingadj0_.AD_Client_ID IN ('39363B0921BB4293B48383844325E84C' , '0')) 
ORDER BY businesspa1_.Name, pricingadj0_.M_Offer_Bpartner_ID 
LIMIT 101

Using this index, the query takes 12 ms (221 times faster than populating the dropdown):

 CREATE INDEX c_bpartner_name_upper 
 ON c_bpartner 
 USING btree (upper(name) COLLATE pg_catalog."default" varchar_pattern_ops);

TestLink Tests

Unfiltered Foreign Key Combo configuration works properly

Disable Foreign Key Filter Combo configuration works properly

Disable Foreign Key Filter Combo configuration works properly with previously defined saved views

Automated Tests

No automated tests have been built for this project. The main changes introduced by this project affect the way the queries are built in the back end, and there is no simple way to test this automatically.