Projects:Grid Config For Foreign Key Drop Down Filters/QA
Contents |
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.